Shell Programming and Scripting

       execute retval = $procedure('$var1', $vari, :var2);

test@ORA11G>
test@ORA11G> --
test@ORA11G> -- create an Oracle function with an OUT parameter
test@ORA11G> --
test@ORA11G> create or replace function f1 (x out number)
  2  return number
  3  is
  4  begin
  5    return 100;
  6  end;
  7  /
Function created.
test@ORA11G>
test@ORA11G> -- now test it
test@ORA11G> declare
  2    p number;
  3    q number;
  4  begin
  5    p := f1(q);
  6    dbms_output.put_line('q = '||q);
  7  end;
  8  /
q =
PL/SQL procedure successfully completed.
test@ORA11G>
test@ORA11G> -- so we *CANNOT* return a function's value via
test@ORA11G> -- the OUT parameter mode
test@ORA11G> -- let's try again
test@ORA11G> declare
  2    p number;
  3    q number;
  4  begin
  5    p := f1(q);
  6    dbms_output.put_line('p = '||p);
  7  end;
  8  /
p = 100
PL/SQL procedure successfully completed.
test@ORA11G>
test@ORA11G> -- as you can see, the return value is always
test@ORA11G> -- from an Oracle function, via the "RETURN" statement
test@ORA11G> -- And a function can *ONLY* return single value
test@ORA11G>
test@ORA11G> -- In fact, the "OUT" parameter mode is useless !
test@ORA11G> -- Also, "IN" need not be mentioned, because all parameters are "IN" parameters
test@ORA11G> create or replace function f1 (x number)
  2  return number
  3  is
  4  begin
  5    -- do your stuff here
  6    return 2*x;
  7  end;
  8  /
Function created.
test@ORA11G>
test@ORA11G> -- and test it now
test@ORA11G> declare
  2    q number := 10;
  3    p number;
  4  begin
  5    p := f1(q);
  6    dbms_output.put_line('p = '||p);
  7  end;
  8  /
p = 20
PL/SQL procedure successfully completed.
test@ORA11G>
test@ORA11G>
test@ORA11G>

$
$
$ cat -n process.sh
     1  #!/bin/bash -e
     2  username=$1
     3  baza=$2
     4  procedure=$3
     5  var1=$4
     6  prosao=0
     7  passwd=''
     8  vari=22
     9  retval=0
    10  #echo $vari
    11  while read line
    12  do
    13          if [[ ! "$line" =~ ^# ]]; then
    14                  tns="$(echo $line | cut -d: -f1)"
    15                  login="$(echo $line | cut -d: -f2)"
    16                  pwd="$(echo $line | cut -d: -f3)"
    17                  if [ $login = $username ]
    18                  then
    19                          if [ $tns = $baza ]
    20                          then
    21                                  passwd=$pwd
    22                                  prosao=1
    23                          fi
    24                  fi
    25          fi
    26  done < "connections"
    27  if [ $prosao = 1 ]
    28  then
    29          vvarr=$(sqlplus -s -l $username/$passwd@$baza<<END
    30          set heading off feedback off pages 0
    31          var var2 Number;
    32          var retval Number;
    33          execute :retval := $procedure('$var1', $vari, :var2);
    34          print :retval;
    35          commit;
    36          exit;
    37          END)
    38          echo "vvarr => $vvarr"
    39  else
    40          echo -2
    41  fi
$
$ ./process.sh test ora11g t2 xyz
vvarr =>         0
$
$