CODEWITHSUNDEEP
oracle-databaseoracle11g
user1968156
user1968156

Reputation: 53

Oracle procedure compilation error

DB Version : Oracle 11.2.0.2

Scenario : Trying to automate creation of range partitions using a procedure. Below is the code for that. It gets created without any error. But when I execute, it throws an error. I think the values am passing to the variables are not right. I tried with various sql's but nothing worked.Can someone please help me with this procedure.

SQL> CREATE OR REPLACE PROCEDURE MONTHLY_PARTITION as
  2
  3    v_partition_name varchar2(30);
  4    v_limit TIMESTAMP with local time zone;

  5    6  begin
  7
  8    select to_char(trunc(add_months(sysdate,1),'MM'),'MonYYYY')
  9    into v_partition_name
 10    from dual;
 11
 12    SELECT TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(current_timestamp, 2), 'YYYYMMDD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')  into v_limit from dual;
 13
 14  execute immediate 'ALTER TABLE TEST ADD PARTITION ' || v_partition_name || ' VALUES LESS    THAN (TIMESTAMP ' || v_limit || ')';
 15
 16  end;
 17  /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL> exec MONTHLY_PARTITION;
BEGIN MONTHLY_PARTITION; END;

*
ERROR at line 1:
ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
ORA-06512: at "MONTHLY_PARTITION", line 14
ORA-06512: at line 1

Upvotes: 1

Views: 2386

Answers (3)

DazzaL
DazzaL

Reputation: 21973

what you have is actually OK. just a few datatype errors that is all!

you have to have your variable as varchar2 and not timestamp when splicing it into the DDL.

ie:

SQL> create table TEST(dte timestamp)
  2  partition by range (dte)
  3  (PARTITION TODAY VALUES LESS THAN (TIMESTAMP'2012-02-01 00:00:00'));

Table created.

SQL> CREATE OR REPLACE PROCEDURE MONTHLY_PARTITION as
  2
  3   v_partition_name varchar2(30);
  4   v_limit varchar2(30);
  5
  6    begin
  7
  8   select to_char(trunc(add_months(sysdate,1),'MM'),'MonYYYY')
  9   into v_partition_name
 10   from dual;
 11
 12   SELECT TO_CHAR(ADD_MONTHS(current_timestamp, 2), 'YYYY-MM-DD HH24:MI:SS')  into v_limit from dual;
 13
 14  execute immediate 'ALTER TABLE TEST ADD PARTITION ' || v_partition_name || ' VALUES LESS THAN (TIMESTAMP''' || v_limit || ''')';
 15
 16  end;
 17  /

Procedure created.

SQL> exec monthly_partition

PL/SQL procedure successfully completed.

SQL> select partition_name from user_tab_partitions where table_name = 'TEST';

PARTITION_NAME
------------------------------
TODAY
MAR2013

also note that your current expression ADD_MONTHS(current_timestamp, 2) isn't truncating to the month. did you want to do that too? i.e. trunc(ADD_MONTHS(current_timestamp, 2), 'mm')?

Upvotes: 2

Randy
Randy

Reputation: 16677

assign this to a string variable:

    str := 'ALTER TABLE TEST ADD PARTITION ' || v_partition_name 
       || ' VALUES LESS THAN (TIMESTAMP ' || v_limit 
       || ')';

then 

dbms_output.PUT_LINE( str );
execute immediate str;

see if the output str will run as you intend from sql plus

Upvotes: 1

Swapna Mohan
Swapna Mohan

Reputation: 175

Partition values must be fixed and you have to manually handle them.You can't have an expression when you specify the value for the partition.

Upvotes: 0

Related Questions