Skip to Main Content
  • Questions
  • How to print ref cursor and nested table as out parameter in procedure using anonymous

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pawan.

Asked: April 07, 2020 - 3:07 am UTC

Answered by: Chris Saxon - Last updated: April 08, 2020 - 3:38 pm UTC

Category: PL/SQL - Version: 11g

Viewed 100+ times

You Asked

Hi Sir,
I want to print all out parameters using sql developer and anonymous block. Please refer below scripts.

CREATE TABLE EMP
(
empno number(4,0) not null,
ename varchar2(10 byte),
job varchar2(9 byte),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0)
);
/


SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-12-80','DD-MM-RR'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-02-81','DD-MM-RR'),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-02-81','DD-MM-RR'),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-04-81','DD-MM-RR'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-09-81','DD-MM-RR'),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-05-81','DD-MM-RR'),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-06-81','DD-MM-RR'),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('09-12-82','DD-MM-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-11-81','DD-MM-RR'),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-09-81','DD-MM-RR'),1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('12-01-83','DD-MM-RR'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-12-81','DD-MM-RR'),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-12-81','DD-MM-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-01-82','DD-MM-RR'),1300,null,10);

-------------------------------------------------------------------------------------------------------------
create or replace type varchar_table is table of varchar2(10 byte);

--------------------------------------------------------------------------
create or replace package pk_test
as
procedure p1_test(i_emp_dptid in number,
o_cur_emp_dtls out sys_refcursor,
o_emp_f_name out varchar_table,
o_emp_l_job out varchar_table
);

end pk_test;
/

create or replace PACKAGE BODY PK_TEST AS

procedure p1_test
(
  i_emp_dptid in number,
  o_cur_emp_dtls out sys_refcursor,
  o_emp_f_name out varchar_table,
  o_emp_l_job out varchar_table
)

AS
  BEGIN
   open o_cur_emp_dtls for
   select *
   from emp where deptno = i_emp_dptid;
   
  SELECT
    ( SELECT (CAST(collect(ename) AS varchar_table)) FROM emp WHERE deptno = 20 ) AS emp_name,
    ( SELECT (CAST(collect(job) AS varchar_table)) FROM emp WHERE deptno = 20 ) AS job_name
    INTO
    o_emp_f_name,
    o_emp_l_job  
  FROM dual;
  END p1_test;

END PK_TEST;
/

and we said...

If you want to view the output of the cursor in SQL Dev/Plus, create a variable and print it:

var cur refcursor;

declare
  cur sys_refcursor;
  name varchar_table;
  job varchar_table;
begin

  pk_test.p1_test ( 
    10,
    :cur,
    name, job
  );

end;
/

print :cur

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450 <null>             10
      7839 KING       PRESIDENT <null>     17-NOV-1981 00:00:00       5000 <null>             10
      7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300 <null>             10

and you rated our response

  (2 ratings)

Reviews

Print output of nested table variable

April 08, 2020 - 3:47 am UTC

Reviewer: pawan from india

Hi Sir,

Thank you for your reply. My concern is I want to print nested table variable also along with ref cursor if you check there are 3 parameters.
Chris Saxon

Followup  

April 08, 2020 - 8:09 am UTC

Loop through the array and output the elements:

var cur refcursor;

declare
  cur sys_refcursor;
  name varchar_table;
  job varchar_table;
  
  procedure print_vc_tab ( tab varchar_table ) as
  begin
    for inx in 1 .. tab.count loop
      dbms_output.put_line ( tab ( inx ) );
    end loop;
  end print_vc_tab;
  
begin

  pk_test.p1_test ( 
    10,
    :cur,
    name, job
  );
  
  dbms_output.put_line ( '** Names **' );
  print_vc_tab ( name );
  dbms_output.put_line ( '** Jobs **' );
  print_vc_tab ( job );

end;
/

** Names **
SMITH
JONES
SCOTT
ADAMS
FORD
** Jobs **
CLERK
MANAGER
ANALYST
CLERK
ANALYST

print :cur

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450 <null>             10
      7839 KING       PRESIDENT <null>     17-NOV-1981 00:00:00       5000 <null>             10
      7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300 <null>             10

April 08, 2020 - 10:24 am UTC

Reviewer: pawan from india

Thank you sir
Chris Saxon

Followup  

April 08, 2020 - 3:38 pm UTC

You're welcome.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.