OCI row count

Is there a way with Oracle OCI to get the number of rows that have been selected before calling OCIStmtFetch.

i.e after the statement:
SELECT * FROM X;
How can I retrieve the number of selected rows (without changing the select statement !)
srobidouAsked:
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.

Mark GeerlingsDatabase AdministratorCommented:
I don't know OCI, but one option may be to do two queries, instead of one.  The first one would be:
select count(*) from x;

That would give you the number of rows.
0
bkowalskiCommented:
How about using the implicit cursor attributes?  I'm not very experienced in programming, so I don't know if this can be done.
-Brad
0
HaukeCommented:
The database doesn't know the number of rows until the last fetch.
A second query doesn't retrieve the exact number of rows, because there can be inserts/deletes from other sessions.
0
bkowalskiCommented:
If you are using a second query to retrieve the exact number of rows, you can set your transaction as ready only to get consistent point in time results and not be affected by inserts/deletes from other sessions.  This is if you are not doing any changes to the data.  If you are changing data, you can use the serializable transaction isolation level or lock the entire table.
0
Andrei RodionovCommented:
That's right, there is no way to get the number of rows that have been selected before calling OCIStmtFetch.

The optimal decision is using additional query like 'select count(*) from x' and oexfet (for OCI 7.x) or OCIStmtExecute (for OCI 8.0) calls.

Andrew



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
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.