Execute procedure from Windows batch file
I tried to call a procedure (my_procedure) from windows batch file. It is doable. The code in batch file is like this:
sqlldr firstname.lastname@example.org control=C:\load\load.ctl
sqlplus -s email@example.com @%MyDir%\my_procedure.sql
Then I saved file as finename.bat or filename.cmd. I click it to run, it will run sqlloader first to load flat file into table. Then it will loginto
SQLPLUS envirnment with pointed database, then it just hang on there. I know SQLPLUS needs adding ' / ' at the end or underneath to make procedure run. I just type '/' at prompt and hit enter key, the procedure will be executed successfully.
The problem is: if I add ';' at the end of my_procedure.sql or add ' / ' underneath into the batch file. It will result in Windows system can not open my_procedure.sql file with SP2-0310 error. If don't have ';' or ' / ', SQLPLUS will not execute my_procedure. So please help me to solve this. How can I add either ';' or ' / ' to the code in batch file and let Windows system can transport into SQLPLUS envirnment for the execution. Thanks a lot.
do you have a / at the bottom of my_procedure.sql
Please read my post. I said if I put "/" at the bottom of my_procedure.sql, the windows will treat it as part of the file path, the file can not be identified and compile into SQLPLUS envirnment for execution. If don't have "/" at the bottom of my_procedure.sql, the script will not be executed. This is my problem and the reason I post here for help.
At current batch file and sqlplus envirnment, I manually type "/" at the bottom, my_procedure.sql will be run successfully. Any idea?
Why would WinDoze care about the contents of your file or even interpret them?
Originally Posted by traveller
You need to put the '/' after an SQL statement, but if you have a procedure (like exec MyProc() you need the ';' after the procedure.
Also, you need to add 'exit' at the end of your script.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Thanks for your input. It is right and will work.
Last edited by traveller; 12-15-2008 at 07:10 PM.
Click Here to Expand Forum to Full Width