-1

I am not sure why my 2nd DBMS_OUTPUT.OUT_LINE doesn't print out my list? I recieve anonymous block completed and the headings (RANK: BABY NAMES: ) print out however my requested list does not. Here is my code:

SET SERVEROUTPUT ON
SET VERIFY OFF;

DECLARE

V_SEARCH VARCHAR2(20):= '&SV_SEARCH';
V_ROWS NUMBER(11) := '&SV_ROWS';

CURSOR C_NAME IS
    SELECT RANK() OVER (ORDER BY CODE DESC)
    FROM (SELECT * FROM "NAME_LIST" WHERE "NAMES" LIKE (V_SEARCH) 
    ORDER BY CODE DESC)WHERE ROWNUM = V_ROWS;

R_NAME C_NAME%ROWTYPE;

BEGIN
    OPEN C_NAME;
    DBMS_OUTPUT.PUT_LINE('RANK:   BABY NAMES:');
    LOOP
    FETCH C_NAME INTO R_NAME;
        EXIT WHEN C_NAME%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(' '||V_ROWS||' '||V_SEARCH);
    END LOOP;
    CLOSE C_NAME;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error'); 
END;

As a result I receive this as my output: anonymous block completed RANK: BABY NAMES:

Myss T-Rose
  • 11
  • 2

2 Answers2

1

I would run your cursor query by itself to verify that you are getting data. My guess is your ROWNUM at the end is causing you to pull no data. Try this cursor query instead:

CURSOR C_NAME IS
SELECT RANK() OVER (ORDER BY CODE DESC)
FROM (SELECT ROWNUM AS RN, NL.* FROM "NAME_LIST" NL WHERE "NAMES" LIKE (V_SEARCH) 
ORDER BY CODE DESC)WHERE RN = V_ROWS;
CDurand
  • 54
  • 4
-2

ROWNUM=1 or it's inferior to something.

nemo
  • 1