Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Creating range paritions automatically

user12241421Jan 10 2013 — edited Feb 15 2013
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
This post has been answered by unknown-7404 on Feb 5 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 15 2013
Added on Jan 10 2013
23 comments
25,640 views