sqlplus in Execute Command

[Joshi]

Hi,

we are executing an sqlplus command through an ExecuteCommand Stage and we would like to hide user/password information in the list of parameters. Any idea? What about environment variables?

Joerg.

[ArndW]

Gruezi Joshi,

You can use environment variables and a UNIX script to read & use them; there is no way to hide the command line when executing a shell command from DataStage. Remember that you need to SET them in the same process right before executing the shell - you cannot rely on any inherited environment attributes.

[Joshi]

Hi,

well, we are on Windows here and tried to use #ENV_VAR# in the sqlplus parameter list. Did not work. We do not need to hide the command line, but we would like to hide the user and password combination. How is this done (set environment variables before executing commands)?

Joerg.

[Sainath.Srinivasan]

You can use parameters from the job sequencer in your Execute Command Routine.

Use sqlplus with -s option to make it silent and not display any feedback.

[ArndW]

Joerg,

since the Command Stage only lets you execute one command, it would be best to write a short DataStage Routine to do this.

Define a routine with 5 parameters containing the information your script needs, such as: UnixCommand, OraInstance, OraSchema, OraUser, OraPassword.

Then build your command(s) and use the EXECUTE Basic statement.

[Joshi]

Hi,

I tried the option to use parameters from the job sequencer. How do I have to use the parameter (#PARAM#, $PARAM, ....)?

Joerg.

[ArndW]

Joerg,

you can specify them with #ParameterName# - but you can also use the clickbox "..." on the right part of the command window to insert parameters.

Viel Spass...

[ray.wurlod]

UNIX response, at least initially.

To hide the password from ps -ef users, you must employ a shell script, whether you call it from a before/after subroutine, a command activity or a routine.

Code: Select all

#!/bin/sh
# Invoke sqlplus command
# Environment variables contain authentication and SQL
# A "here script" is used to have sqlplus prompt for password, 
# rather than have it appear on the command line.
sqlplus ${ORACLE_USER}@${ORACLE_DB} << -END
${ORACLE_PASSWORD}              # response to password prompt
${SQL_STATEMENT}                # SQL statement to execute
quit                            # exit from sqlplus
-END

I'm guessing there's a way to do something like this in a BAT file, but don't have the skills. You might try this and see whether it works.

Code: Select all

sqlplus %ORACLE_USER%@%ORACLE_DB%
%ORACLE_PASSWORD%
%SQL_STATEMENT%
QUIT