Sql Job fails with "Could not find Sql Loader executable" or ""sqlplus: command not found" error

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

When running an IMPORT/EXPORT Job, or sql Jobs such as FORECAST or HISTORY_PURGE, it Aborts with on the following errors:

Could not find Sql Loader executable

or

Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
Could not login to sqlplus

or 

ORACLE_HOME = [/home/oracle] ? starting sqlp 
sqlplus -s @/path/to/sql/script script1.lis
/appworx/home/exec/SQLP: line 31: sqlplus: command not found
Could not login to sqlplus

or

appworx/exec/BODY: line 319: .: oraenv: file not found

Environment

Component : APPLICATIONS MANAGER

Cause

Sql Loader executable is not found in environment/path

Or

Oracle Home value may have been overwritten with a new value due to oraenv being called

Resolution

Correct the Oracle environment to stop it from being overwritten by '.oraenv'.

Additional Information

Enabling BODY debug, rerunning the Job, and reviewing the output file will provide additional information about the ORACLE environment. In many cases, the ORACLE environment is being overwritten when '.oraenv' is being called. This occurs in the BODY script when a parameter is missing in the scripting logic such as the net_connect value.  Below is snippet of the BODY script that determines if '.oraenv' will be called or not.

            if [ "$login_sid" != "" -a "$net_connect" = "" -a "$ORACLE_SID" != "$login_sid" ]; then
  if [ "$db_type" = "ORACLE" -o "$db_type" = "OAE" ]; then
   ORACLE_SID=$login_sid
                 ORAENV_ASK=NO
                 export ORACLE_SID ORAENV_ASK
                 . oraenv </dev/null

The most common solution is to review the Oracle Login object definition and adding a "network_alias" value (net_connect).