| Cursor Related issue. [message #38267] |
Fri, 05 April 2002 08:27  |
Kumar
Messages: 115
Registered: December 1998
|
Senior Member |
|
|
My application has a set of 76 procedures which run when i click on a button created in Forms 6i.
but when my 65 th procedure is executing it gives the below mentioned error.I have already set the max_cursors_open variable in INIT.ORA File to 700 (this setof procedures do an insert/update of one set of tables into another set of tables),the way the cursor is used i presume Explict CLOSE <cursor> stmt is not needed for closing the cursor,when i compile this procedure it does not give any error.it gives this error only during runtime.I need to use the cursor in the same way as it is shown in the below sample procedure.
ORA-00604 : error occured at recursive SQL LEVEL 1
ORA-01000 : maximum open cursors exceeded.
My procedure looks like below.
PROCEDURE I66_OFFICE_MST (O_STATUS OUT VARCHAR2) IS
temp1 number(5):=0;
current_status varchar2(1);
current_phase number(5);
counter number:=0;
cursor c1 is select rowid,
OFF_PRO_ID,
OFF_ID,
OFF_TYPE,
OFF_NAME
FROM MT_OFFICE_MST;
BEGIN
BEGIN
select imp_status,imp_phase INTO current_status,current_phase
from import_control
where imp_filename=:filename;
END;
IF current_phase=65 AND current_status='P' THEN
FOR rec IN c1
LOOP
select count(*) INTO temp1
from T_OFFICE_MST
where OFF_ID =REC. OFF_ID ;
IF temp1=0 THEN
BEGIN
Insert INTO T_OFFICE_MST (
OFF_PRO_ID,
OFF_ID,
OFF_TYPE,
OFF_NAME
)
Values(
rec.OFF_PRO_ID,
rec.OFF_ID,
rec.OFF_TYPE,
rec.OFF_NAME);
EXCEPTION
WHEN OTHERS then
O_STATUS:= 'MT_OFFICE_MST: INSERT FAILED ‘||SQLERRM ;
END;
ELSE
BEGIN
UPDATE T_OFFICE_MST set
OFF_PRO_ID=rec.OFF_PRO_ID,
OFF_ID=rec.OFF_ID,
OFF_TYPE=rec.OFF_TYPE,
OFF_NAME=rec.OFF_NAME
where OFF_ID =rec.OFF_ID;
EXCEPTION
WHEN OTHERS then
O_STATUS:= 'MT_OFFICE_MST: UPDATE FAILED ‘ ||SQLERRM;
END;
END IF;
counter:=counter+1;
IF mod(counter,100)= 0 then
COMMIT;
END IF;
END LOOP ;
END IF;
END;
Can anybody give the reason for this error.
Thanks in advance
kumar
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
