Oracle-L: RE: Maximum Open Cursors on Insert Trigger

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_ESPN.COM>
Date: Mon, 28 Jul 2003 07:04:25 -0800
Message-ID: <F001.005C77A7.20030728070425@fatcity.com>

Just for a more _completeness_ I'd put a curser close statement in the exception clause ... I agree with you that for-update is a no no in this context.

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Monday, July 28, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L

We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger checks a parent table for records. I know this trigger really is not needed, since a Foreign Key exists to enforce referential integrity, so I plan to disable it
(furthermore, it does a SELECT FOR UPDATE, which doesn't make sense).
However, I'm trying to understand why the developer keeps getting "ORA-01000: maximum open cursors exceeded" during the inserts. I've bumped OPEN_CURSORS up to 1000. Is there something unique to VB that could be causing this problem? I've done mass inserts before on tables that have triggers without running into this type of problem. I can't see any problem in the trigger logic, since the cursor is always closed. This is a 9.2.0.3 database on Tru64.

Thanks,
Jay

The source for the trigger is:

CREATE OR REPLACE TRIGGER "TOPAS".TI_CABLE_PAIRS BEFORE INSERT ON CABLE_PAIRS FOR EACH ROW
DECLARE
INTEGRITY_ERROR EXCEPTION;

    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

DECLARATION OF INSERTCHILDPARENTEXIST CONSTRAINT FOR THE PARENT "EXCHANGES" CURSOR CPK1_CABLE_PAIRS(VAR_EXCHANGE VARCHAR) IS SELECT 1 FROM EXCHANGES WHERE EXCHANGE = VAR_EXCHANGE AND VAR_EXCHANGE IS NOT NULL FOR UPDATE OF EXCHANGE; BEGIN
PARENT "EXCHANGES" MUST EXIST WHEN INSERTING A CHILD IN "CABLE_PAIRS" IF :NEW.EXCHANGE IS NOT NULL THEN OPEN CPK1_CABLE_PAIRS(:NEW.EXCHANGE); FETCH CPK1_CABLE_PAIRS INTO DUMMY; FOUND := CPK1_CABLE_PAIRS%FOUND; CLOSE CPK1_CABLE_PAIRS; IF NOT FOUND THEN ERRNO := -20002; ERRMSG := 'Parent does not exist in "EXCHANGES". Cannot create child in "CABLE_PAIRS".'; RAISE INTEGRITY_ERROR; END IF; END IF; -- ERRORS HANDLING EXCEPTION WHEN INTEGRITY_ERROR THEN RAISE_APPLICATION_ERROR(ERRNO, ERRMSG); END; /

**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: jhostetter_at_decommunications.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

text/plain attachment: ESPN_Disclaimer.txt

Received on Mon Jul 28 2003 - 10:04:25 CDT