UNIX for Dummies Questions & Answers

Hi,

I am working on a shell script that fires a plsql package. I would like the package to return a value to the shell script and then the shell script to return it to the javascript. Is it possible for the shell script to accept the return status from the plsql package and then pass it back to the calling java script? The call to the package from the shell script looks like:

echo "=== Calling the processing procedure..."
sqlplus hotman/hotman > /dev/null << EOF
begin
pho_glovia_pkg.move_data_prc('$r_status');
end;
/
EOF

When I run it like this it won't call the package, but if I go to the package and remove the return parameter and remove the "('$r_status')" from the shell script it runs just fine.

Thanks,

Amber Taylor

if you are just trying to grab the exit code is is returned to the shell script and can be accessed in the $? variable. If the value is something else but still an integer, have your package exit with the value you want to grab. exit myvalue this will cause the shell to think that myvalue is the return code and it can be accessed in $? not sure if this is what you wanted but hope it helps. I have found that working with a function is easiest that way all you need to do is

value = `sqlplus hotman/hotman << EOF
begin
pho_glovia_pkg.move_data_prc();
end;
/
EOF`

the value returned by the function will be in $value

Thanks for your advice. I was attempting to reuse code :-) that existed in a package. This package normally returns a status and I was trying to pick up that status from the package using a shell script and return it to the java. As a workaround I created a transaction/error table that the java can query after execution of the shell script to find the status of a run. The shell script inserts any errors during the file loads and the package will make the insert on exit for any errors found during the run. This way I can return multiple errors when needed.

Thanks,

Amber Taylor

Hi Amber,

You should be able to do somthing like...

#!/usr/bin/ksh

sqlplus -s scott/tiger@db << EOF > /tmp/result.$$
declare invar number;
declare outvar char(30);
exec myschema.mypkg.myproc(invar, outvar);
dbms_output.putline(outvar);
/
EOF

read result /tmp/result.$$
echo $result
rm -f /tmp/result.$$
exit 0

...if you do a search on the forum from "sqlplus" you should
see lots of good examples. The one above is just a quick and dirty
one.

Thanks!

Amber Taylor