Oracle JOB fails

Question

I have a Oracle JOB that runs a executable (bash script).. This scripts runs itself SQLPLUS which runs a PACKAGE

Check this

function run_pkg_load_svr() {
    if [ "$1" != "" ] && [ "$2" != "" ];
    then
        sqlplus -l -s $1/$2 <<EOF
            WHENEVER SQLERROR EXIT 99;
            WHENEVER OSERROR EXIT 88;
            BEGIN
                PKG_LOAD_SVR.SP_MAIN();
             END;
            /  
EOF
        pkg_retcode=$?
        echo $DATE_LOG >> upload.log
        echo "PKG EXIT CODE: " $pkg_retcode >> upload.log
    fi
}

SO, if I run the JOB, its exit code (PKG) is 1

But, If I run manually the package or the script, it works

STATUS is SUCCEEDED in log events

How is possibile to have such a problem?

In addition, when SP_MAIN starts I populate also a log_table.. even that doesn't work

Answer 1

You probably doesn't even connect to the database.

I get the same 1 status here if I give wrong user /password:

host:/jcho $  sqlplus -l -s  a/b@database <<EOF
>  select 1 from dual;
> EOF
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

host:/jcho $ echo $?
1

... First be sure you are able to connect.

edit

I also get same error when database I try to connect doesn't exist:

host:/jcho $  sqlplus -l -s  a/b@unknown_database <<EOF
>  select 1 from dual;
> EOF
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
    
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
host:/jcho $ echo $?
1

So make sure you're in the same context when commands are run from Oracle Job and manually. Especially, display content of environment variables ORACLE_SID, (TWO_TASK?), ORACLE_HOME and PATH (PATH should contain $ORACLE_HOME/bin);

And also check variables are correctly set by displaying them from your script: add

 echo USER-PASSWORD: $1-$2

as first line of your function.