Reference cursor in Oracle Sql Developer

Question

I need to write a reference cursor in Oracle SQL developer and I have no clue how its written. I just know basic queries.

I have written following cursor to best of my knowledge after looking up into various example. I am getting an compile error "Missing or Invalid option".

Can anyone help me out?

Step 1: I created a new procedure in Oracle sql developer version 4. Step 2: I wrote the following cursor

DECLARE
routeid  VARCHAR2(10);
cursor     c1 IS
SELECT shipment_id FROM SHIPMENT
WHERE shipment_id = 20;

BEGIN
OPEN c1;
 LOOP
   FETCH c1 INTO routeid;
   EXIT WHEN c1%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(routeid);
 END LOOP;
END;
/

Thank you in advance for the help.

Answer 1

Better way to do simple cursor

CREATE OR REPLACE PROCEDURE Test_cursor (Out_Pid OUT VARCHAR2) AS 
cursor  c1 IS
SELECT shipment_id,p_id FROM test
WHERE shipment_id = 99;

c1_rec c1%rowtype;

BEGIN
 OPEN c1;
  LOOP
  FETCH c1 INTO c1_rec;
  EXIT WHEN c1%NOTFOUND;

  Out_Pid := c1_rec.p_id;
  DBMS_OUTPUT.PUT_LINE('Result from query '||c1_rec.p_id );
  DBMS_OUTPUT.PUT_LINE('Result from out parameter '||Out_Pid );
 END LOOP;

  END Test_cursor;