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!

Range Partitioning of Table

816293Nov 18 2010 — edited Nov 18 2010
Hi all,
I need to do range partitioning on a table using current system timestamp. sth like

CREATE TABLE SAMPLE(ID NUMBER, SAMPLE_DATE TIMESTAMP)PARTITION BY RANGE(SAMPLE_DATE)(PARTITION P1 VALUES LESS THAN (LOCALTIMESTAMP));

Is there any way I can assign a dynamic date using variable other than string literal after VALUES LESS THAN clause? I tried achieving this with stored procedure but always throws error for this filed.

CREATE OR REPLACE PROCEDURE partition_test
IS
x TIMESTAMP;
comm long;
BEGIN
SELECT LOCALTIMESTAMP into x from dual;
comm := 'CREATE TABLE SAMPLE(ID NUMBER, SAMPLE_DATE TIMESTAMP)PARTITION BY RANGE(SAMPLE_DATE)(PARTITION P1 VALUES LESS THAN (x))';
execute immediate comm;
END partition_test;
/

error I'm getting is
ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
Any clue? Would really appreciate your help.

Noman
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 16 2010
Added on Nov 18 2010
2 comments
135 views