[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2618
  • Last Modified:

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 !)
0
srobidou
Asked:
srobidou
1 Solution
 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now