No of Rows for DBMS_SQL.EXECUTE

[surajitmitra78]

Need to get the TOTAL count of no of rows that is retreived from SELECT statement for DBMS_SQL.EXECUTE. Is there any subprogram in DBMS_SQL.EXECUTE which gets me the no of rows retreived from a SELECT STATEMENT in DBMS_SQL.EXECUTE.

It should function the way SQL%ROWCOUNT functions.

Things which dont satisfies the above requirement in DBMS_SQL is as follows:
DBMS_SQL.EXECUTE_AND_FETCH
DBMS_SQL.FETCH_ROWS
DBMS_SQL.LAST_ROW_COUNT

Could any one put some thought into it. :)

[bobiander]

Hi,
I try to find some function in DBMS_SQL , but there's nothing to do this.
But I can propose you two workarounds.
1. If you fetch data row-by-row you can count rows in varaible.
2. If you fetch data in bulk array , you can get array row's count

I hope this help you.

[gamyers]

Trick question.
DBMS_SQL.EXECUTE doesn't fetch any rows. In fact, for a SELECT it doesn't really do anything much.
Basically, you need to fetch all the rows before you know how many rows you are going to fetch.

Or at least fetch one row, with a nice analytic function to tell you how many rows will be fetched, such as :
SELECT table_name, column_name, COUNT(*) OVER ()
FROM user_tab_columns

[surajitmitra78]

thanks,
i can live with the solution.