Skip to Main Content
  • Questions
  • sets of ORA-603/604/1000/6512/4088 errors

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

Comments

Hariharan Senthil pandiyan, September 20, 2016 - 3:15 am UTC

Thanks Connor,

I will test and let you know as soon as possible.


Thanks,

Hariharan Senthil pandiyan, September 20, 2016 - 3:19 am UTC

Thanks connor ,

I will test and update the same.



Thanks,

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library