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

Last updated: April 08, 2020 - 3:38 pm UTC

Version: 11g

Viewed 10K+ times! This question is

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

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Print output of nested table variable

pawan, April 08, 2020 - 3:47 am UTC

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

pawan, April 08, 2020 - 10:24 am UTC

Thank you sir
Chris Saxon
April 08, 2020 - 3:38 pm UTC

You're welcome.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library