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

Maybe the oracle account that executes the script has different environment settings and can't run sqlplus for some reason. You need to capture more diagnostic information. btw why host out to a shell script that calls SQL*Plus which connects back to (presumably) the same database? It seems a bit overcomplicated, not to mention insecure as you are passing the password around as plain text. — William Robertson, Feb 27 '17 at 11:42

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 27 '17 at 10:37

J. Chomel

8,193
15
41
69

Hi, gonna check it – Michael Johnks Feb 27 '17 at 10:42
Well, when in my bash script I call the function run_pkg_load_svr $user $pass edit: $user='dboracle' $pass='dboracle' – Michael Johnks Feb 27 '17 at 10:44
Then what happens when you do a `sqlplus -l -s dboracle/dboracle` from command line? do you get normal prompt? – J. Chomel Feb 27 '17 at 10:49
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options SQL> I run without -l -s, anyway it works I don't understand why If I run the script via command line, it works everything..instead via JOB ORACLE doesnt't – Michael Johnks Feb 27 '17 at 10:54
What do you get if you add `echo USER-PASSWORD: $1-$2` at the beginning of your script? Are the variables correctly set? – J. Chomel Feb 27 '17 at 11:50
Done, now I'm passing the service name and it works, thank you – Michael Johnks Feb 27 '17 at 13:07

Oracle JOB fails

1 Answers1

edit