USER DEFINED SQL FILE WITH DRS STAGE

kris007 · Post by **kris007** » Wed Jun 20, 2007 2:31 pm

Which argument are you talking about? Can you cut and paste it here? Just changing UNIX to NT should do.

vnspn · Post by **vnspn** » Wed Jun 20, 2007 2:39 pm

I tried changing the first argument from "UNIX" to "NT", and it didn't work. If I Test the routine, it just gets hanged.

I'm talking about the secong argument "InputArg" which you pass to the 'DSExecute'.

Code: Select all

InputArg = "sqlplus":' ':USERNAME:'/':PASSWORD:'@':INSTANCE:' ':'@':PATH

I think the format of this statement has been framed should be sightly different for executing it on Windows!

kris007 · Post by **kris007** » Wed Jun 20, 2007 3:07 pm

I ran the routine on Windows originally. I changed it to meet the requirements of the OP. Why don't you try it and let me know if it doesnt work.

chulett · Post by **chulett** » Wed Jun 20, 2007 3:09 pm

vnspn wrote:I think the format of this statement has been framed should be sightly different for executing it on Windows!

Nope, it's exactly the same.

vnspn · Post by **vnspn** » Wed Jun 20, 2007 3:22 pm

Ok, I tried executing the same code with changing just the first argument from "UNIX" to "NT".

In the Director log, I get a warning message from the routine as "Error when executing command.". So looking into your code for that loop, it should have had a condition where 'SystemReturnCode' should have been something other than '0'.

Also, in the Director log, I see message returned after successfully getting connected to the database. So, the error is in the SQL statement part of it...

kris007 · Post by **kris007** » Wed Jun 20, 2007 3:35 pm

Try executing just this statement from your command line.

Code: Select all

sqlplus && USERNAME/PASSWORD @ INSTANCE  @PATH

vnspn · Post by **vnspn** » Wed Jun 20, 2007 4:02 pm

Thanks Krish. I would try that and let you know.

In the mean time, we would want to execute a direct SQL statment in the routine rather executing it from a .sql file. In that case, can I substitute the complete SQL statement at the place where we specify the 'PATH' currently.

vnspn · Post by **vnspn** » Wed Jun 20, 2007 11:08 pm

The last part in building the 'InputArg' is the argument PATH.

In my case, I do need to substitute a SQL statement instead of a PATH where the SQL is available.

For example, you have provided it as
sqlplus <username>/<password>@<dbname> @ <PATH>

But, I want to execute it as,
sqlplus <username>/<password>@<dbname> @ SELECT COUNT(*) FROM DUAL;

When I try to executing with the above line as the 'InputArg', I get an error message that - SP2-0310: unable to open file "SELECT.sql"

So, instead of considering the last part as an SQL statement, it considers it as the file name that contains the SQL.

Please let me know how to overcome this.

Thanks.

chulett · Post by **chulett** » Thu Jun 21, 2007 6:36 am

vnspn wrote:So, instead of considering the last part as an SQL statement, it considers it as the file name that contains the SQL.

Because you told it it was a file through the use of the '@' symbol. Try removing it and see what happens.

Not saying it will work, but worth a shot. Still think you are better scripting this in a 'here document' style so that errors can be properly captured. Your way, all you'll know is if you were able to get into sqlplus or not, errors generated by the sql itself won't be reported.

vnspn · Post by **vnspn** » Thu Jun 21, 2007 7:01 am

I tried removing the '@' symbol. The command that was passed to 'InputArg' would look like,

Code: Select all

sqlplus <username>/<password>@<dbname> SELECT COUNT(*) FROM DUAL;

I tried printing the output of this in the Director log. It gets connected to the database, but the SQL command is not recognized after that. The output on the log looks like,

SQL*Plus: Release 10.2.0.2.0 - Production

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Usage 1: sqlplus -H | -V

-H Displays the SQL*Plus version and the
usage help.
-V Displays the SQL*Plus version.

Usage 2: sqlplus [ [<option>] [<logon>] [<start>] ]

<option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]

-C <version> Sets the compatibility of affected commands to the
version specified by <version>. The version has
the form "x.y[.z]". For example, -C 10.2.0
-L Attempts to log on just once, instead of
reprompting on error.
-M "<options>" Sets automatic HTML markup of output. The options
have the form:
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
-R <level> Sets restricted mode to disable SQL*Plus commands
that interact with the file system. The level can
be 1, 2 or 3. The most restrictive is -R 3 which
disables all user commands interacting with the
file system.
-S Sets silent mode which suppresses the display of
the SQL*Plus banner, prompts, and echoing of
commands.

<logon> is: (<username>[/<password>][@<connect_identifier>] | /)
[AS SYSDBA | AS SYSOPER] | /NOLOG

Specifies the database account username, password and connect
identifier for the database connection. Without a connect
identifier, SQL*Plus connects to the default database.

The AS SYSDBA and AS SYSOPER options are database administration
privileges.

The /NOLOG option starts SQL*Plus without connecting to a
database.

<start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]

Runs the specified SQL*Plus script from a web server (URL) or the
local file system (filename.ext) with specified parameters that
will be assigned to substitution variables in the script.

When SQL*Plus starts, and after CONNECT commands, the site profile
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run. The files may
contain SQL*Plus commands.

Refer to the SQL*Plus User's Guide and Reference for more information.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 21, 2007 3:29 pm

Have you investigated generating a "here script" as I suggested earlier?

vnspn · Post by **vnspn** » Mon Jun 25, 2007 12:03 pm

Hi Ray,

By "here script", do mean the one below,

Code: Select all

cmd = "sqlplus -s ":userID:"/":passWD:"@":dbName
cmd<-1> = "SELECT COUNT(*) FROM DUAL;"
cmd<-1> = "quit;"

Call DSExecute("NT",cmd,output,rc)

I tried with this and it still didn't work. I printed the output of the command in the Director log. It is as below,

Error 45 initializing SQL*Plus
Internal error
DataStage/SQL: Table "DUAL" does not exist.
Verb "QUIT;" is not in your VOC.

Is there a way we could resolve this?

Thanks.

chulett · Post by **chulett** » Mon Jun 25, 2007 12:09 pm

Ask your DBA what this means: "Error 45 initializing SQL*Plus".

ray.wurlod · Post by **ray.wurlod** » Mon Jun 25, 2007 12:18 pm

No that is not what I meant by a "here script". Read my posts carefully again, and get some advice from someone competent with shell scripts.