how do i call a pl/sql function from unix script 1

[Premalm]

Hi Guys,

I have stored Pl/sql function which returns a count. I want to call this function from a unix shell script. How can I call it ? any Ideas ?

Thank you.
Premal

 

[Scunningham99]

$ORACLE_HOME/bin/sqlplus system/password << EOF
exec package01.name_of_body01( 'param1','param2');
exit
EOF

exit 0

above is an example u can use in shell

Sy UK

 

[Premalm]

In the above case you are executing a oracle stored procedure. how do I execute a oracle stored function and
take the return value in an Unix variable ?

Thanks
Premal

 

[SirCharles]

try this:
cat >/tmp/tmpCommand.sql <<EOF
set head off
set pagesize 9000
spool /tmp/tmpOut.txt
select unique_hr_name from inet_user where rownum < 2;
spool off
exit
EOF

sqlplus 'username/password' '@'/tmp/tmpCommand.sql
unixVar=`grep -v 'rows selected' /tmp/tmpOut.txt`

 

[SirCharles]

Ooops:
replace: select unique_hr_name from inet_user where rownum < 2;

with: select yourFunction() from tablename;

 

[sem]

The solution provided by SirCharles works only in case your function has an appropriate purity level (WNDS). I may suggest slightly different approach.

export MYVAR=`sqlplus -s user/pass@db <<EOF
set head off feedback off serverout off
var v varchar2(255)
exec :v := your_function
print v
exit
EOF`

Regards, Dima

http://seminihin.narod.ru

 

[Premalm]

I tried running the above example but it doesn't output me anything.

export MYVAR=`sqlplus -s user/pass@db <<EOF
set head off feedback off serverout off
var v varchar2(255)
exec :v := pkg_job_sche.Countjobs
print v
exit
EOF`

Any ideas ?

Thanks
Premal

 

[sem]

First of all what do you expect to be outputed? You asked to assign a result to a variable - this script assigns pkg_job_sche.Countjobs value to $MYVAR. Of course if your connect string user/pass@db is correct at least. Besides export MYVAR= doesn't work in ALL shells (it works in BASH but not in pure Bourn)

Regards, Dima

http://seminihin.narod.ru

 

[Premalm]

The result I expect to be output is the value of v.
I need it so that I can loop in my shell script.
I am using korn shell.

Thanks
Premal.

 

[sem]

The v variable is declared within sql script, thus can not be accessed from shell directly. That's why it's "printed" and its value is passed to MYVAR shell variable through stdout.
This script is quick-and-dirty, it doesn't handle any non-standard situations, but IMO in your case most probable problem is with connect string.
Try to launch sql*plus, connect to Oracle and call one-by-one:

set head off feedback off serverout off
var v varchar2(255)
exec :v := pkg_job_sche.Countjobs
print v

At the end you should see the result of pkg_job_sche.Countjobs

Regards, Dima

http://seminihin.narod.ru

 

[SirCharles]

Dima,
I tried your suggestion. Works great. Please explain about purity level.

 

[sem]

A function shouldn't update database at least to be used within sql. For packaged functions their implementations are "hidden" from sql engine, thus in versions prior to 9i (if I remember) PRAGMA had to be used in package specification.

There are 4 levels:

WNPS (Write No Package State) -does not change package variables
WNDS(Write No Database State) -does not change database tables
RNDS(Read No Database State) -does not read database data
RNPS (Read No Package State) -does not read package variables

Regards, Dima

http://seminihin.narod.ru