how to call oracle stored procedure from unix shell

Shell Programming and Scripting
1

Hi

i want to call a oracle stored procedure from unix (using bash shell).

consider this is my oracle stored procedure with parameter

create procedure testproc(name IN varchar, age IN Number, id OUT Number )

AS

begin
id=1;
dbms_output.put.line('successfull validation')

end;

i want to call the stored procedure and want to store the return value from stored procedure into a variable in unix(bash shell).

also i want to display the dbms_output.putline content into unix(bash shell)

Thanks

1 Like
2

Is this related to your previous post? :

http://www.unix.com/shell-programming-scripting/130813-how-pass-values-unix-shell-oracle-stored-procedure.html

3

Hi

its differrent from my previous post.

Thanks.

---------- Post updated at 10:44 PM ---------- Previous update was at 09:25 PM ----------

Hi

can provide the script.

thanks.

4

Try this,

#!/bin/sh
sqlplus -s username/password<<END
execute procedure_name( parameters,....)
Commit;
exit;
END


eg:

sqlplus -s scott@dbase/tiger<<END
execute test_proc(nila,20,101);
commit;
exit;
END

after this save and exit from editor
and run in from the uix prompt... sh sciptname

it will work..

5

Hi nila

exec procedure();
is not working.
but i tried call procedure(); it was working.

but my concern is

the stored procedure has a OUT parameter and i want to use the out parameter value in unix script.

Thanks

6

Here is one idea:

  • inside your pl/sql code, use UTL_FILE package to write the value of the out parameter into a unix file
  • from your shell script, read the value from the file

If you want to avoid writing the value to a file, you can write the output to stdout and use the back-quote operator in the shell script to assign the stdout value to some shell variable.

Thanks.

7

UTL_FILE won't work if his database is running on a different machine than his shell script.

Try this.

$ cat use_oracle_proc.ksh
#!/bin/ksh

run_sql()
{
  $ORACLE_HOME/bin/sqlplus -S $scott@dbase/tiger <<EOF
  SET PAGESIZE 0;
  SET FEEDBACK OFF;
  SET SERVEROUT ON;
  VAR major NUMBER;
  VAR minor NUMBER;
  EXEC DBMS_PROFILER.GET_VERSION (:major, :minor);
  PRINT major;
  PRINT minor;
EXIT;
EOF
}

set -A ARRAY $(run_sql)
ARRAYCOUNT=${#ARRAY[*]}
ARRAYIDX=0

while (( $ARRAYIDX < $ARRAYCOUNT ))
do
 echo  "ARRAY[$ARRAYIDX]=(${ARRAY[ARRAYIDX]})"
 ARRAYIDX=$(($ARRAYIDX+1))
done

exit 0

$ ./use_oracle_proc.ksh
ARRAY[0]=(2)
ARRAY[1]=(0)
1 Like