CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqldatabase-partitioning
user2035693
user2035693

Reputation: 313

alter table add partition using a procedure oracle g11

  1. I'm trying to add partitions by month on a partitioned table using a procedure that will be ran each year on January 1.

    create or replace procedure create_partitions as
  part VARCHAR2(50);
  date_val DATE;
  sql_val VARCHAR2(4000);
begin
  date_val := TO_DATE(CONCAT(extract(year from sysdate),'-06-01 00:00:00'),'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN');
  part := CONCAT('P_MAY',extract(year from sysdate));
  sql_val := 'ALTER TABLE TEST1 
    ADD PARTITION '||part||' VALUES LESS THAN ('||date_val||')
    SEGMENT CREATION IMMEDIATE';

EXECUTE IMMEDIATE sql_val;
end create_partitions;

When I run this procedure I get this error

ORA-00907: missing right parenthesis

Instead of concatenating I tried with binding values

sql_val := 'ALTER TABLE TEST1 
    ADD PARTITION :1 VALUES LESS THAN (:2)
    SEGMENT CREATION IMMEDIATE';

EXECUTE IMMEDIATE sql_val USING part, date_val;

and I get this

ORA-00902: invalid datatype

If I try this with hardcoded values it work without problem. But I need something dynamic.

  1. also there is a way to add multiple partitions in G11 without running multiple alter queries? I tried this, but I think it works only in G12+

    ALTER TABLE TEST1 ADD
  PARTITION t1...
  PARTITION t2...

Thanks

Upvotes: 0

Views: 4366

Answers (2)

natasa
natasa

Reputation: 3

this is how your seq should be:

sql_val := 'ALTER TABLE TEST1 
    ADD PARTITION '||' '||part|| ' ' ||' VALUES LESS THAN ('||date_str||')
    SEGMENT CREATION IMMEDIATE';

you need a blankd space where the partition name is

Upvotes: 0

Rusty
Rusty

Reputation: 2138

You need to convert your date value into date literal like this:

date_val := TO_DATE(CONCAT(extract(year from sysdate),'-06-01 00:00:00'),'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN');
date_str := 'DATE'''||to_char(date_val, 'yyyy-mm-dd')||'''';
part := CONCAT('P_MAY',extract(year from sysdate));
  sql_val := 'ALTER TABLE TEST1 
    ADD PARTITION '||part||' VALUES LESS THAN ('||date_str||')
    SEGMENT CREATION IMMEDIATE';

EXECUTE IMMEDIATE sql_val;

Or if you need time component (but I see you don't in your example) like that:

date_str := 'TO_DATE('''||to_char(date_val, 'yyyy-mm-dd hh24:mi:ss')||''', ''yyyy-mm-dd hh24:mi:ss'')';

I can also recommend to download PL/SQL utilities packages from http://www.softcraftltd.co.uk/downloads/

Upvotes: 1

Related Questions