trigger- problem: too many open cursors

From: Andreas Mosmann <mosmann_at_expires-30-04-2009.news-group.org>
Date: Wed, 01 Apr 2009 14:00:28 +0200
Message-ID: <1238587228.75_at_user.newsoffice.de>

hi ng,

it is a bit difficult to explain and more difficult to analyze and we look for a hint where to search for our problem.

We use Oracle 11.1.0.6.0 and there are triggers, that write down changes of some tables compressed into a special changelog-table.

Something like

CREATE TRIGGER MyOwner.MyTrigger BEFORE UPDATE ON TABLE1
FOR EACH ROW declare

      EOraDatumAuszerhalb EXCEPTION;
      PRAGMA EXCEPTION_INIT(EOraDatumAuszerhalb, -20100);

    lvs_ChangeLog_Fields varchar2(4000);
    lvs_ChangeLog_Program varchar2(64);

    lvs_ChangeLog_IPAdress varchar2(64);     lvs_ChangeLog_Machine varchar2(64);
begin

• some probably uninteresting code if (:new.cdatumfaellung>sysdate) or (:new.cdatumpflanzung>sysdate) or (:new.cdatumfaellung<:new.cdatumpflanzung) then raise EOraDatumAuszerhalb;--exception end if; lvs_ChangeLog_Fields:= EncodeXML('CID',:old.CID,:new.CID)||

EncodeXML('CIDNEBENANLAGE',:old.CIDNEBENANLAGE,:new.CIDNEBENANLAGE)||

        EncodeXML...
    ;
    if lvs_ChangeLog_Fields is not null then       

lvs_ChangeLog_Fields:='<Spalten>'||chr(13)||lvs_ChangeLog_Fields||'</Spalten>'||chr(13);

        select
            program,
            machine,
            sys_context('USERENV','IP_ADDRESS')
        into
            lvs_ChangeLog_Program,
            lvs_ChangeLog_Machine,
            lvs_ChangeLog_IPAdress
        from
            v$session
        where
            audsid=sys_context('USERENV','SESSIONID');

        insert into BAUMPRG.TBCHANGELOG(
            clogid,
            clogtime,
            clogtable,
            CLOGIDUSER,
            CLOGOSUSER,
            CLOGPROGRAM,
            CLOGMACHINE,
            CLOGIPADDRESS,
            clogschema,
            clogreason,
            cdataid,
            cdatafields
        )values(
            BAUMPRG.PChangeLog.NewGUID(),
            sysdate,


            'TABLE1',

            :new.CIDNUTZER,
            SYS_CONTEXT('USERENV','OS_USER'),
            lvs_ChangeLog_Program,
            lvs_ChangeLog_Machine,
            lvs_ChangeLog_IPAdress,


            'OWNER',

            'U',

            :old.cid,
            lvs_ChangeLog_Fields
        );

    end if;
end;

This is only a Code fragment.
EncodeXML is a short function, that returns an XML- string- fragment only if there are differences between old and new value.

Now the problem: Sometimes (no way to find out all circumstances) there is an error

ORA-01000: maximum open cursors exceeded
ORA-06512: at MyOwner.MyTrigger, line 167
ORA-04088: error during execution of trigger MyOwner.MyTrigger

Line 167 is the line, where the insert statement is executed

There are no loops in the code and so we do not know where new cursors are opened. What can we do to find out our mistake(s)?

Many thanks
Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Wed Apr 01 2009 - 07:00:28 CDT