Hi,
I have a .sql file say Query.sql file , it contains a series of sql queries which include cursors , looping etc.
I need to run this Query.sql file from datastage.
Currently i am storing it in Unix and using the below command in before job subroutine to invoke it from a dummy job.
sqlplus USER_NAME/PWD@DSN_NAME #$FilePath#Query.sql
It runs file , the problem is the dummy job wont get aborted when the query in the Query.sql fails to run.
How to abort the dummy job ? Or is there any better way to do this.
As a simple solution we can write a stored proc and run it from datastage,but the clinet is adamant to use the same Query.sql and not interested in creating procs
Thanks
Running a .sql file
Moderators: chulett, rschirm, roy
That's because, by default, all your exit status indicates is the ability to execute the sqlplus command itself, it could care less what happens with the sql you run inside the session.
You need to capture the output from the sql session which can be as simple as redirecting it to a flat file. Then after the session ends, grep through the file for ORA or ERR lines, count them and set a non-zero exit status if that count is greater than zero.
You need to capture the output from the sql session which can be as simple as redirecting it to a flat file. Then after the session ends, grep through the file for ORA or ERR lines, count them and set a non-zero exit status if that count is greater than zero.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
The default behavior while executing sql scripts is - To continue and take no action when a SQL error occurs.
In the beginning of your sql file put the below line to exit the script with failure.
HTH
In the beginning of your sql file put the below line to exit the script with failure.
Code: Select all
WHENEVER SQLERROR EXIT FAILURE;
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.