Row Count

[lesstjm]

How do I count how many rows are in a ref cursor?

[sambavan]

Set a count variable or before exit do cursor%rowcount

Sam

[lesstjm]

to add a count variable would change my query, which I don't want to do and you can't exit with cursor%rowcount. It doesn't work for a ref cursor.

[amar]

Getting the number of rows in a ref cursor is not possible AFAIK. This is because of the fact that, when the ref cursor resultset is passed back to the calling app, there is no guarantee that all the rows of the resultset have been returned.

The only way would be to process the rows one by one and keep a count :(

Please correct me if I am wrong!


-amar

[smcdmc]

Try using SQLCA.SQLERRD(2). The value of this field is set to how many rows where selected. Here is a paste from an Oracle help.

For INSERT, UPDATE, DELETE, and SELECT INTO statements, sqlca.sqlerrd[2] records the number of rows processed. For FETCH statements, it records the cumulative sum of rows processed.

I know how to use this field when writing programs such as Cobol or C++ but I have not been able to figure out how to use it in PL/SQL.

Here is the link to the page I found:

http://otn.oracle.com/docs/products/...08arr.htm#5621

[amar]

SQLCA.* is a Pro*C/C++ or Pro*Cobol feature I think

-amar