Re: Maximum Open Cursors on Insert Trigger

There are some relevant notes on MetaLink that may be of help.

Search on 'visual basic ora-1000'

Jared

On Monday 28 July 2003 07:54, Jay Hostetter wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.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).