oraclestored-proceduresplsqloracle11gsys-refcursor

How to print the cursor values in oracle?


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?


Solution

  • 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