calling stored procedure from shell script.

Shell Programming and Scripting
1

Hi All,
This is a very starnge problem I am having. I have a shell script that calls a stored procedure.

Here's my code in shell script:

sqlplus "userid/pwd" @file.sql

and file.sql has the following statement:

exec my_storedProc;

Now, when I execute my shell script, nothing happens, the stored procedure is NOT executed.

But, if I run the same stored procedure not as a script but, do a sqlplus "userid/password" on UNIX, it brings up the sqlplus prompt. Now if I manually type exec myStoredProc; , it runs just fine.

I do not know why it is happening. Also, I am able to run other stored procedures from shell scripts just fine. This is the only stored proceudre that is giving me problem.

FYI, my stored procedures access external tables. I checked permissions on the external tables and they have full access.

Any suggestions will be appreciated.

2

try the following ...

sqlplus "userid/pwd" <<!
exec my_storedProc;
!
3

How do you know that the stored procedure didn't execute? Are you expecting output? If you are, did you set server output on? If you are not expecting output but rather a transaction to have taken place, is this how you know? If not, you can put a simple insert statement in your SQL script or the stored procedure to see if the transaction took place.

sqlplus "userid/pwd" <<!
set serveroutput on size 1000000
exec my_storedProc;
!

Shouldn't be a problem.

You can also select something from dual to see if you are able to return any rows from you stored procedure.

Thomas