Running a .sql file

[major]

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

[chulett]

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.

[narasimha]

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.

Code: Select all

WHENEVER SQLERROR EXIT FAILURE;

HTH

[chulett]

Or that.

[major]

got it

Thanks Chulett and narasimha.