Thanks for the question, Hariharan.
Asked: September 19, 2016 - 10:30 am UTC
Last updated: September 19, 2016 - 4:13 pm UTC
Version: 11.2.0.3
Viewed 1000+ times
You Asked
Hello All,
The question here is how does Oracle handle cursoring with a trigger? Th emax cursor error is happening during execution of the following simple code:
CREATE OR REPLACE TRIGGER TNT_OWNER.TRG_USC_ABI_MESSAGE_EMAIL
AFTER INSERT OR UPDATE
ON TNT_OWNER.USC_ABI_MESSAGE
FOR EACH ROW
DECLARE
v_error VARCHAR2(200);
v_id NUMBER;
v_table VARCHAR2(30);
BEGIN
IF GTD_AFTER_TRIG_CONTROL.DISABLE_AT_SESSION_LEVEL = TRUE THEN
RETURN; --- This will return control back and not execute the trigger
END IF;
-- As of 09/21/15, the behid is critical to all downstream processing
-- so no point in writing to email buffering table if that is missing
IF :NEW.BROKER_ENTRY_HEADERS_ID IS NOT NULL THEN
v_table := 'USC_ABI_MESSAGE';
v_id := :NEW.ABI_ID;
INSERT INTO usc_email_trg_buffer
(abstract_key,
written_date,
table_name,
table_key,
error_message)
VALUES
(usc_email_trg_buffer_s1.NEXTVAL,
SYSDATE,
v_table,
v_id,
null);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error := SUBSTR(SQLERRM, 1, 200);
INSERT INTO usc_email_error_log
(PROCEDURE_NAME, TABLE_NAME, RUN_DATE, ERROR_MSG, ROW_DATA)
VALUES
('ACE_EMAIL_TRIGGER', v_table, SYSDATE, v_error, v_id);
END;
/
Based on the above ... let's give an example that a DB session executes an UPDATE to 500 rows in table USC_ABI_MESSAGE. Since the trigger is on EACH ROW, will Oracle open a cursor for each INSERT executed within the trigger (i.e., 500 opened cursors)?
Thanks in Advance,
and Connor said...
You certainly shouldnt get cursor problems in this scenario, because it is not as if we have 500 insert statements currently *running*. They run in serial, ie insert #1 executes *and completes*, then #2 and so forth.
For example
SQL> show parameter open_cur
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
SQL> drop table t purge;
Table dropped.
SQL> drop table t1 purge;
Table dropped.
SQL>
SQL> create table t ( x int);
Table created.
SQL> create table t1 ( y int );
Table created.
SQL>
SQL> create or replace
2 trigger trg
3 before insert on t
4 for each row
5 begin
6 insert into t1 values (:new.x );
7 end;
8 /
Trigger created.
SQL>
SQL> insert into t
2 select rownum from dual
3 connect by level <= 10000;
10000 rows created.
SQL> select count(*) from t;
COUNT(*)
----------
10000
SQL> select count(*) from t1;
COUNT(*)
----------
10000
SQL>
So 10000 inserts via the trigger are no problem
Can you give us a test case showing your problem occurring ?
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment