Output cursor data with dbms_output.put_line

Question

--pkg
create or replace package cur_pkg as 
type t_cur is ref cursor;
procedure open_cur_spr_ppl (spr_id in number,
  spr_name in varchar2, 
  spr_family in varchar2);
end cur_pkg;

--pkg_body
create or replace package body cur_pkg as
  procedure open_cur_spr_ppl (spr_id in number,
  spr_name in varchar2, 
  spr_family in varchar2)
  is 
    v_curs t_cur;
  begin
    open v_curs for 
    select spravochnik_id, spravochnik_name, spravochnik_family
    from spravochnik_people
    where spravochnik_id >= 1770;
    loop
      FETCH v_curs INTO spr_id, spr_name, spr_family;
      EXIT WHEN v_curs%notfound;
      dbms_output.put_line('ID:' || spr_id || 'Name:' || spr_name || 'Family:' || spr_family);
    end loop;
    close v_curs;
  end open_cur_spr_ppl;  
end cur_pkg;

I want to output the data for the cursor. Please advise how to implement it correctly. The problem is these lines. I don't know how to do it correctly.

FETCH v_curs INTO spr_id, spr_name, spr_family;

EXIT WHEN v_curs%notfound;
dbms_output.put_line('ID:' || spr_id || 'Name:' || spr_name || 'Family:' || spr_family);

The error you are getting isn't from the `dbms_output` call, which is valid, it's from the fetch. — William Robertson, Jul 30 '22 at 08:22

score 2 · Answer 1 · answered Jul 29 '22 at 08:32

One of the errors in your procedure was trying to use:

FETCH v_curs INTO spr_id, spr_name, spr_family;

Where spr_id, spr_name, spr_family are all declared as IN parameters and cannot be written to. You would want to declare them as local variables instead.

When you want to retrieve a cursor, normally you do not open a cursor and print it to the database console immediately as that makes the procedure only useful for that one thing and most times you are going to want to return the cursor without reading it.

Instead, you would open the cursor and then return it as an OUT parameter to the calling scope so that they can read it and, if necessary, format and output it separately. If you did want to print it to the console then you can split that off into a separate procedure:

CREATE PACKAGE cur_pkg AS
  TYPE t_cur is ref cursor;

  PROCEDURE open_cur_spr_ppl (
    p_cur OUT SYS_REFCURSOR
  );

  PROCEDURE print_cur_spr_ppl (
    p_cur IN SYS_REFCURSOR
  );
end cur_pkg;

CREATE PACKAGE BODY cur_pkg AS
  PROCEDURE open_cur_spr_ppl (
    p_cur OUT SYS_REFCURSOR
  )
  is 
  begin
    open p_cur for 
      select spravochnik_id,
             spravochnik_name,
             spravochnik_family
      from   spravochnik_people
      where  spravochnik_id >= 1770;
  end open_cur_spr_ppl;  

  PROCEDURE print_cur_spr_ppl (
    p_cur IN SYS_REFCURSOR
  )
  is
    v_id     spravochnik_people.spravochnik_id%TYPE;
    v_name   spravochnik_people.spravochnik_name%TYPE;
    v_family spravochnik_people.spravochnik_family%TYPE;
    c_id_width     CONSTANT PLS_INTEGER := 10;
    c_name_width   CONSTANT PLS_INTEGER := 30;
    c_family_width CONSTANT PLS_INTEGER := 30;
  begin
    -- Header
    DBMS_OUTPUT.PUT_LINE(
      LPAD('ID', c_id_width, ' ')
      || ' | ' || RPAD('NAME', c_name_width, ' ')
      || ' | ' || RPAD('FAMILY', c_family_width, ' ')
    );
    DBMS_OUTPUT.PUT_LINE(
      LPAD(':', c_id_width, '-')
      || ' | ' || RPAD(':', c_name_width, '-')
      || ' | ' || RPAD(':', c_family_width, '-')
    );

    LOOP
      FETCH p_cur INTO v_id, v_name, v_family;
      EXIT WHEN p_cur%notfound;
      DBMS_OUTPUT.PUT_LINE(
        LPAD(v_id, c_id_width, ' ')
        || ' | ' || RPAD(v_name, c_name_width, ' ')
        || ' | ' || RPAD(v_family, c_family_width, ' ')
      );
    END LOOP;
    CLOSE p_cur;
  end print_cur_spr_ppl;  
end cur_pkg;

Then if you want to call it you can use:

DECLARE
  v_cur SYS_REFCURSOR;
BEGIN
  cur_pkg.open_cur_spr_ppl(v_cur);
  cur_pkg.print_cur_spr_ppl(v_cur);
END;
/

Which, for the sample data:

CREATE TABLE spravochnik_people (spravochnik_id, spravochnik_name, spravochnik_family) AS
SELECT 1800, 'Alice', 'Abbot' FROM DUAL UNION ALL
SELECT 1900, 'Betty', 'Baron' FROM DUAL;

Outputs:

        ID | NAME                           | FAMILY                        
---------: | :----------------------------- | :-----------------------------
      1800 | Alice                          | Abbot                         
      1900 | Betty                          | Baron

db<>fiddle here

score 0 · Answer 2 · answered Aug 01 '22 at 01:24

If someone need

 --pkg
    create or replace package cur_pkg as 
    type t_cur is ref cursor;
    procedure open_cur_spr_ppl (spr_id in out spravochnik_people.spravochnik_id%TYPE,
      spr_name in out spravochnik_people.spravochnik_name%TYPE, 
      spr_family in out spravochnik_people.spravochnik_family%TYPE);
    end cur_pkg;
    --pkg_body
    create or replace package body cur_pkg as
      procedure open_cur_spr_ppl (spr_id in out spravochnik_people.spravochnik_id%TYPE,
      spr_name in out spravochnik_people.spravochnik_name%TYPE, 
      spr_family in out spravochnik_people.spravochnik_family%TYPE)
      is 
        v_curs t_cur;
      begin
        open v_curs for 
        select spravochnik_id, spravochnik_name, spravochnik_family
        from spravochnik_people;
        loop
          FETCH v_curs INTO spr_id, spr_name, spr_family;
            EXIT WHEN v_curs%notfound;
        end loop;
        close v_curs;
      end open_cur_spr_ppl;  
    end cur_pkg;

It's nice coming back to share what finally worked for you. But I don't see how you output the content of the cursor as you where initially asking. In your code, the output is only the last row of the retrieved cursor (the values of spr_id, spr_name and spr_family for that last row) so there's not output for the rest of the cursor. — Ana GH, Aug 02 '22 at 09:23

Output cursor data with dbms_output.put_line

2 Answers2