Retrieve a select count(*) in Oracle inside a cursor

Question

I need to retrieve the number of rows in a SELECT COUNT(*) statement that is inside a cursor (in Oracle).

The following code should explain it clearly:

PROCEDURE Save(CF_CURSOR OUT "VPA"."CF_#Runtime".CF_CURSOR_TYPE) AS
    V_CF_CURSOR "VPA"."CF_#Runtime".CF_CURSOR_TYPE;
    CF_ROWCOUNT NUMBER;
    BEGIN
        OPEN V_CF_CURSOR FOR
        SELECT COUNT(*) INTO CF_ROWCOUNT FROM (
            SELECT * FROM "VPA"."Employee" -- returns 1 row
        ) WHERE ROWNUM <= 1;
        IF(CF_ROWCOUNT = 0) THEN
            -- DO SOMETHING BUT NEVER GOES HERE
        END IF;
        COMMIT;
    CF_CURSOR := V_CF_CURSOR;
    END;

Here, the value of CF_ROWCOUNT is never set. If I remove the cursor, everything works as expected. I have tried to use SQL%ROWCOUNT, but it does not work either. And, I cannot remove the cursor...

Thanks in advance!

Firstly it's not going to work as your syntax is incorrect. Secondly when you write "I have tried to use SQL%ROWCOUNT, but it does not work either" what doesn't work? Did you get error messages? What did you run? Lastly, are you simply trying to get a count or do you also want to use the data in employee? — Ben
– Ben, Commented Feb 27, 2013 at 8:57
Shouldn't you be doing a FETCH on the cursor before you can get a result? — user330315
– user330315, Commented Feb 27, 2013 at 9:18

Chris Cameron-Mills · Accepted Answer · 2013-02-27 09:02:11Z

2

Have you tried opening the cursor - which does a COUNT(*), then fetching that into the CF_ROWCOUNT variable instead of doing it as an INTO within the ref-cursor statement.

For example:

OPEN V_CF_CURSOR FOR SELECT COUNT(*) FROM "VPA"."Employee"; -- returns 1 row
FETCH V_CF_CURSOR INTO CF_ROWCOUNT;

answered Feb 27, 2013 at 9:02

Chris Cameron-Mills

4,6571 gold badge29 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vincium Over a year ago

Thank you for this solution. The main issue is that this code is auto-generated and I don't have full control on what gets generated. Your solution works. But I have decided to remove the cursor (and that works), because I only need to retrieve the number of rows fetched with the select statement and nothing else.

Collectives™ on Stack Overflow

Retrieve a select count(*) in Oracle inside a cursor

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related