Row count from REF CURSOR

[g***@gmail.com]

I'm sure I'm just missing something, but can't seem to find the answer.

We have a function that returns a REF CURSOR using the OPEN..FOR
syntax. The SQL for the cursor is dynamically built in the procedure
based on a series of configurations, such that the cursor will always
be different based on the most current configuration. The generalized
approach is that the dynamic SQL string is stored in a temporary table
and then retrieved.

SELECT to_char(SQLTranslationString)
INTO vcSQLTranslationString
FROM TranslationString
WHERE AuditID = iAuditID;

OPEN returnCursor FOR vcSQLTranslationString;

In this particular system, we have stringent audit requirements, such
that we have to know the final target row count. I know the calling
system that initiated the procedure can always call back and tell me
the answer, but I was wondering if I could force the procedure to do
it. I tried the following:
iRowsProcessed := returnCursor%ROWCOUNT;
, but found in the documentation that this will always be zero.

Any suggestions?

[Jim Kennedy]

Post by g***@gmail.com
I'm sure I'm just missing something, but can't seem to find the answer.
We have a function that returns a REF CURSOR using the OPEN..FOR
syntax. The SQL for the cursor is dynamically built in the procedure
based on a series of configurations, such that the cursor will always
be different based on the most current configuration. The generalized
approach is that the dynamic SQL string is stored in a temporary table
and then retrieved.
SELECT to_char(SQLTranslationString)
INTO vcSQLTranslationString
FROM TranslationString
WHERE AuditID = iAuditID;
OPEN returnCursor FOR vcSQLTranslationString;
In this particular system, we have stringent audit requirements, such
that we have to know the final target row count. I know the calling
system that initiated the procedure can always call back and tell me
the answer, but I was wondering if I could force the procedure to do
iRowsProcessed := returnCursor%ROWCOUNT;
, but found in the documentation that this will always be zero.
Any suggestions?

You won't know the record count until you fetch the last row.
Jim

[Mark C. Stock]

"Jim Kennedy" <jim dot scuba dot kennedy at gee male dot com> wrote in
message news:o4ydnc-ww-***@comcast.com...
:
: <***@gmail.com> wrote in message
: news:***@g44g2000cwa.googlegroups.com...
: > I'm sure I'm just missing something, but can't seem to find the answer.
: >
: > We have a function that returns a REF CURSOR using the OPEN..FOR
: > syntax. The SQL for the cursor is dynamically built in the procedure
: > based on a series of configurations, such that the cursor will always
: > be different based on the most current configuration. The generalized
: > approach is that the dynamic SQL string is stored in a temporary table
: > and then retrieved.
: >
: > SELECT to_char(SQLTranslationString)
: > INTO vcSQLTranslationString
: > FROM TranslationString
: > WHERE AuditID = iAuditID;
: >
: > OPEN returnCursor FOR vcSQLTranslationString;
: >
: > In this particular system, we have stringent audit requirements, such
: > that we have to know the final target row count. I know the calling
: > system that initiated the procedure can always call back and tell me
: > the answer, but I was wondering if I could force the procedure to do
: > it. I tried the following:
: > iRowsProcessed := returnCursor%ROWCOUNT;
: > , but found in the documentation that this will always be zero.
: >
: > Any suggestions?
: >
: You won't know the record count until you fetch the last row.
: Jim
:
:

so, what you may want to do, if your audit requirements are such, is to
return a set of ROWIDs, which will give you the count, then use that set of
ROWIDs to fetch the actual rows -- that way the record select is done once,
you know up front the total number of rows, and the data fetch uses the
exact same set of rows (unless some are deleted in the meanwhile)

++ mcs