Range Partitioning of Table
816293Nov 18 2010 — edited Nov 18 2010Hi 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