How to access outparameter of a Procedure called inside a shell script?

Shell Programming and Scripting
1

Hi

I am calling a PLSQL procedure inside a shell scipt.
How to access out paramters of this procedure inside the mail shell script.

I am using below peiece of code.

echo " inside PKG validation"
       `sqlplus -s login/password@database <<EOF
          set heading off
          set feedback off
          set pagesize 0
          set linesize 32767



           VARIABLE l_out_put      VARCHAR2(1000)
           VARIABLE l_change_flag  VARCHAR2(2)
             exec xxat_sox_proc('$file_name',:l_out_put,:l_change_flag);
          print l_out_put
          print l_change_flag
       exit
       EOF`
        if [ $l_change_flag = "Y" ]
        then
echo " after if"
        echo "$l_out_put" >> SOX_`date +'%Y%m%d'`
        fi

Here my IF condition is failing as unix is unable to recognise it.

Appreciate your quick response

2

Your SQL session will presumeable display the correct results to you, so what you need to do with the shell script is to read them in.

Are you expecting a single line with two values from your SQL or two lines?

If it is the latter, then after you EOF, try inserting the following:-

read l_out_put
read l_change_flag

This may hang if your shell decides it really wants to display the answers and then read input from the keyboard. Perhaps you need to change then end of your SQL block to read:-

   print l_out_put
          print l_change_flag
       exit
       EOF`|while read l_out_put
                do
                    read l_change_flag
                done
        if [ $l_change_flag .........

Does that steer you towards a solution?

If not, can you run the SQL bit and paste the output from that and I'm sure someone will get it sorted.

I hope that this helps,
Robin
Liverpool/Blackburn
UK

3

Here's an example I did once to illustrate reading output from SQL/Plus and putting the data returned into shell variables. Maybe the general framework here will help you. This is ksh93 on Solaris:

#!/usr/dt/bin/dtksh
unset version
unset status

( sqlplus -s login/password@database <<EOF
  set heading off;
  select 'version='||'1.2' from dual;
  select 'status='||'UP' from dual;
  exit;
EOF
)|
while read line
do
 if [[ -n $line ]]
   then eval $line
 fi
done

print "Version: $version"
print "Status: $status"

P.S. You best change your apps password ASAP!!!

1 Like
4 
       EOF`

This line must not be indented or it will not work and all the remaining lines in the script will be presented to sqlplus.
Try this:

EOF`
5

If indenting makes the code more readable, I have found that using a TAB characters to indent is acceptable to ksh, however spaces are a definite no-no.

Robin
Liverpool/Blackburn
UK

6

If you have <<-EOF instead of <<EOF the terminator EOF of the Here Document can be indented with tabs.

7

Tabs or spaces makes no difference with <<, both will cause the terminator to be ignored.

If you use <<-, you can use tabs (but not spaces).

EDIT: As methyl said. (curses lack of delete button :p)

EDIT2: And apparently <<# allows both (on ksh93, at least).