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.
JelaVAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you can't tell how much data is in a cursor without fetching from it.  Unless your cursor includes a column that counts the entire result set you won't know how many rows until you fetch them all.

Instead of looping to fill your plsql table, why not bulk collect the cursor into the table in one step?  Much more efficient.
0
ytarkanCommented:
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 ......)
0
JelaVAuthor Commented:
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=''.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ytarkanCommented:
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
0
sdstuberCommented:
can you post your code?  the "some more selection on each element"  parts might be included in the original query itself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JelaVAuthor Commented:
I included all in the original query.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.