CODEWITHSUNDEEP
oracle-databaseoracle11g
redsoxlost
redsoxlost

Reputation: 1235

Print Oracle PLSQL OUT variable

create or replace package demo_pckg 
as
 type cursor_type is ref cursor;
 procedure demo_proc(i_deptno number,o_result out cursor_type);
end;
/


create or  replace package body demo_pckg
as
procedure demo_proc(i_deptno number,o_result out cursor_type)
as
 begin
    open o_result for
    select * from employees 
    where department_id=i_deptno;
    end;
end;
/

What should I do in case I want to print that OUT cursor variable ?

Upvotes: 0

Views: 197

Answers (2)

Alex Poole
Alex Poole

Reputation: 191580

The simplest way from SQL*Plus or SQL Developer is with variable and print:

var deptno number
var result refcursor
exec :deptno := 10;
exec demo_pckg.demo_proc(:deptno, :result);
print result

Or:

var result refcursor
declare
  deptno emplyees.department_id%type;;
begin
  deptno := 10;
  demo_pckg.demo_proc(deptno, :result);
end;
/
print result

The result is treated as a bind variable in the procedure call, so it's prefixed with : there, but it's a native variable to SQL*Plus so it doesn't have one for the print call. You can run either in SQL*Plus, or as a script in SQL Developer, which will show the results in the Script Output window.

You can hard-code the deptno value in the procedure call in either case, of course, rather than delcaring a variable to hold that.

If you're calling this from Java or some other client program you can treat your OUT cursor like any other result set.

You can also declare result as a sys_refcursor in your procedure, rather than declaring your own type.

Upvotes: 2

yǝsʞǝla
yǝsʞǝla

Reputation: 16422

dbms_output.put_line prints to standard output. You need to enable server output to see the results with: set serveroutput on. Turning server output on should be done in your environment like sqlplus, etc. You will need to select results into a variable with select column1 into var1 from ... so that you can print them later with dbms_output.put_line('my var1: ' || var1);

Upvotes: 1

Related Questions