How to dbms_output record variable

Question

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?

Answer 1

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.