Home » SQL & PL/SQL » SQL & PL/SQL » Help with splitting partitioned table (Oracle 10.x)
Help with splitting partitioned table [message #348628] Wed, 17 September 2008 07:56 Go to next message
Felder
Messages: 6
Registered: September 2008
Junior Member
Hi,

I'm currently working on pl sql script, which should perform splitting of partitioned table.
DECLARE
splittime date:= TO_DATE('2008-09-01 00:00:00','SYYYY-MM-DD HH24:MI:SS');

test VARCHAR2(100):= 'ALTER TABLE table SPLIT PARTITION table_maxvalue AT(:1)
 INTO(PARTITION table_200808, PARTITION table_maxvalue)';
BEGIN
execute immediate test USING splittime;
END;

Alternatively I've tried also
execute immediate 'ALTER TABLE table SPLIT PARTITION table_maxvalue AT(:1)
INTO(PARTITION table_200808, PARTITION table_maxvalue)' USING TO_DATE('2008-09-01 00:00:00','SYYYY-MM-DD HH24:MI:SS');

Result has always been
ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE.

I have also tried various other syntaxes, but with no luck at all. I would be very grateful for any help, because next step I can think of is to make a bash script run by cronjob with sqlplus instead of oracle scheduler task.

And i would really like to know what is wrong with above for future reference.
Re: Help with splitting partitioned table [message #348631 is a reply to message #348628] Wed, 17 September 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use bind variables in DDL.

Regards
Michel
Re: Help with splitting partitioned table [message #348641 is a reply to message #348631] Wed, 17 September 2008 08:19 Go to previous messageGo to next message
Felder
Messages: 6
Registered: September 2008
Junior Member
Thank you for quick answer.

Is there anything that I can do about it at all?

I would really prefer to get this functionality to work via scheduler instead of some hack running in OS.

Re: Help with splitting partitioned table [message #348656 is a reply to message #348641] Wed, 17 September 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Embed the value inside the statement text.

Regards
Michel
Re: Help with splitting partitioned table [message #348659 is a reply to message #348656] Wed, 17 September 2008 08:59 Go to previous messageGo to next message
Felder
Messages: 6
Registered: September 2008
Junior Member
I tried this one in the beginning (using USING and binds was my last ditch effort)

splittime date:= TO_DATE('2008-09-01 00:00:00','SYYYY-MM-DD HH24:MI:SS');
BEGIN
execute immediate 'ALTER TABLE table SPLIT PARTITION table_maxvalue AT(splittime) INTO(PARTITION table_200808, PARTITION table_maxvalue)';
END;



But the result was same ORA-14019.
Re: Help with splitting partitioned table [message #348663 is a reply to message #348659] Wed, 17 September 2008 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I said embed the value not the variable:
execute immediate 'first part'||variable||'last part';

Regards
Michel
Re: Help with splitting partitioned table [message #348672 is a reply to message #348663] Wed, 17 September 2008 09:31 Go to previous message
Felder
Messages: 6
Registered: September 2008
Junior Member
Thank you very much, I had tried it but decided to try once more and this seems to be working, although i don't know why i needed so many single quotation marks.

execute immediate 'ALTER TABLE table SPLIT PARTITION table_maxvalue AT('''|| splittime ||''') INTO(PARTITION ...


Thank you for your patience and help!

[Updated on: Wed, 17 September 2008 12:51]

Report message to a moderator

Previous Topic: Please help guys.
Next Topic: running update on 200000 records
Goto Forum:
  


Current Time: Thu Dec 08 01:57:56 CST 2016

Total time taken to generate the page: 0.15715 seconds