SQL & PL/SQL

Hi there,

I´m having a problem with the following situation in Oracle 8i:

I have a table TEST_TABLE, which is partitioned by range using a DATE column. The idea is to have one partition for each month, so the HIGH_VALUE of the partitions is always the first day of the of the following month that the partition represents.

I created a BEFORE INSERT TRIGGER on the table TEST_TABLE, which tests if the partition for the month of the record that is being inserted exists and, in case it doesn´t, an AUTONOMOUS_TRANSACTION PROC is called to create the TRIGGER.

Running the code below one can see that although the partitions are being created as expected, when you try to insert a record with a date higher than that of the last partition for the first time, this error is returned:
ORA-14400: inserted partition key is beyond highest legal partition key.

Notice that if you run the same insert statement again, it´s inserted correctly on the partition that was created on the first try.

I´ll appreciate any help with this matter.

code
----------------
CREATE TABLE TEST_TABLE (
ID NUMBER,
DT DATE
)
TABLESPACE USERS
PARTITION BY RANGE (DT)
(
PARTITION PART_B42009 VALUES LESS THAN (TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
TABLESPACE USERS
);
/

CREATE OR REPLACE PROCEDURE SP_ADD_PARTITION(P_DATE TEST_TABLE.DT%TYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_STR VARCHAR2(500);
BEGIN

V_STR := 'ALTER TABLE TEST_TABLE ADD'
|| ' PARTITION PART_' || TO_CHAR(P_DATE, 'YYYYMM')
|| ' VALUES LESS THAN ( TO_DATE ( '''
|| TO_CHAR(ADD_MONTHS(P_DATE, 1), 'YYYY-MM') || '-01 00:00:00'', '
|| '''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))';

EXECUTE IMMEDIATE(V_STR);

END SP_ADD_PARTITION;
/

CREATE OR REPLACE TRIGGER TR_B_I_R_TEST_TABLE
BEFORE INSERT
ON TEST_TABLE FOR EACH ROW
DECLARE
V_PARTITION_EXISTS NUMBER;
BEGIN

IF :NEW.DT >= TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') THEN

EXECUTE IMMEDIATE ('SELECT COUNT(1)'
|| ' FROM all_tab_partitions atp'
|| ' WHERE atp.table_name = ''TEST_TABLE'' '
|| ' AND atp.PARTITION_NAME = :v1 ')
INTO V_PARTITION_EXISTS
USING 'PART_' || TO_CHAR(:NEW.DT, 'YYYYMM');

IF V_PARTITION_EXISTS = 0 THEN

DBMS_OUTPUT.PUT_LINE ('Partition [' || 'PART_' || TO_CHAR(:NEW.DT, 'YYYYMM') || '] does not exist!');
DBMS_OUTPUT.PUT_LINE ('Creating..');
SP_ADD_PARTITION ( :NEW.DT );
DBMS_OUTPUT.PUT_LINE ('Success.');

EXECUTE IMMEDIATE ('SELECT COUNT(1)'
|| ' FROM all_tab_partitions atp'
|| ' WHERE atp.table_name = ''TEST_TABLE'' '
|| ' AND atp.PARTITION_NAME = :v1 ')
INTO V_PARTITION_EXISTS
USING 'PART_' || TO_CHAR(:NEW.DT, 'YYYYMM');

IF V_PARTITION_EXISTS = 1 THEN
DBMS_OUTPUT.PUT_LINE ('it´s visible at this stage..');
ELSE
DBMS_OUTPUT.PUT_LINE ('it´s not visible at this stage..');
END IF;

ELSE

DBMS_OUTPUT.PUT_LINE ('Partition [' || 'PART_' || TO_CHAR(:NEW.DT, 'YYYYMM')
|| '] already exists!');

END IF;

END IF;

DBMS_OUTPUT.PUT_LINE ('Continuing with insertion..');

END TR_B_I_R_TEST_TABLE;
/

-- Goes to the lower partition
INSERT INTO TEST_TABLE VALUES (1, TO_DATE('2008-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS'));

-- Returns error on the first try
INSERT INTO TEST_TABLE VALUES (2, TO_DATE('2009-01-01 00:00:01','YYYY-MM-DD HH24:MI:SS'));

----------------