Oracle: Get the row count in a cursor when using a OPEN-FOR-USING statement

countcursorsoracle

Within an Oracle procedure, after opening a cursor for a select statement, I fail to find a mean to count the number of rows fetched.

OPEN mycursor FOR
SELECT * FROM TABLE;

-- mycursor%ROWCOUNT is always set to 0, even if the cursor has rows.
IF mycursor%ROWCOUNT = 0
THEN
  <error processing code here>
END IF;

This is expected, as documented at Oracles's documentation website:

The OPEN-FOR-USING statement associates a cursor variable with a
query, executes the query, identifies the result set, positions the
cursor before the first row in the result set, then zeroes the
rows-processed count kept by %ROWCOUNT.

So, except for running a second, redundant 'select count(*) from table', are there any other means to find out the number of rows within the cursor?

EDIT #1: I am not looping after loading the cursor; it is returned as is to the calling procedure. However, I must raise an exception if there are no row. These are the specifications.

Best Answer

Do the ROWCOUNT check after looping through the set. If ROWCOUNT will remain 0 if there were no data in the cursor. You could use a FOR LOOP construct like:

OPEN ....

FOR ... IN mycursor
LOOP
    /* Process data here */
END LOOP:

IF mycursor%ROWCOUNT = 0 ...

CLOSE mycursor;

Related Solutions

Mysql – How to COUNT number of rows with LIMIT

It sounds like you want to run the LIMIT, but still know how many rows would have been returned without the LIMIT. Check out FOUND ROWS

Mysql – How to return resultset from MySQL Stored Procedure using prepared statement

I will suggest you a solution create a log table like this

CREATE TABLE log_records
(
Panel_Id INT,
Project_Number_val VARCHAR(255)
);

In your code add lines as follows here i will insert those record into log_table which you was selecting please look the line /**Added by abdul */

    IF FoundCount = 1 THEN
        SET @Project_Number=Project_Number_val;

        SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');


    /*--Added by Abdul*/
        SET @insert_sql = CONCAT ('INSERT INTO log_records' ,'SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');
        PREPARE stmt_insert FROM @insert_sql;
        EXECUTE stmt_insert;
        DEALLOCATE PREPARE stmt_insert; 
    /**--End**/     

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

And then where loop end add line

SELECT * FROM log_records;

This will give you the desired result.

Also Write one line in your code before you open the cursor otherwise each procedure call will be appending data in that table.

TRUNCATE TABLE log_table;

Best Answer

Related Solutions

Mysql – How to COUNT number of rows with LIMIT

Mysql – How to return resultset from MySQL Stored Procedure using prepared statement

Related Question