How to print the cursor values in oracle?

Question

I am new to oracle and I am learning cursors.My table City has two columns city_id,city_name.So,this is what I tried:

DECLARE
  CURSOR city_list is
  SELECT * from OT.City;

  v_list SYS_REFCURSOR;
BEGIN
  OPEN city_list FOR
   v_list := city_list;
   DBMS_OUTPUT.PUT_LINE(v_list.city_id);
   EXIT WHEN city_list%NOTFOUND;
  CLOSE city_list;
END;
/

i am trying to assign the data of cursor to the newly declared value v_list SYS_REFCURSOR;.But the output is coming as error at v_list := city_list;.How can I assign all the values of cursors to the another cursor directly or is there any other methods?

Answer 1

You can still use SYS_REFCURSOR by locating between OPEN and FOR clauses, and followed by your cursor, and then

need to add LOOP FETCH INTO... END LOOP template and Close the opened cursor after you current code :

DECLARE
  v_list SYS_REFCURSOR;
  v_city_id   city.city_id%TYPE;
  v_city_name city.city_name%TYPE;
BEGIN 
  OPEN v_list FOR
    SELECT city_id, city_name FROM City;

  LOOP 
    FETCH v_list
    INTO  v_city_id, v_city_name;
    EXIT WHEN v_list%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_city_id || ' - ' || v_city_name);
  END LOOP;
  CLOSE v_list;  
END;
/

Demo

Answer 2

This question is tagged with oracle 11g, but let me provide the new way to return result which is introduced in oracle 12c.

DBMS_SQL.RETURN_RESULT(l_cursor) can be used to print the result in oracle 12c.

DECLARE
 v_list SYS_REFCURSOR
BEGIN
 Open v_list for
 SELECT * from OT.City;

 DBMS_SQL.RETURN_RESULT(v_list);
END;
/

Cheers!!