any limitation on cursor size?

[anagarur]

Hi Guys:

I have a pl/sql stored procedure. Have a cursor in there that will retrieve say about 2 or more million records. Will this cause a problem. IS there a limitation on the maximum no. of rows we cna have in a cursor.

Please advise.

Thanks,
Aruna

[mber]

Yes, it is defined in "init.ora" file.

General speaking, it is allowed several hundreds of cursors to be opened. To be sure to close your cursors after regular period of time ---- don't exceed the max permitted number of opening cursors, this will cause errors.

Take care

[jmodic]

[QUOTE][i]Originally posted by mber [/i]
[B]Yes, it is defined in "init.ora" file.

General speaking, it is allowed several hundreds of cursors to be opened. To be sure to close your cursors after regular period of time ---- don't exceed the max permitted number of opening cursors, this will cause errors.

Take care
[/B][/QUOTE]

You've missed the point of the original question.

The answer is: No, there is no limmit on the number of rows the cursor will potentially retrive.

HTH,

[Mark_Woz]

Hi anagarur

Apologies if you already know the stuff I am listing.

I get the feeling from your original question that
your problem is more of an SQL performance tuning issue.
I suspect you are doing data migration/warehousing using PLSQL procedures/packages.

If your programs seem to 'hang' the session or take forever to return the rows from your query, you need to start looking at 'EXPLAIN PLAN', Cost and Rule based optimization, and very importantly, creating indexes on your join columns. If your SQL query is doing a whole lot of full table scans, you could wait until the cows come home before it returns with the rows.

How about showing us the SQL/PLSQL that you are running in your cursor ??