Home » SQL & PL/SQL » SQL & PL/SQL » Autonomous Transaction in a Trigger (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0)
Autonomous Transaction in a Trigger [message #429243] Mon, 02 November 2009 13:35 Go to next message
pmukhi
Messages: 3
Registered: November 2009
Junior Member
Hi,
The task is to alter a table and add new partitions if the inserted data does not fit into the existing partitions.

I am using the following insert:
insert into HISTOGRAM(HISTOGRAM_ID, SNAPSHOT_ID, PARAM_ID, TOTAL_BIN_COUNT, TOTAL_PROD_COUNT_MIDPOINT, MAX_END_RANGE, DEVICE_ID, SNAPSHOT_REALTIMECLOCK, CLEARED_REALTIMECLOCK)
values (histogram_seq.nextval, 240, '6330000000020',0,0,'120', 24, 81000000, 0)

And the trigger code is:
create or replace TRIGGER ADD_PARTITION_HISTOGRAM
before INSERT ON HISTOGRAM
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;-- needed for Execute Immediate
HV_MAX NUMBER; N NUMBER; CNT NUMBER; POS NUMBER;
sqlString VARCHAR2(300); -- ALTER SQL STRING
-- since we are finding (max - 1) partition, we have to multiply the counts by 2
PARTITION_RANGE_INC CONSTANT NUMBER:= 2*2000000;
PARTITION_POS_INC CONSTANT NUMBER:= 2*1;
BEGIN
-- Assumes that we have more than one partition on that table
-- We are getting the 2nd last partition position and the high value of that partition
<<L1>>
SELECT HIGH_VALUE, PARTITION_POSITION
INTO HV_MAX, POS
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME='HISTOGRAM'
AND PARTITION_POSITION =
( SELECT (MAX(PARTITION_POSITION) - 1)
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'HISTOGRAM');

IF :NEW.SNAPSHOT_REALTIMECLOCK >= HV_MAX THEN
BEGIN
CNT := HV_MAX + PARTITION_RANGE_INC;
N := POS + PARTITION_POS_INC;
sqlString := 'ALTER TABLE HISTOGRAM ADD PARTITION HIST_RTC' || N || ' VALUES LESS THAN (' || CNT || ')';
EXECUTE IMMEDIATE sqlString;
IF :NEW.SNAPSHOT_REALTIMECLOCK >= CNT THEN
DBMS_OUTPUT.PUT_LINE ('LOOPING AGAIN, GOING TO LABEL');
GOTO L1;
END IF;
commit;
END;
END IF;
END;


The error message that I get is:
Error starting at line 1 in command:
insert into HISTOGRAM(HISTOGRAM_ID, SNAPSHOT_ID, PARAM_ID, TOTAL_BIN_COUNT, TOTAL_PROD_COUNT_MIDPOINT, MAX_END_RANGE, DEVICE_ID, SNAPSHOT_REALTIMECLOCK, CLEARED_REALTIMECLOCK)
values (histogram_seq.nextval, 240, '6330000000020',0,0,'120', 24, 81000000, 0)
Error report:
SQL Error: ORA-14400: inserted partition key does not map to any partition


My question is:
1. In the above trigger code, I am adding a new partition when the inserted value is greater than the partition range value of the 2nd last partition. (Reason: We did not want to wait till the last partition is exhausted and then add new partitions)
The above code works when the inserted data is less than the last partition range value
2. However, if the values being inserted are all greater than the defined partitions, the above ORA_14400 error is thrown. Here I can see that the table is altered and a new partition is altered, but the main transaction (insert statement) does not see the committed state of the table (new partition being added)
I believe this is because I am using pragma autonomous_transactions, but since there is a commit at the end of the trigger, shouldn't the insert statement be able to see that a new partition has been added?

If not, please advice on how this would be possible.

Thanks.
Re: Autonomous Transaction in a Trigger [message #429244 is a reply to message #429243] Mon, 02 November 2009 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A statement sees the database at it was at its starting point.

Quote:
please advice on how this would be possible.

Upgrade to 11g and use interval partitioning.

Regards
Michel

[Updated on: Mon, 02 November 2009 13:46]

Report message to a moderator

Re: Autonomous Transaction in a Trigger [message #429245 is a reply to message #429243] Mon, 02 November 2009 13:45 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>shouldn't the insert statement be able to see that a new partition has been added?

NO!
Oracle provides a read consistent view of the DB as it existed at the start of this transaction.
Re: Autonomous Transaction in a Trigger [message #429246 is a reply to message #429244] Mon, 02 November 2009 13:51 Go to previous messageGo to next message
pmukhi
Messages: 3
Registered: November 2009
Junior Member
Hi Michel,

Thanks for your response.
Unfortunately, in the current release, upgrading to 11g is not an option. Thus we need to have some pl sql code to add the partitions.

Could you suggest some work around for this problem?

I was under the impression that since I was using a 'before insert' trigger, the trigger code would execute, add the partition and then the insert would take place.

Thanks.
Re: Autonomous Transaction in a Trigger [message #429247 is a reply to message #429246] Mon, 02 November 2009 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it with a trigger.
Investigate inserting through a procedure that will first test if the appropriate partition exist.

If you can't, investigate modifying the application to handle the exception and then add a partition before retrying the insert.

If you can't do, then nothing can be done and you have to precreate all partitions.

Regards
Michel
Re: Autonomous Transaction in a Trigger [message #429593 is a reply to message #429247] Wed, 04 November 2009 12:50 Go to previous message
pmukhi
Messages: 3
Registered: November 2009
Junior Member
Thanks for your Michel.

An update on this issue. We have to decide to precreate all the partitions (unless the DBA figures out some other solution).

Regards.
Previous Topic: Retrieving a resource's working days in financial periods between hire and termination date
Next Topic: ORA-00904: : invalid identifier during merge
Goto Forum:
  


Current Time: Mon Dec 05 08:48:03 CST 2016

Total time taken to generate the page: 0.15748 seconds