This discussion is archived
1 Reply Latest reply: Dec 15, 2012 4:26 AM by Dude! RSS

How to get procedure outparameter value in unix variable

RajeshKanna Newbie
Currently Being Moderated
hi,
I have created a procedure with one out parameter value. how to get the out value in unix shell script.
procedure get_edname(v_filename out varchar2)

is

TYPE emp_rec IS RECORD(empname varchar2(100),dname varchar2(100));
l_emprec emp_rec;

TYPE emp_tab IS TABLE OF l_emprec%type;
l_emptab emp_tab;
l_str varchar2(200);
v_filehandler UTL_FILE.FILE_TYPE;

begin

select ename,dname
bulk collect into l_emptab
from emp e,dept d
where e.deptno=d.deptno
order by dname;

v_filename:='empdetails'||to_char(sysdate,'DDMONHH24MISS')||'.txt';
v_filehandler:=UTL_FILE.FOPEN('TEST_SER1',v_filename,'w');
UTL_FILE.PUT_LINE(v_filehandler,'***** Employeename and departmentname ******');
for i in l_emptab.first .. l_emptab.last
loop
l_str:=l_emptab(i).empname||','||l_emptab(i).dname;
UTL_FILE.PUTF(v_filehandler,'%s\n',l_str);
end loop;
UTL_FILE.PUT_LINE(v_filehandler,'***** End of Report ******');  
UTL_FILE.FCLOSE(v_filehandler);
end;
i wrote the shellscript as below
sqlplus -s scott/tiger << EOF
set serveroutput on;
declare
var res varchar2(100);
begin
GET_EDNAME(res);
dbms_output.put_line(res);
end;
EOF
can any one help me how to retrive out parameter value in unix
  • 1. Re: How to get procedure outparameter value in unix variable
    Dude! Guru
    Currently Being Moderated
    Perhaps the following examples can help you:

    <pre>
    ora_status=`sqlplus -s /nolog <<-EOF
    set pages 0 feed off
    connect / as sysdba
    select status from v\$instance;
    exit
    EOF
    `
    ora_status=`echo 'set pages 0 feed off
    connect / as sysdba
    select status from v$instance;
    exit' | sqlplus -s /nolog`
    </pre>

    Note the different quotes (' vs. `).

    Keep in mind that the shell variable, ora_status in the above examples, will contain the info exactly as is it appears from the SQL*Plus output.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points