0

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!

Vincium
  • 3
  • 1
  • 2
  • 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 Feb 27 '13 at 08:57
  • Shouldn't you be doing a `FETCH` on the cursor before you can get a result? –  Feb 27 '13 at 09:18

1 Answers1

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;
Chris Cameron-Mills
  • 4,587
  • 1
  • 27
  • 28
  • 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. – Vincium Mar 01 '13 at 08:52