Count on number of cursors opened

Database
Enthusiast

Count on number of cursors opened

Hi,
I have a question on the number of cursors opened in a procedure for below case as there is a restriction of only 15 cursors must be opened in any stored procedure.
if my main procedure has 5 cursors opened and sub proc has 3 cursors opened and closed in it. I was wondering how much would be the cursor count of my main procedure?
replace procedure Main_proc()
begin
call sub_proc()
end
3 REPLIES
Enthusiast

Re: Count on number of cursors opened

A session can have a max of 16 open result sets (Aka responses). it doesn't matter whether you are doing it in a procedure or in another proc called from that. The tally goes at session level not at procedure level.
Enthusiast

Re: Count on number of cursors opened

Thanks for your reply. I have one more weird question on

L1: FOR a as cursor_name1 cursor for select stmt
DO --operation
L2: For b as cursor_name2 cursor for select stmt
DO --operation
L3: For c as cursor_name3 cursor for select stmt
DO --operation
END FOR L3;
END FOR L2;
END FOR L1;

Does teradata compiler say this, "U have used 3 cursors(responses) already. so you are only left with remaining 13!!"
or "it says u have used and closed 3 cursors, so u r free to use remaining cursors!!"


I am very much concerned with this kind of structure because, I have to write about 10 L1's, so wondering wat is my cursor count?
Our business logic goes this way in oracle and I have to migrate it in teradata n have no time to think other logic as I almost at the end of deadline.

Please advise me.

rgs
Enthusiast

Re: Count on number of cursors opened

The operative term is “open cursors”. When you finish the outer FOR cursor loop you will have no open cursors. So you have 15 to use again. The 16 th one is used for the stored procedure response.

When you have the chance (time) to go over the logic I would seriously look at getting rid of the cursor loops and replace them with SQL relational statements, where possible. You will probably see a dramatic performance improvement unless your cursor loop is just a few rows.