Mirjana, June 13, 2003 - 10:02 am UTC
Great response
Tony, June 13, 2003 - 10:16 am UTC
Very good point about using...
for x in ( select .... )
loop
and using views (there are none used on this project, just huge cursors).
Also project standards insist on creating cursors for selecting MAX values and COUNTs(single row queries) rather than select into.
Is this method good or bad?
June 13, 2003 - 10:48 am UTC
that method is dog slow, requires more code, incurrs more programming bugs.
select into is
o correct
o fast
o efficient
o more correct
o the only way real programmers do it. (maybe we can shame people into changing?)
How to use view in for c in (select * from emp) loop
Yong Wu, June 13, 2003 - 1:34 pm UTC
Tom,
I have used
for c in (select ...) loop
end loop;
in all my development.
I don't know or never use 'view' in the loop.
Can you give an example of using view? in
for c in (select ...) loop
end loop
Thanks,
June 13, 2003 - 2:26 pm UTC
sure
for x in (select * from all_users) loop
it is no different then a table.
ref cursor
A reader, June 13, 2003 - 6:13 pm UTC
Hi Tom,
If it's a ref cursor, is it true we only define ONE package spec in ONE schema, like
create or replace package pkg_refcur
is
type refcur is ref cursor;
end;
/
and use it(pkg_refcur.refcur) whenever we need a ref cursor?
thanks,
June 14, 2003 - 8:34 am UTC
exactly, yes. (and in 9i, just use SYS_REFCURSOR)
Reply to main question
Tony Johnston, July 02, 2003 - 7:10 am UTC
Tom,
I showed the developer your response and this was his reply
<quote>
My view on this is simple.
All of my cursor definitions are *fully parameterized* and hence can be considered to be individual (reusable) units of code. As a result, it is my view that they all should exist at the same level as functions and procedures.
Having the benefit of hindsight I would not now place the global cursor definitions in the package specification but in the package body. The only reason for this is one of scope.
</quote>
Can I have your comments on this please?
Thanks
July 02, 2003 - 8:36 am UTC
to each their own.
Me, I find
is
begin
open c(x,y,z);
loop
fetch c into ....
or
is
begin
for x in c(x,y,z)
loop
infinitely less readable then
is
begin
for x in ( select * from emp where .... )
loop
and I use views for big queries. All of my queries are *fully parameterized* as well. I just prefer implicit cursors to explicit ones and use them where ever practical.
At the end of the day -- it is more a matter of style then anything else.
Thanks
Tony, July 03, 2003 - 9:21 am UTC
I have used a mixture of the three (not really used views much, I've found that a lot of team leaders I've worked for seem to be scared of database objects, eg. TYPEs, VIEWs and even TRIGGERS) but I do agree with you that yours is much more readable and I will endeavour to use the method you use.
Thanks for a great site.
p.s. The three sample chapters from your new book are very good, I will place my order as soon as it is released.
A reader, August 02, 2003 - 1:46 am UTC
"
I don't have to worry that "oh, you know procedure P1 uses cursor_x, and I use cursor_x, and since they are the same cursor_x will we stomp on eachother?" --
you know, that problem you ALWAYS have with global variables.
"
Tom please explain and illustrate, suppose we have a cursor declared in a package spec, and it is called simultaneously
from two different places, what is the downside of it, what will happen. It is just a select, why should it make a difference even if it is called from 100 places simultaneously.
This is different from global variables. If session A sets the global variable to a value 1 , then session 2 calling it might have a problem.
Please discuss.
August 02, 2003 - 8:13 am UTC
try to open that cursor two times, without closing it. see what happens.
global variables are not shared over sessions. you've got that wrong.
session 1 and session 2
A reader, August 02, 2003 - 9:01 am UTC
In session one I did the following..
SQL> ed
Wrote file afiedt.buf
1 create or replace package test1 as
2 cursor c1 is
3 select deptno
4 from dept;
5* end;
SQL> /
Package created.
SQL> ed
Wrote file afiedt.buf
1 declare
2 lv_var number;
3 begin
4 open test1.c1;
5 fetch test1.c1 into lv_var;
6 dbms_output.put_line(lv_var);
7* end;
SQL> /
PL/SQL procedure successfully completed.
Then I opened session 2 and did the following
SQL> declare
2 lv_var number;
3 begin
4 open test1.c1;
5 fetch test1.c1 into lv_var;
6 dbms_output.put_line(lv_var);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> declare
2 lv_var number;
3 begin
4 open test1.c1;
5 fetch test1.c1 into lv_var;
6 dbms_output.put_line(lv_var);
7 end;
8 /
declare
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "SCOTT.TEST1", line 3
ORA-06512: at line 4
I infer two things from the above
1.A single cursor cannot be opened twice from the same session , if it is not closed the first time.
2.But from across different sessions it can be opened simultaneously, but only once. If we try to open it the second time from any session , it will fail.
Tom, how can we ensure that the cursor is closed , what are the different ways..
I did the following..
SQL> set serveroutput on
SQL> declare
2 lv_var number;
3 begin
4 close test1.c1;
5 open test1.c1;
6 fetch test1.c1 into lv_var;
7 dbms_output.put_line(lv_var);
8
9 end;
10 /
10
PL/SQL procedure successfully completed.
Lastly, my dept has data, but why is the dbms_output.put_line not showing any output.. above
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
August 02, 2003 - 9:08 am UTC
1) correct
2) correct -- each session has it's own data segment if you will, just like if you start up the same program two times in your OS. they have their own data and cursors are just "variables" like a number or date really.
If you follow my standard -- don't use globals -- you don't have to worry about it.
If you use globals AND the cursor is left open -- it is what we call "a bug". You cannot (should not) use "cursor%isopen" and close it -- as that might totally mess up some other process. A cursor should be closed when you are done with it and if it is not -- you have a bug to fix.
lastly, your output is there -- look at the 10 right above plsql procedure successfully completed.
each session has it's own data segment
A reader, August 02, 2003 - 9:14 am UTC
"each session has it's own data segment"
Tom please elaborate on the above. For each session what does this data segment hold.
August 02, 2003 - 10:00 am UTC
whatever you want -- it is your plsql global variables that are in there.
why not fail the cursor
A reader, August 02, 2003 - 9:17 am UTC
I find it interesting that , you admit that leaving a cursor open is a very bad idea, but on the other hand the software allows to do so.
1.Why cant oracle come up with an enhancement and make it impossible to leave a cursor open, i.e. make it a must to close it.
2.Secondly, what is a memory leak due to leaving cursors open, is the scenario we are discussing any way related to it.
August 02, 2003 - 10:02 am UTC
That isn't the point.
the software must allow you to do so.
it can be very very useful.
the bad practice is leaving it open when you didn't mean to, after you were done using it.
my point was -- if your code ever tries to open a cursor that is already opened -- that is most likely a bug in your code. Just like two subroutines in a VB program both opening the same file at the same time (you sort of get "garbage" in that file as a result)
1) we do already. if you use local variables. done. if you use globals, you get the POWER of leaving them open, coupled with the responsibility of managing them.
2) leaking memory is much like leaking a cursor. you opened a resource, you never close it and never reference it again, you've leaked that resource. do it often enough and you run out of that resource.
1+1 != 2
A reader, August 02, 2003 - 12:07 pm UTC
Tom
Somehow your statements are not matching up.
You say it is a bad practice, but at the same time you say
-
if you use globals, you
get the POWER of leaving them open, coupled with the responsibility of managing
them.
-
Please tell where can this be useful, where can this be used powerfully, in what scenario, to handle what, accomplish which kind of functionality?
And what does managing them amount to? Well, I leave it open, in this context what does 'managing them' amount to ?
August 02, 2003 - 12:20 pm UTC
global variables, in general, are a bad practice. They are easily abused by programmers that don't know any better or get a little "lazy" (oh, bother, don't add yet another formal documented parameter -- lets just put a global up here. we'll remember to set it properly everywhere we need to).
Sometimes -- they are the only way to do it (think about if you wanted to know how many times a procedure in a package was invoked in your session -- perhaps a global variable would be useful and efficient there. In this case, I would hide it in a package body -- NOT the spec, so the procedure in the body could increment it and then provide a "get" function to return its value upon demand.
Sometimes -- like in a trigger that wants to communicate information back to the caller -- it is the only way. The trigger can set variable values in a package spec that other procedures/function can then read.
Global variables are a programming tool -- easily and frequently abused by those that do not yet know any better (haven't been burned hugely by them yet -- it'll happen, 100% sure on that) or those that have gotten lazy in their programming.
Not always abused, easily abused.
I do not have a real world case where by leaving a global cursor open in a package spec or body would be considered "good". I'm sure someone has one -- I just never do it.
A rationale for exposing cursors in package spec
Stew Stryker, November 07, 2003 - 2:53 pm UTC
I publish a cursor in the package specification when I need to share that cursor with another procedure. In my case, I sometimes need to run the same cursor in the automated test package. It's not often, but I've done it twice in the past 4 months.
In a specific example where I'm creating a summary table, I use the public cursor compare the original query result with the results in the summary table.
If anyone care, I recently started using the Test-Driven Development methodology (as espoused by Kent Beck and other XPers) and the EXCELLENT FREE utPLSQL automated testing suite from Steven Feuerstein. It has totally changed how I program, all for the better. My quality's much higher and the work doesn't take any longer than before.
To bring this shameless plug back on topic, my test package runs the same query as my main program and I use it to compare the results. So it has to be public.
November 07, 2003 - 5:26 pm UTC
and now you have a global, ugh.
don't see why your test package -- which should be calling UNITS (subroutines) needs access to something that should be private. SQL is not the interface here -- subroutines that take inputs and produce outputs are.
I still haven't seen the light on this one. I cannot understand why you need "the sql"
Desperately Need Help
Bhushan, June 22, 2006 - 1:30 pm UTC
Hi Tom,
I observed a very peculiar thing and moreover could not find the explanation for it on this site :-(
I have an cursor declared in Package Specification and i have one of the global variable in the 'select' for eg
CREATE OR REPLACE PACKAGE testing
x varchar2(3)='ABC';
cursor test is
select x pt2 from table1;
pr1 test%rowtype;
end testing;
CREATE OR REPLACE PACKAGE BODY testing is
for c1 in test loop
/* now when i try to print c1.pt2 i get null but if i mention the cursor itself as 'select nvl(x,'n') pt2 from table1' then i do not get 'n' but the value of x that i am supposed to or expecting.
Any help/hint would be highly appreciated.
Thanks a million in advance
DB is Oracle8i Enterprise Edition Release 8.1.7.4.0
June 22, 2006 - 2:55 pm UTC
give complete example and cut and paste it straight from sqlplus.
Local and global cursors - efficiency?
Chandini, June 23, 2006 - 4:26 am UTC
Hi Tom,
When comparing global to local cursors you said (right at the beginning of this thread) that the local cursors are marginally more efficient CPU wise than the global ones.
Could you please elaborate on the this? Why/How would it be more efficient to use a local cursor?
Also, in terms of creating views, do you follow any 'set of rules' to determine when/how many views you need in an appl?
Thank you for your help.
Chandini
June 23, 2006 - 10:17 am UTC
we were really comparing implicit to explicit cursors....
the only rules regarding the number of views are:
create as many as you need
do not create less than you need
do not create more than you need
sort of like "how many procedures should a package have" - exactly as many as you need.
Bhushan, June 23, 2006 - 8:42 am UTC
Here is the actual cursor that we use...
CREATE OR REPLACE PACKAGE Test AS
/* Other variables initialized */
PT1 VARCHAR2(10) := 'PRODUCTION';
PT2 VARCHAR2(3) := 'EHS';
/*For Default DropDowns*/
almlmt_dd VARCHAR2(25) := 'APPLICABLE ALARM LIMIT'; --030206 Bhushan
/*------------------queries for package----------------------------------------*/
CURSOR UNPROCESSED_EVENTS IS
SELECT NVL(C.PLANT_TYPE, PT1) PLANT_TYPE,
A.SITE_ID,
A.PLANT_ID,
UPPER(A.TAG_NAME) TAG_NAME,
A.EVENT_START_TIME,
A.EVENT_END_TIME,
NVL(A.DENT_VALUE, 0) DENT_VALUE,
NVL(A.DENT_COST, 0) DENT_COST,
A.TRIGGER_STATUS,
A.SEND_VISUAL_ALARM,
A.PROCESS_STATUS,
A.EVENT_NUMBER,
NVL(A.NON_CONFORMANCE_TYPE, B.EVENT_NAME) NON_CONFORMANCE_TYPE,
B.TAG_IDENTIFIER,
B.EVENT_NAME,
B.PROCESS_LINE,
B.VISUAL_ALARM,
B.COMMODITY_VALUE_CUR,
B.COMMODITY_GSU_T1,
B.COMMODITY_SOC_T1,
B.COMMODITY_UNIT_GSU,
B.COMMODITY_UNIT_SOC,
NVL(B.NO_ALARM, 'N') NO_ALARM,
NVL(B.EMAIL_ALARM, 'N') EMAIL_ALARM,
NVL(B.ONCE_PER_EVENT, 'N') ONCE_PER_EVENT,
NVL(B.EVENT_BEGINS, 'N') EVENT_BEGINS,
NVL(B.EVENT_ENDS, 'N') EVENT_ENDS
FROM GMN04.GMN04_EVENT_INPUT_IP21 A,
GMN04.GMN04_EVENT_MASTER B,
(SELECT DISTINCT SITE_ID, PLANT_ID, NVL(PT2,'X') PLANT_TYPE
FROM GMN04_EHS_SCENARIO_TAG) C
WHERE A.SITE_ID = B.SITE_ID
AND A.PLANT_ID = B.PLANT_ID
AND B.SITE_ID = C.SITE_ID(+)
AND B.PLANT_ID = C.PLANT_ID(+)
AND UPPER(A.TAG_NAME) = UPPER(B.TAG_NAME)
AND A.ERROR_FLAG = 'N'
AND B.EVENT_STATUS = ES1
AND A.PROCESS_STATUS = PS1
AND A.TRIGGER_STATUS IN (TS1, TS2, TS3, TS4)
AND TO_DATE(TO_CHAR(A.MODIFIED_ON, TF1), TF1) >
P_LAST_PROCESS_DATE - ((1 / 24 / 60) * 6);
P_R1 UNPROCESSED_EVENTS%ROWTYPE;
/*After this various Procedures/functions declared not important */
end Test
Here is the package Body
Pasting only till the 'Tricky' part
CREATE OR REPLACE PACKAGE BODY Test IS
/*main calling procedure------------------------------------*/
PROCEDURE PROCESS_EVENT IS
BEGIN
BEGIN
P_ERR_DESC := 'WHILE DELETING PREVIOUS ENTRIES FROM GMN04_VISUAL_ALARM';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GMN04.GMN04_VISUAL_ALARM';
EXCEPTION
WHEN OTHERS THEN
V_RESULT := LOG_OUT(P_ERR_DESC || SQLERRM);
END;
/*get the last processing date */
P_LAST_PROCESS_DATE := GET_LAST_PROCESS_DATE;
dbms_output.put_line ('value of pt2 is '|| PT2);
--This Line gives me the value as 'EHS'
FOR C1 IN UNPROCESSED_EVENTS LOOP
dbms_output.put_line ('value of C1 Cursor is '|| c1.plant_type);
/* The Above DBMS.Output line gives me NULL if i remove the NVL function from the cursor's 'from' part 'SELECT DISTINCT SITE_ID, PLANT_ID, NVL(PT2,'X') PLANT_TYPE
FROM GMN04_EHS_SCENARIO_TAG'
However when i have the NVL Function i do not get 'X' but i get the output as 'EHS',also if i directly replacce the PT2 in the cursor with 'EHS' everything works fine */
I am more than eager for your reply...
Saludos,
Bhushan
June 23, 2006 - 10:22 am UTC
ok, let me be "more clear"
give me a 100% complete, yet concise, however entirely self contained, but small test case
eg: reproduce this in it's entirety (from create table onwards) with as little code as you can.
Heck, if you can just use dual and not create any tables - great.
for you see - I want/need to run it myself (and I find many (most) times that I find my own mistakes when I whittle the problem down to a 100% complete, yet concise test case)
Bhushan, June 26, 2006 - 2:31 am UTC
Hi Tom,
I could not replicate the scenario successfully.However i have tried various options and discovered one more astonishing thing.PLease help!
Pasting the cursor(this cursor is in Package Specification ) again...
CURSOR UNPROCESSED_EVENTS IS
SELECT NVL(C.PLANT_TYPE, PT1) PLANT_TYPE,
A.SITE_ID,
A.PLANT_ID,
UPPER(A.TAG_NAME) TAG_NAME,
A.EVENT_START_TIME,
A.EVENT_END_TIME,
NVL(A.DENT_VALUE, 0) DENT_VALUE,
NVL(A.DENT_COST, 0) DENT_COST,
A.TRIGGER_STATUS,
A.SEND_VISUAL_ALARM,
A.PROCESS_STATUS,
A.EVENT_NUMBER,
NVL(A.NON_CONFORMANCE_TYPE, B.EVENT_NAME) NON_CONFORMANCE_TYPE,
B.TAG_IDENTIFIER,
B.EVENT_NAME,
B.PROCESS_LINE,
B.VISUAL_ALARM,
B.COMMODITY_VALUE_CUR,
B.COMMODITY_GSU_T1,
B.COMMODITY_SOC_T1,
B.COMMODITY_UNIT_GSU,
B.COMMODITY_UNIT_SOC,
NVL(B.NO_ALARM, 'N') NO_ALARM,
NVL(B.EMAIL_ALARM, 'N') EMAIL_ALARM,
NVL(B.ONCE_PER_EVENT, 'N') ONCE_PER_EVENT,
NVL(B.EVENT_BEGINS, 'N') EVENT_BEGINS,
NVL(B.EVENT_ENDS, 'N') EVENT_ENDS
FROM GMN04.GMN04_EVENT_INPUT_IP21 A,
GMN04.GMN04_EVENT_MASTER B,
(SELECT DISTINCT SITE_ID, PLANT_ID, PT2 PLANT_TYPE
FROM GMN04_EHS_SCENARIO_TAG) C
WHERE A.SITE_ID = B.SITE_ID
AND A.PLANT_ID = B.PLANT_ID
AND B.SITE_ID = C.SITE_ID(+)
AND B.PLANT_ID = C.PLANT_ID(+)
AND UPPER(A.TAG_NAME) = UPPER(B.TAG_NAME)
AND A.ERROR_FLAG = 'N'
AND B.EVENT_STATUS = ES1
AND A.PROCESS_STATUS = PS1
AND A.TRIGGER_STATUS IN (TS1, TS2, TS3, TS4)
AND TO_DATE(TO_CHAR(A.MODIFIED_ON, TF1), TF1) >
P_LAST_PROCESS_DATE - ((1 / 24 / 60) * 6)
/*this is the line in package body where i use the cursor*/
for c1 in UNPROCESSED_EVENTS loop
dbms_output.put_line ('value of C1 Cursor is '|| ascii(substr(c1.plant_type,2,1))||','||length(c1.plant_type));
This give me length as 3 but ascii value 0 although PT2 is innitialzed to 'EHS'..i changed PT2 to 'EH' and checked it then gives me the Length as 2 but still ascii value 0
so what i did was i changed the select in cursor's from statement to
SELECT DISTINCT SITE_ID, PLANT_ID, PT2||'' PLANT_TYPE
FROM GMN04_EHS_SCENARIO_TAG
and then it works just fine..is this informatin enough to understand my problem..else i will have to cpy paste the whole code..it isnt too big although..
Thanks in advance
Bhushan
June 26, 2006 - 7:42 am UTC
if and when you give me:
...
give me a 100% complete, yet concise, however entirely self contained, but small
test case
........
CONCISE being very important (make up a table to work with here, few columns, small)
COMPLETE being very important (if I cannot cut and paste it and run it, forget it)
Assign value from table to global variable in package specification
A reader, November 22, 2012 - 7:37 am UTC
In one of application the global variable is defined in a package specification and value for SMTP Server is hard coded in it. Now the issue which it can raise is that the same SMTP server will not be available on all the environments (Development, Staging, and Production). As this global variable is being used in all the applications so it will have large change scope. What would be best approach to handle it so that this implementation will keep intact and we start to put value of SMTP Server in database table and pull it in global variable?
November 29, 2012 - 6:45 am UTC
put the value in a table (a single row, single column table).
ops$tkyte%ORA11GR2> create table smtp_table ( smtp_hostname varchar2(128) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into smtp_table (smtp_hostname) values ( 'my.host.name' );
1 row created.
and make a result cache function:
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace
2 function get_smtp_name return varchar2
3 result_cache
4 as
5 l_smtp_hostname smtp_table.smtp_hostname%type;
6 begin
7 select smtp_hostname
8 into l_smtp_hostname
9 from smtp_table;
10
11 return l_smtp_hostname;
12 end;
13 /
Function created.
that way, the table will be read ONCE after database start up and the result will come from the SGA for every other session.
Assign value from table to global variable in package specification
A reader, December 03, 2012 - 7:13 am UTC
Thank you.
How we will assign value to global variable from this function. The issue is that this global variable is being called from many places.
December 03, 2012 - 9:36 am UTC
???
I showed you everything?????
you will not have a global variable, you will have a function
this function will be result cached.
you will put the value this function should contain in a table.
that table will be read once.
so - instead of changing a constant in a package, you update a row in a table.
Assign value from table to global variable in package specification
A reader, December 03, 2012 - 11:27 pm UTC
Thank you for explaining again. I got your point at the first place. The issue is that it is already build application and from many Procedures (procedures in different packages) they call a global variable declared in common package to get SMTP Server address. If we move to use the function implementation then we have to change in all the places of application from where global variable was being called. Therefore i was asking that if there any way that we feed this value to global variable from table so we have not to change the call from existing procedures.
December 04, 2012 - 7:41 am UTC
why?
how will they even know the difference between:
create package my_pkg
as
variable constant number := 5;
end;
and
create package my_pkg
as
function variable returns number;
end;
??????????????
they reference my_pkg.variable in all cases??
what code beyond the constant package would have to be modified???? I'm confused???
Assign value from table to global variable in package specification
A reader, December 05, 2012 - 4:38 am UTC
Incomplete information creates situations like this. Sorry i could not provided you details. The variable is declared in package specification and value is hard coded in it. The variable is called from every where. So i need to assign value to this variable so that there will be no change in code.
A reader, December 05, 2012 - 7:16 am UTC
As Tom is explaining, you need not change "the call from existing procedures".
Currently you have a global variable (eg. my_smtp_host) in a package (eg. my_pkg) that will be accessed by your existing procedures as my_pkg.my_smtp_host.
After you create a function with the same name as the global variable (and remove the global variable definition), your existing procedures can continue to use my_pkg.my_smtp_host. They will now be accessing the function instead of the global variable.
Assign value from table to global variable in package specification
A reader, December 05, 2012 - 9:43 pm UTC
Thank you, so clear and i missed to follow it.
Maximum open cursor exceede error .
Praveen, June 24, 2019 - 12:12 pm UTC
Hi Tom,
We are using a package and we closed the cursor in that packaged procedure but still no of open cursor on that package is high.
create or replace PACKAGE BODY PKG_imo
IS
PROCEDURE GETimo(logid VARCHAR2,accno VARCHAR2,mdcIN INTEGER,miaccno OUT BLOB) IS
CURSOR imcur(accno VARCHAR2,mdc INTEGER) IS
/* SELECT miaccno FROM tempimp
WHERE accno= miaccno
AND NDC = mdc; */
SELECT iaccno FROM img
WHERE CAWBNO = miaccno
AND NDC = mdc;
BEGIN
IF checksceurity(logid,miaccno) THEN
/* BEGIN
INSERT INTO tempimg SELECT * FROM img WHERE accno= miaccno
AND NDC = mdc;
EXCEPTION
WHEN OTHERS THEN
NULL;
END; */
OPEN imcur(miaccno,mdc);
FETCH imcur INTO imaccno;
CLOSE imcur;
END IF;
END;
FUNCTION checksceurity(logid IN VARCHAR2,miacccno IN VARCHAR2) RETURN BOOLEAN
IS
BEGIN
RETURN TRUE;
END;
END Pkg_imo;
June 24, 2019 - 1:18 pm UTC
You can check which cursors are still open by querying v$open_cursor.
maximum Cursor exceeded issue
Praveen, June 24, 2019 - 1:28 pm UTC
Hi I checked in v$open_cursor , sql_text is shown like this
begin
img_cur.getimage(:1,:2,:3,:4);
end;
/
Im not getting from this how I can limit the cursor limit as no loops in my plsql program.
The above plsql pacakge called from java.
June 24, 2019 - 1:34 pm UTC
That's the only statement that appears?
If that's the main offender, you need to hunt through your code to find what's executing this.
Maximum Cursor exceeded
Praveen Darla, June 25, 2019 - 5:24 am UTC
Yes that is the statement it is appearing , I checked the plsql code looks fine for me there are no loops and cursor opened and closed properly from db side . Can you please suggest the possible steps how I can debug the problem.
I'm confused how to solve this problem.
Thanks ,
Praveen Darla
June 25, 2019 - 7:55 am UTC
Are there other statements that appear in open cursor? Surely that can't be the only one? Where are they coming from?
What does the Java code do? Does that open but not close cursors? Are you using a connection pool?
What is the parameter OPEN_CURSORS set to? Is it possible that this is set too low and your code has more than this many cursors open at once? Can you change your code to close cursors earlier?