Page 1 of 1
sqlplus in Execute Command
Posted: Fri Feb 25, 2005 1:33 am
by 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.
Posted: Fri Feb 25, 2005 2:04 am
by 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.
Posted: Fri Feb 25, 2005 2:45 am
by 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.
Posted: Fri Feb 25, 2005 3:04 am
by 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.
Posted: Fri Feb 25, 2005 3:06 am
by 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.
Posted: Fri Feb 25, 2005 4:29 am
by Joshi
Hi,
I tried the option to use parameters from the job sequencer. How do I have to use the parameter (#PARAM#, $PARAM, ....)?
Joerg.
Posted: Fri Feb 25, 2005 4:36 am
by 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...
Posted: Fri Feb 25, 2005 3:39 pm
by 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