Re: Row count from REF CURSOR

"Jim Kennedy" <jim dot scuba dot kennedy at gee male dot com> wrote in message news:o4ydnc-ww-Rh3W7enZ2dnUVZ_sKdnZ2d_at_comcast.com...
:
: <greg.sewell_at_gmail.com> wrote in message
: news:1140012179.431100.175920_at_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 Received on Wed Feb 15 2006 - 09:00:42 CST