DSExecute with sqlplus giving ORA error

[bmsq]

Hi guys,

We are in the process of migrating our DS jobs from our Dev and testing environments to our SVT/PROD environments. However, in doing so we seem to have uncovered a rather baffling problem when trying to execute sqlplus from DS basic using DSExecute.

Here is a cut down version of our ExecuteSQL DS Basic routine which is raising the error:

Code: Select all

* ------------------------------------------------------------
* Set Routine Constants.
* ------------------------------------------------------------
Lf = Char(10)
CrLf = char(13):Char(10)
RoutineName = "ExecuteSQL"
* ------------------------------------------------------------
* Generate the SqlPlus commands.
* ------------------------------------------------------------
Command<1> = 'sqlplus -S ': Username :'/': Password :'@': Instance :' <<END'
Command<2> = Convert(@FM, Lf, SqlStatement)
Command<3> = 'EXIT 0;'
Command<4> = 'END'

Command = Ereplace(Command, @FM, CrLf)
* ------------------------------------------------------------
* Execute SqlPlus from the UNIX Shell.
* ------------------------------------------------------------
Call DSExecute("UNIX", Command, SqlResult, SystemReturnCode)

Now this routine has worked on about 4-5 different environments without problem, however our PROD/SVT environment returns the following errors from Oracle:

Code: Select all

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory


SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_string>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_string>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

I've checked all the parameters for the routine and there is nothing wrong. I've gone through all the environment settings and the only notable difference I could see was that the "SHELL" variable is "/bin/ksh" in SVT rather than "bash" like in all the other environments. However, I've manually performed the same commands from the command line (both bash and ksh) and sqlplus logged in without a problem.

Can anyone give me any suggestions as to what could be causing this? We are quickly reaching the end of our SVT phase and things are starting to get desparate.

[ArndW]

Most likely it is the environment and some missing oracle settings. Could you add a

Code: Select all

Call DSExecute("UNIX", 'env', CommandResult, SystemReturnCode) 

call to the BASIC code and compare the actual runtime values?

[chulett]

I'd suggest a couple of additional logging statements to see if that helps. Put in calls to DSLogInfo for things like the Command just before you execute it or anything else you'd like to confirm. I'd also try removing the "-S" option temporarily and see if that is hiding something useful.

I personally wouldn't force a zero exit status like that, it can mask problems with your actual sql executed. We tee off the output to a tmp file based on the PID and grep it for Oracle errors after completion. The results of that check are what determine the exit status. Just a thought, unless something like that is happening in the 'un-cut-down' version.

[bmsq]

Thanks for your response guys,

As stated the shown DS code is a cut down version of what we are actually using. The real code has a debug switch to log information at all steps using DSLogInfo. This is why I'm sure what I've executed manually was EXACTLY the same as what is performed in DS Basic.

Also, the full code exit on error so I'm not forcing an exit status. In fact, the error I posted was logged to the director log by the error handling.

However, I'll try removing the silent switch to see if there is any extra information being hidden.

I'll also get the env variables using DSExecuted as suggested. This routine is called from the sequencer, so shouldn't the output of this be exactly the same as already displayed at the start of the logs? Hmm, I'll see what happens.

Thanks, I'll let you know how this goes.

[chulett]

The only reason I mentioned the exit code was this snippet:

Code: Select all

Command<3> = 'EXIT 0;'

You'll always get the exit code of sqlplus itself, your error shows that you couldn't launch / connect to it cleanly. I was concerned about the times where you connect to and exit from sqlplus just fine but the sql you run inside the session throws an error. If you've got that covered, well then - groovy.

[bmsq]

Thanks Chutlett,

The full routine includes

Code: Select all

WHENEVER OSERROR EXIT 1
WHENEVER SQLERROR EXIT 1

Anyway, the issues has been resolved. Our environment actually has two databases, only one of which was causing the error. Turns out the Environment support guys only tested one database from the command line, not both. Once that was discovered, a quick look at the TNS names for the problematic SID was enough to notice an incorrect ip address.

Bah!

Thanks for all your help guys,

Appreciated!

[chulett]

Aha! :D

Glad you got it sorted out.