Home » RDBMS Server » Server Administration » ABOUT SEQUENCE IN PL/SQL
ABOUT SEQUENCE IN PL/SQL [message #369679] Fri, 04 August 2000 04:06 Go to next message
KED
Messages: 1
Registered: August 2000
Junior Member
my table isCREATE TABLE TEST_TABLE (
FUND_RUN_NO1 NUMBER(10) DEFAULT 0 NULL,
COMP_RUN_NO1 NUMBER(12) DEFAULT 0 NULL,
MEM_RUN_NO1 NUMBER(12) DEFAULT 0 NULL;

and my pl/sql is
SQL> CREATE OR REPLACE TRIGGER TEST_SEQ
2 BEFORE INSERT ON TEST_TABLE
3 FOR EACH ROW
4
4 BEGIN
5 INSERT INTO TEST_TABLE
6 ( FUND_RUN_NO1)
7 VALUES
8 (FUND_SEQUENCE.NEXTVAL);
9 COMMIT;
10 END;
11 /

but when I run script in sql plus to insert table test_table like this :
INSERT INTO TEST_TABLE
(COMP_RUN_NO1,MEM_RUN_NO1)
VALUES (3,4);

then return this code:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "GEN.TEST_SEQ", line 2
ORA-04088: error during execution of trigger 'GEN.TEST_SEQ'

how I handle it?
Re: ABOUT SEQUENCE IN PL/SQL [message #369682 is a reply to message #369679] Sun, 06 August 2000 16:34 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

Ask your DBA to increase the OPEN_CURSORS init.ora parameter. Also read the following (note 18591.1) from Oracle MetaLink:

Error: ORA-1000
Text: maximum open cursors exceeded
-------------------------------------------------------------------------------
Cause: A host language program attempted to open too many cursors.
The initialization parameter OPEN_CURSORS determines the maximum
number of cursors per user.
Action: Modify the program to use fewer cursors.
If this error occurs often, shut down Oracle, increase the value of
OPEN_CURSORS, and then restart Oracle.

*** Important: The notes below are for experienced users - See [[NOTE:22080.1]]

Explanation:
Cursors are resources explicitly opened by application code, and in
recursive operations by Oracle code. The init.ora <Parameter:OPEN_CURSORS>
controls the maximum number of concurrent cursors a session may have open
at any point in time. An attempt to open an additional cursor results in
the ORA-1000 "maximum open cursors exceeded" error.

Diagnostic Steps:
1. Check the value of the OPEN_CURSORS parameter

2. A DBA can use the <View:V$OPEN_CURSOR> to see what cursors are in use
at any point in time.

3. If it is proving difficult to identify the reason for the ORA-1000
error then it is possible to get the user session to generate a trace
file when the error occurs by adding the following event to the init.ora.
IMPORTANT: See [[NOTE:75713.1]] before adding any event to the init.ora file

event="1000 trace name errorstack level 3"

This will cause a trace file to be written by any session when it hits
an ORA-1000. Provided MAX_DUMP_FILE_SIZE is large enough this trace
should help identify what all cursors in the session are being used for
and hence help identify the cause of the ORA-1000.

WARNING: Although ORA-1000 can often be tackled by increasing OPEN_CURSORS
it is not sensible to just keep increasing this value as open
cursors hold resources. Eg: If you have got OPEN_CURSORS up to
several hundred then it may be better to identify what all these
cursors are for rather than just blindly increasing OPEN_CURSORS
further.

Additional Notes:
Dictionary corruption, especially loops in DEPENDENCY$, can cause
ORA-1000 on ALTER / DROP of an object.

Articles:
HOLD_CURSOR and RELEASE_CURSOR in precompilers [[NOTE:2055810.6]]
Tuning Precompiler applications [[NOTE:73922.1]]
Workarounds for ORA-1000 [[NOTE:2060331.6]]
.



--------------------------------------------------------------------------------

Best regards
Frank Naude
Re: ABOUT SEQUENCE IN PL/SQL [message #369683 is a reply to message #369679] Fri, 11 August 2000 09:09 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
Hi,
You cannot use commit statement in Triggers..
remove that statement and try to create trigger..
Suresh
Previous Topic: $ORACLE_HOME/admin/ora/arch
Next Topic: RMAN-08002 starting full resync of recovery catalog ?
Goto Forum:
  


Current Time: Tue Oct 20 10:19:55 CDT 2020