CODEWITHSUNDEEP
databaseplsqloracle10gcursor
Sagar Patni
Sagar Patni

Reputation: 312

Pl/Sql open cursor problems

I have code like, 

create or replace function ... return types.cursortype 
as 
    l_cursor    types.cursorType; 
begin 
    open l_cursor for select ... ; 
    return l_cursor; 
end;

this code is used to return hundreds of records daily. Will these open cursors cause any memory leak issue ?

Is there any way to close such unclosed cursors in the procedure only ?

Any SQL for keeping track of such open cursor ?

If its duplicate please add references..

Upvotes: 0

Views: 958

Answers (1)

void
void

Reputation: 7890

Will these open cursors cause any memory leak issue ?

No if you don't leave them opened after using (for explicit cursors), when opening a cursor, oracle creates a context area, PL/SQL controls the context area through a cursor which holds the result returned by a SQL statement. and for implicit cursors there is nothing to worry about

Is there any way to close such unclosed cursors in the procedure only ?

You can, but don't, because you opening it for using outside the procedure or function which you really don't know when and where it will be used and how long the using will take, and you have to close the cursor after finishing the use of it. (the cursor will open when you call the procedure or function)

Any SQL for keeping track of such open cursor ?

Yes there are, you can query v$sesstat, v$statname and v$session SYS views:

select *
from v$sesstat ss, v$statname sn, v$session s
where ss.statistic# = sn.statistic#  and s.sid=ss.sid
and sn.name = 'opened cursors current';

here there is a good article on monitoring the cursors.

Upvotes: 1

Related Questions