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  |
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  |
Michel Cadot
Messages: 29436
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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]
|
|
| |
| Re: Autonomous Transaction in a Trigger [message #429246 is a reply to message #429244] |
Mon, 02 November 2009 13:51 |
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 |
Michel Cadot
Messages: 29436
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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
|
|
| |
Goto Forum:
Current Time: Tue Dec 1 20:41:08 CST 2009
Total time taken to generate the page: 0.15371 seconds
|
] 
Members
Search
Help
Register
Login
Home
