File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes create store procedure using shell script. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "create store procedure using shell script." Watch "create store procedure using shell script." New topic
Author

create store procedure using shell script.

satya kiran
Ranch Hand

Joined: Nov 07, 2000
Posts: 52
I am planning to create a stored procedure using a shell script. but it is not creating it.

Here is the sample of my shell script.

a.sh
*************

$ORACLE_HOME/bin/sqlplus \
abcdusr/abcdpwd@production
@/dir/storedprocedure.sql

The storedprocedure.sql will be

CREATE OR REPLACE PROCEDURE ABCD.Auto_Ack_Batch
AS
CURSOR autocur IS ...........


My problem is the shell script is connecting to the database but not executing the script.

I would appreciate any suggesting to overcome this.

Thanks in advance.

Regards,
Satya
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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).

Hope that helps.

Jules
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Satya,

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:

@/dir/storedprocedure.sql

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 ]
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Satya,

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.

Jules
satya kiran
Ranch Hand

Joined: Nov 07, 2000
Posts: 52
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.

Regard,
satya
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: create store procedure using shell script.
 
Similar Threads
Time complexity for jdbc connection
Passing sysdate as parameter to Oracle procedure
call shell script and create a file
porting a .bat file to unix
Working with Java in UNIX