So, in Oracle (11gR2) on aix I have a function like:
CREATE OR REPLACE function COMMON.t2(var1 in varchar2, vari in number,var2 in out number) return number as
begin
insert into korisnik_temp(kor_inicia, kor_opisno) values(1, var1);
var2:=0;
return var2;
exception
when others then
var2:=-1;
return var2;
end;
and a shell script calling it:
#!/bin/bash -e
username=$1
baza=$2
procedure=$3
var1=$4
prosao=0
passwd=''
vari=22
retval=0
echo $vari
while read line
do
if [[ ! "$line" =~ ^# ]]; then
tns="$(echo $line | cut -d: -f1)"
login="$(echo $line | cut -d: -f2)"
pwd="$(echo $line | cut -d: -f3)"
if [ $login = $username ]
then
if [ $tns = $baza ]
then
passwd=$pwd
prosao=1
fi
fi
fi
done < "connections"
if [ $prosao = 1 ]
then
vvarr=$(sqlplus -s -l $username/$passwd@$baza<<END
set heading off;
set feedback off;
var var2 Number;
execute retval = $procedure('$var1', $vari, :var2);
print var2;
commit;
exit;
END)
echo $vvarr
else
echo -2
fi
but it makes an error because it's an function.
How could I get a returning value from an oracle function?
---------- Post updated at 02:57 PM ---------- Previous update was at 12:12 PM ----------
ok, please, change the name of thread into 'getting return value from oracle function'
When creating a "here document" in unix shell you state the string which will end the "here document".
In your case this is the three characters "END".
To be recognised, the string "END" must appear on its own line with no indentation.
Personally I don't use the $( commands ) syntax for anything complicated.
Your line:
END)
Should not have "END" indented and after looking at this a bit more closely
we should move the right parenthesis to the next line too:
END
)
Before you code anything in Oracle further, I'd suggest you to go through the "Oracle Concepts Guide", especially the section on Oracle subprograms - functions, procedures and packages.
An Oracle function *ALWAYS* returns a value via the RETURN statement. The datatype of the return value is strongly typed via the RETURN clause of its signature.
See below -
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>
See my earlier post, you should change your function's signature.
Shown below is how you should capture the function's return value, but this is still lousy code - pass only those parameters that you need to.
$
$
$ 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
$
$
Yes, but I do hope you understand it still is not a good way of doing things.
You should remove that OUT parameter from the function, because it is useless.
Remove the 2nd parameter since it is never used inside your function.
And once you do your DML stuff, you can return the hard-coded value 0 on success.
Also capturing "when others then" will only suppress the line number that threw the exception. A better way is to capture only named exceptions, or the ones that you expect. If you are unsure of the exceptions that will be thrown, then leave out "when others then" and let Oracle do the escalation part for you. The resultant diagnostic information will be much more useful than what you have now.
[/quote]
You cannot put that in a begin...end block.
Invocation of procedure is in the sqlplus environment itself.
And the "ORA-" message is returned to the sqlplus environment.
The begin...end block does not even come into play in this scenario.
