1 Reply Latest reply: Dec 15, 2012 6:26 AM by Catch_22 RSS

    How to get procedure outparameter value in unix variable

    RajeshKanna
      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
          Catch_22
          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.