Does it work if you put stored_procedure.sql in the directory you run the script from? The syntax doesn't look quite right but it's a long time since I've used Oracle. I think we used to set username and password, etc. in an environment variable before running SQL*Plus. You could also try redirecting stdio (i.e. using < /dir/stored_procedure.sql) instead of the @ syntax).
My problem is the shell script is connecting to the database but not executing the script.
Pardon me for asking, but how do you know that the SQL script is not being executed?
I would appreciate any suggesting to overcome this
I'd really like to help you out, but there are many possible reasons why the SQL script is not being executed (as you claim). Unfortunately, I couldn't find any details (in your post) of any error messages you may be getting (or if you even are getting error messages), and since I can't know anything about your situation -- unless you tell me -- there is no way I can help you, based on the [lack of] information you have provided.
What I can do is give you some suggestions as to how you may discover the cause of your problem -- since, usually, after discovering the cause, the problem is very easy to fix.
Most shells have a "-x" option that prints out debug information. Unfortunately, I don't know what shell you are using (Bourne, Korn, C, etc) so all I can do is refer you to the "man" page for the shell you are using. The "debug" output may help you discover the cause of your problem.
Also, have you tried to execute the SQL script directly from "SQL*Plus"? In other words, open an "SQL*Plus" session and type:
Does it work when you do that?
That's about all I can offer (for now).
Good Luck, Avi.
[ August 19, 2004: Message edited by: Avi Abrami ] [ August 19, 2004: Message edited by: Avi Abrami ]
Joined: Aug 02, 2004
Avi's post made me think: you're not expecting the script to execute the SP, are you? It will only create it, which may produce little or no output. I say this as, in the past, I've come across the same issue with some of my less experienced developers.
Joined: Nov 07, 2000
Thank you guys. It is working now. I tested the script directly executing in the sqlplus. Then tried it from the script, if i create another script like a.sql that has stored procedure and call it from the second script it executed. One more thing i forgot is to put '/' at the end of the script.
Once again many thanks for your suggestions.
subject: create store procedure using shell script.