Oracle Environment : 11.2.0.2
I'm trying to create below procedure to create range partitions automatically every month. I can't implement interval partition here as the field on which the column is partitioned is a Timestamp field. In the below proc I want to select timestamp and pass that to v_limit variable as the partitioned column is timestamp filed. I tried current_timestamp function but thats not returning timestamp value. Is there a way where I can pass timestamp value and add 2 months to it as below (like add 2 months to sysdate)
Didn't work:
SQL> select add_months(current_timestamp,2) from dual;
ADD_MONTH
---------
10-MAR-13
create or replace procedure abc.month_part as
v_part_name all_tab_partitions.partition_name%type;
v_limit TIMESTAMP;
begin
select to_char(trunc(add_months(sysdate,1),'MM'),'MonYYYY')
into v_part_name
from dual;
select to_char(trunc(add_months(sysdate,2),'MM'),'dd-mon-yyyy')
into v_limit
from dual;
execute immediate 'ALTER TABLE abc.partxy ADD PARTITION ' || v_part_name || ' VALUES LESS THAN (' || v_limit || ') tablespace USERS';
end;
/
Can someone help me with this.
Thanks in advance