sqloracleplsqlsys-refcursor

How to dbms_output record variable


set serveroutput on;
DECLARE
  PI_STARTDATE DATE;
  PO_STATUS NUMBER;
  PO_HEADER VARCHAR2(200);
  PO_LABEL VARCHAR2(200);
  PO_RECORD SYS_REFCURSOR;
  PO_NEXTINCSTARTDATE DATE;
BEGIN
  PI_STARTDATE := to_date('2020-05-01','yyyy-MM-dd');
  PCK_FAB_REPORTS.PRC_MONTHLY_WRTOFF_REPORT(
    PI_STARTDATE => PI_STARTDATE,
    PO_STATUS => PO_STATUS,
    PO_HEADER => PO_HEADER,
    PO_LABEL => PO_LABEL,
    PO_RECORD => PO_RECORD,
    PO_NEXTINCSTARTDATE => PO_NEXTINCSTARTDATE);
DBMS_OUTPUT.PUT_LINE('PO_STATUS = ' || PO_STATUS);
DBMS_OUTPUT.PUT_LINE('PO_HEADER = ' || PO_HEADER);
DBMS_OUTPUT.PUT_LINE('PO_LABEL = ' || PO_LABEL);
DBMS_OUTPUT.PUT_LINE('PO_NEXTINCSTARTDATE = ' || PO_NEXTINCSTARTDATE);
END;

I want to unit test the procedure and want to display the ref cursor variable as well. How to display ref cursor using dbms?


Solution

  • Here's an example based on Scott's EMP table:

    SQL> set serveroutput on
    SQL>
    SQL> declare
      2    l_ename   emp.ename%type;
      3    l_job     emp.job%type;
      4    l_rc      sys_refcursor;
      5  begin
      6    open l_rc for select ename, job from emp
      7                  where deptno = 10;
      8
      9    loop
     10      fetch l_rc into l_ename, l_job;
     11      exit when l_rc%notfound;
     12
     13      dbms_output.put_line(l_ename ||' '|| l_job);
     14    end loop;
     15    close l_rc;
     16  end;
     17  /
    CLARK MANAGER
    KING PRESIDENT
    MILLER CLERK
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    So: you have to fetch refcursor into variables and then display contents of those variables.