How to fetch the cursor data in oracle stored procedure

Question

create or replace
PROCEDURE get_new
AS
    CUST_ID varchar2(100);
    ROUTERNAME_N VARCHAR2(100); 
BEGIN
    CURSOR c1 IS
    SELECT TRAFFIC_CUST_ID,ROUTERNAME INTO CUST_ID,ROUTERNAME_N
    FROM INTERFACE_ATTLAS
    WHERE rownum > 3;

    my_ename INTERFACE_ATTLAS.TRAFFIC_CUST_ID%TYPE;
    my_salary INTERFACE_ATTLAS.ROUTERNAME%TYPE;

    LOOP
        FETCH c1 INTO my_ename;
        FETCH c1 INTO my_salary;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(my_ename);
    end loop;
end;

I am new to oracle and stored procedure. I am trying to get the rows using cursor fetch, and getting following error:

PLS-00103: Encountered the symbol "C1" when expecting one of the following:
:= . ( @ % ;

Answer 1

Rewrite it like this:

create or replace
PROCEDURE get_new
AS
    my_ename INTERFACE_ATTLAS.TRAFFIC_CUST_ID%TYPE;
    my_salary INTERFACE_ATTLAS.ROUTERNAME%TYPE;
    CURSOR c1 IS
    SELECT TRAFFIC_CUST_ID,ROUTERNAME
    FROM INTERFACE_ATTLAS
    WHERE rownum > 3;
BEGIN
  open c1;    
    LOOP
        FETCH c1 INTO my_ename, my_salary;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(my_ename);
    end loop;
  close c1;
end;

Do not forget to open and close cursors. It always will print nothing because of rownum > 3; You wanted to type: rownum < 3;, didn't you?

Answer 2

create or replace
PROCEDURE get_new
AS
    CUST_ID varchar2(100);
    ROUTERNAME_N VARCHAR2(100); 
BEGIN
    CURSOR c1 IS
    SELECT TRAFFIC_CUST_ID,ROUTERNAME INTO CUST_ID,ROUTERNAME_N
    FROM INTERFACE_ATTLAS
    WHERE rownum > 3;

    my_ename INTERFACE_ATTLAS.TRAFFIC_CUST_ID%TYPE;
    my_salary INTERFACE_ATTLAS.ROUTERNAME%TYPE;

    LOOP
        FETCH c1 INTO my_ename;
        FETCH c1 INTO my_salary;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(my_ename);
    end loop;
end;

Cursor should be declare in the declaration part. Then have to open in begin-end section. In the declaration section you can not assign value to variable. When you are fetching value you can not select randomly value form cursor,

modified code:

create or replace
PROCEDURE get_new
AS
   no mean --CUST_ID varchar2(100);
  no means -- ROUTERNAME_N VARCHAR2(100); 

    CURSOR c1 IS
    SELECT deptno,job
    FROM emp;

    my_ename emp.deptno%TYPE;
    my_salary emp.job%TYPE;

BEGIN
    open c1;

    LOOP

       fetch c1 into my_ename,my_salary;
       -- FETCH c1 INTO my_ename;
        --FETCH c1 INTO my_salary;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(my_ename);
    end loop;
end;

execute get_new.

Answer 3

You should probably declare your cursor and your variables my_ename and my_salary in the dedicated section, i.e. before the BEGIN, and then open your cursor:

IS
    CUST_ID varchar2(100);
    ROUTERNAME_N VARCHAR2(100);
    C1 sys_refcursor;
    my_ename INTERFACE_ATTLAS.TRAFFIC_CUST_ID%TYPE;
    my_salary INTERFACE_ATTLAS.ROUTERNAME%TYPE;
BEGIN
    OPEN C1 for
    SELECT ...

Answer 4

You would have to declare the Cursor before BEGIN. You would use no INTO clause in the cursor declaration. Then you would have to OPEN the cursor. Then you would FETCH INTO my_ename, my_salary, not one after the other (you fetch rows, not columns). WHERE rownum > 3 returns no rows. As you don't want a first row, you will never get a second, third and fourth either.

And you can use an implicit cursor which is easier to deal with (no need to open, fetch and close explicitely):

BEGIN
  FOR rec IN
  (
    select traffic_cust_id, routername 
    from interface_attlas
    where rownum <= 3
  ) LOOP
     DBMS_OUTPUT.PUT_LINE(rec.traffic_cust_id || ': ' || rec.salary);
  END LOOP;
END;