How to run a SQL in JOB control

[DJRH]

Hi ,

Can anybody tell me, how can i run a SQL query in JOB CONTROL. I need to run the SQL query in Job control and capture its output and based on the output I need to kick off another job.

Please advise

Thanks

[rohit1231]

Try this:
[code]
Call DSExecute("UNIX", $ORALE_HOME/../sqlplus/$COMMAND , Result, ReturnCode)
[/code]

[DJRH]

Try this:

Code: Select all

Call DSExecute("UNIX", $ORALE_HOME/../sqlplus/$COMMAND , Result, ReturnCode)

Thanks for you reply, I tried the below mentioned command and it did not work.

BuildCmd =sqlplus ETL/ETL@MIRDB.PRD select * from dual
call DSExecute ("UNIX", BuildCmd , CmdOutput, CmdRetCode)

But if i copy the command in .sql (mentioned below ) and then run it thats work fine but i do not want .sql file...Please advise if it is possible to run without .sql.

BuildCmd =sqlplus ETL/ETL@MIRDB.PRD </source/test1.sql
call DSExecute ("UNIX", BuildCmd , CmdOutput, CmdRetCode)

[rohit1231]

What is your actual requirement and why would the sql file not work for you

Just curious as there may be a better way based on your requirement

[DJRH]

What is your actual requirement and why would the sql file not work for you

Just curious as there may be a better way based on your requirement

I want to check some data through table query and based on the output of the query I will be executing another job. I do not want to add any object like file creation or the .sql on the server. I want to run the query directly in the Job Control and capture the output and based on the output run the downstream job.

I hope I answered what you asked

[ray.wurlod]

Tell us the error message so that we don't need to waste time guessing.

My guess is that the PATH environment variable is not set to include the Oracle bin directory for the user that executes DataStage job.

[DJRH]

Tell us the error message so that we don't need to waste time guessing.

My guess is that the PATH environment variable is not set to include the Oracle bin directory for the user that executes DataStage job.

This is the Error message I got.

CmdOutput contains :Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
<start> ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-L" attempts log on just once
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode

[ray.wurlod]

Good. We've established that the command can be found. We've also established that the syntax of your sqlplus command is incorrect. Does this form of the command (redirecting stdin) work from the command line?

[DJRH]

Good. We've established that the command can be found. We've also established that the syntax of your sqlplus command is incorrect. Does this form of the command (redirecting stdin) work from the command line?

Hi,
I looked around for the solution and here is what i found from the forum.

I can run the sql command if the command is stored in the .sql file but i do not wanted any addiotnal object to be created on the server thus avoided.

We can also run the sql command through the below mentioned command.

BuildCmd =sqlplus ETL/ETL@MIRDB.PRD <<EOF":char(10):"select * from dual ;":char(10):"EOF":char(10)
call DSExecute ("UNIX", BuildCmd , CmdOutput, CmdRetCode)


Now, my curiousity is can we avoid the EOF used in SQL command else this works for me.

[ray.wurlod]

Because you've constructed a "here script" you need some way to end the nested command stack. It doesn't have to be EOF, you can use any string. The nested command stack begins with <<token and ends with token.