Re: Shell script to catch PL/SQL return values

> I need some help from the experts on PL/SQL and Shell scripting. I need
> a shell script that runs a PL/SQL procedure and gets the values
> returned from the PL/SQL procedure into the shell variables. The PL/SQL
> procedure returns multiple values.
>
> I was able to assign a single return value from the SQL statement to
> the shell variable as below, but what about multiple values...

$ fsql() {
> sqlplus -s << !
> / as sysdba
> set serverout on feed off head off echo off
> begin
> for x in (select username from dba_users)
> loop
> dbms_output.put_line(x.username);
> end loop;
> end;
> /
> exit
> !
> }

$ fsql | while read name; do echo "Username = $name";done

Username = MGMT_VIEW
Username = SYS
Username = SYSTEM
Username = DBSNMP
Username = SYSMAN
Username = TEST
Username = OUTLN
Username = EXFSYS
Username = DMSYS
Username = WMSYS
Username = ORDSYS
Username = XDB
Username = OLAPSYS
Username = SI_INFORMTN_SCHEMA
Username = ORDPLUGINS
Username = MDSYS
Username = CTXSYS
Username = ANONYMOUS
Username = MDDATA
Username = OE
Username = TSMSYS
Username = IX
Username = DIP
Username = PM
Username = HR
Username = SH
Username = BI
Username = SCOTT

HTH
Dimitre Received on Tue Jun 27 2006 - 06:08:53 CDT