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?

On a side note: It's far easier to work with implicit cursors, e.g.: `for rec in (select * from ot.city) loop dbms_output.put_line(rec.city_id); end loop;`. — Thorsten Kettner, Sep 29 '19 at 15:58

Barbaros Özhan · Accepted Answer · 2019-09-29T16:02:09.557

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

score 7 · Answer 2 · answered Sep 29 '19 at 16:38

7

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!!

answered Sep 29 '19 at 16:38

Popeye

35,427
4
10
31

but what for 11g? – Random guy Sep 29 '19 at 16:57
@Tejash, is `DBMS_SQL.RETURN_RESULT` is the only alternative for DBMS_OUTPUT.PUT_LINE inside loop to print the cursor result? or this can be used anywhere else also? – Ankit Bajpai Sep 30 '19 at 07:27
As far as I know, This is the only alternative. others are to write in the file. – Popeye Sep 30 '19 at 09:09

score 2 · Answer 3 · answered Sep 29 '19 at 15:50

There are many issues with your code, for example:

v_list should be declared as city_list%ROWTYPE
you need to OPEN the cursor and then LOOP
to assign the current record to city_list, you want to use FETCH INTO

Consider this new version of your code:

DECLARE
    CURSOR city_list IS SELECT * from city ORDER BY city_id;
    v_list city_list%ROWTYPE;
BEGIN
    OPEN city_list;
    LOOP
        FETCH city_list INTO v_list;
        EXIT when city_list%notfound;
        DBMS_OUTPUT.PUT_LINE(v_list.city_id);
    END LOOP;
    CLOSE city_list;

END;
/

In this demo on DB Fiddle, given the following content for the city table:

CITY_ID | CITY_NAME  
------: | :----------
      1 | New York   
      2 | Los Angeles

The code outputs:

1 rows affected

dbms_output:
1
2

0xdb · Answer 4 · 2021-03-11T09:28:51.243

2

In 11g the simplest way to print values from the cursor is to pass the cursor variable:

var rc refcursor
begin
    open :rc for
    select City.* from (
        select rownum id, trim (column_value) name 
        from xmlTable ('"New York","Paris"')) City;
end;
/
print rc

        ID NAME            
---------- ----------------
         1 New York        
         2 Paris

edited Mar 11 '21 at 09:28

answered Sep 12 '20 at 14:07

0xdb

3,539
1
21
37

``set autoprint on`` or use ``print rc;`` after the PL/SQL block. – Alex Bartsmon Feb 28 '21 at 00:50

How to print the cursor values in oracle?

4 Answers4