JelaV
asked on
How to check the size of the cursor
After opening a cursor for a dynamic sql query I would like to see if anything been selected before starting looping through the cursor. I can check when I started looping but it means that it would check for null as many times as many elements in the cursor which could be thousands. After opening the cursor I need to insert it into a plsql table which is to be returned back to the user.
Why dont you use
"CREATE TABLE {table_name} AS SELECT {sqlstatement of cursor}"
or if you would likte use an existing table you can do the same thing by
"INSERT INTO {table_name} ( SELECT ......)
"CREATE TABLE {table_name} AS SELECT {sqlstatement of cursor}"
or if you would likte use an existing table you can do the same thing by
"INSERT INTO {table_name} ( SELECT ......)
ASKER
Ok, after opening the cursor I need to perform some more selection on each element so I have to use loop. so if I do not check on null then it will give me an exception when I use select statement where eg some_id=''.
Personally I always try to avoid using cursors for many reasons primarly performans and many others.
But if you think that you realy need cursor unevitably, then may be the rowcount propery of cursor helps you or you may run the SQL statement and then check SQL%ROWCOUNT.
In any way I recommend to add your probable null field (some_id) to where criteria or use it inside null etc. other alternatives in order to avoid using cursor, if it is possible of course.
Since I dont knpw your situation and needs in detail I may not propose the best solution, just trying to findsomething appropriate
But if you think that you realy need cursor unevitably, then may be the rowcount propery of cursor helps you or you may run the SQL statement and then check SQL%ROWCOUNT.
In any way I recommend to add your probable null field (some_id) to where criteria or use it inside null etc. other alternatives in order to avoid using cursor, if it is possible of course.
Since I dont knpw your situation and needs in detail I may not propose the best solution, just trying to findsomething appropriate
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I included all in the original query.
Instead of looping to fill your plsql table, why not bulk collect the cursor into the table in one step? Much more efficient.