Run shell script(fr Ds) that contains a call to sqlplus

dprasanth · Post by **dprasanth** » Fri Oct 28, 2005 5:08 am

Hi,
I am running a simple shell script through Datastage as a BEFORE ROUTINE.
This is the script.
sqlplus -s username@engt1/password <<EOF
execute useradmin.msp_loader.drop_index('rnkt0','attendance',null,'ALL');
EOF. I have put this in a shell script called drop_index.ksh and it is present in the path /app03/engineering/dpr on my unix box. So I am executing the shell script as BEFORE ROUTINE(ExecSH).But I get the following error
Afterjobroutine..BeforeJob (ExecSH): Error when executing command: /app03/engineering/dpr/drop_index.ksh
*** Output from command was: ***
/app03/engineering/dpr/find_date.ksh: sqlplus: not found. I searched the forum and got the below result. But I really couldn't fix my problem with the below solution

viewtopic.php?t=89449&highlight=sqlplus%3A+not+found

Can anyone of you please let me know what can be done about this?

Thanks in Advance.

Regards,
Dprasanth

kcbland · Post by **kcbland** » Fri Oct 28, 2005 6:06 am

sqlplus is not in the path for the DS engine process. Check the path in the dsenv file in the DSEngine directory. The DS engine was started without the path in the environment file that can find the Oracle binaries. Fix that and you fix your problems.

The other method is to reference the binaries directly in your script, or add to the path yourself the Oracle binaries directory.

dprasanth · Post by **dprasanth** » Fri Oct 28, 2005 6:32 am

kcbland wrote:sqlplus is not in the path for the DS engine process. Check the path in the dsenv file in the DSEngine directory. The DS engine was started without the path in the environment file that can find the Oracle binaries. Fix that and you fix your problems.

The other method is to reference the binaries directly in your script, or add to the path yourself the Oracle binaries directory.

I had added the $ORACLE_HOME/bin in dsenv PATH and LD_LIBRAY_PATH and had executed dsenv. But still it is complaning.
These are my entries in dsenv
ORACLE_HOME=/usr/oracle/product/9.2.0.6; export ORACLE_HOME
PATH=$DF30DIR/bin:$PATH:$ORACLE_HOME/bin.
I had added the PATH through DS Adminstrator in the ENVIORNMENT VARIABLES. But still it is complaining

chulett · Post by **chulett** » Fri Oct 28, 2005 6:53 am

At worst case, add it to your script - either that or put a fully pathed (instead of a relative one) reference to sqlplus there.

dprasanth wrote:These are my entries in dsenv
ORACLE_HOME=/usr/oracle/product/9.2.0.6; export ORACLE_HOME
PATH=$DF30DIR/bin:$PATH:$ORACLE_HOME/bin.

You don't literally have a dot at the end of 'bin' in your dsenv file, do you?

Make sure you export the new PATH as well.

dprasanth · Post by **dprasanth** » Fri Oct 28, 2005 6:57 am

chulett wrote:At worst case, add it to your script - either that or put a fully pathed (instead of a relative one) reference to sqlplus there.

dprasanth wrote:These are my entries in dsenv
ORACLE_HOME=/usr/oracle/product/9.2.0.6; export ORACLE_HOME
PATH=$DF30DIR/bin:$PATH:$ORACLE_HOME/bin.

You don't literally have a dot at the end of 'bin' in your dsenv file, do you? Make sure you export the new PATH as well.

Sorry to trouble you all... I had added all the entries in the dsenv file, but I forgot to restart the DS services

I restarted the DS services and the script started working. Thanks a lot for your time

ray.wurlod · Post by **ray.wurlod** » Fri Oct 28, 2005 6:57 pm

The reason this works, of course, is that dsenv is read (for example by dsrpcd) when DataStage is started. Not subsequently.