This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Aug 6, 2013 11:49 AM by user499554 RSS

Scheduler doesn't fetch returncode shell script

776878 Newbie
Currently Being Moderated
When i run the following script on the command line, i get the appropriate exit code (1). But when i run the same script true the oracle scheduler i get exit code (0).

#!/bin/ksh
/.../exit1.sh
echo $? >test.txt



And exit1.sh is just

#!/bin/ksh
exit 1

The oracle schedueler doesn't fetch the exit code.

i'm using an 11g db.

Thx for you help!

Greetzz,

Tim
  • 1. Re: Scheduler doesn't fetch returncode shell script
    P.Forstmann Guru
    Currently Being Moderated
    This is not a DBMS_SCHEDULER issue but a UNIX scripting misunderstanding:

    With Oracle Entreprise Linux, I have the following:
    [oracle@lx01 tmp]$ uname -a
    Linux lx01.localdomain 2.6.18-92.el5 #1 SMP Fri May 23 22:17:30 EDT 2008 i686 i686 i386 GNU/Linux
    [oracle@lx01 tmp]$ cat test1.ksh
    #!/bin/ksh
    ./exit1.ksh
    echo $? >test.txt
    [oracle@lx01 tmp]$ cat exit1.ksh
    #!/bin/ksh
    exit 1
    [oracle@lx01 tmp]$ ./test1.ksh
    [oracle@lx01 tmp]$ echo $?
    0
    [oracle@lx01 tmp]$ cat test.txt
    1
    It is normal that test1.ksh returns 0 because last statement of test1.ksh does not fail and does not explicitely returns 1: by default it will return 0.

    If you remove the echo statement you get:
    [oracle@lx01 tmp]$ cat test2.ksh
    #!/bin/ksh
    ./exit1.ksh
    [oracle@lx01 tmp]$ ./test2.ksh
    [oracle@lx01 tmp]$ echo $?
    1
    And with Oracle 11.2 I get:
    SQL>
    SQL> begin
      2   dbms_scheduler.create_job('myjob',
      3     job_action=>'/tmp/test2.ksh',
      4     job_type=>'executable', enabled=>false);
      5   dbms_scheduler.enable('myjob');
      6  end;
      7  /
    
    PL/SQL procedure successfully completed.
    SQL> select log_date, job_name, status from user_scheduler_job_log;
    
    LOG_DATE
    ---------------------------------------------------------------------------
    JOB_NAME
    --------------------------------------------------------------------------------
    STATUS
    ------------------------------
    03-JUN-10 12.09.41.032534 PM +02:00
    MYJOB
    FAILED
    Edited by: P. Forstmann on 3 juin 2010 12:11
  • 2. Re: Scheduler doesn't fetch returncode shell script
    776878 Newbie
    Currently Being Moderated
    If i run the script on the command line in Unix, i get the return code 1.

    But if i run it with the scheduler it doesn't !

    (i've tried changing exit 1 -> return 1 .. )
  • 3. Re: Scheduler doesn't fetch returncode shell script
    P.Forstmann Guru
    Currently Being Moderated
    Please give full output of your UNIX and SQL scripts including display of script return code and how you check job status in Oracle.

    You must take care that all scripts are executable by the right Unix user account and that the scripts have full path name:

    With these Unix scripts:
    [oracle@lx01 tmp]$ cat /tmp/test2.ksh
    #!/bin/ksh
    /tmp/exit1.ksh
    [oracle@lx01 tmp]$ cat /tmp/exit1.ksh
    #!/bin/ksh
    exit 1
    [oracle@lx01 tmp]$ ls -al /tmp/*.ksh
    -rwxr-xr-x 1 oracle oinstall 18 Jun  3 11:58 /tmp/exit1.ksh
    -rwxr--r-- 1 oracle oinstall 41 Jun  3 11:59 /tmp/test1.ksh
    -rwxr-xr-x 1 oracle oinstall 26 Jun  3 12:40 /tmp/test2.ksh
    I get the right Unix return code in ERROR# column:
    SQL>
    SQL> begin
      2   dbms_scheduler.create_job('mytest2',
      3     job_action=>'/tmp/test2.ksh',
      4     job_type=>'executable', enabled=>true);
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select log_date, job_name, status, error#, additional_info
      2   from user_scheduler_job_run_details
      3   where job_name = 'MYTEST2';
    
    LOG_DATE
    ---------------------------------------------------------------------------
    JOB_NAME
    --------------------------------------------------------------------------------
    STATUS                             ERROR#
    ------------------------------ ----------
    ADDITIONAL_INFO
    --------------------------------------------------------------------------------
    03-JUN-10 12.43.30.448881 PM +02:00
    MYTEST2
    FAILED                                  1
    ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted
    See also GUIDE TO RUNNING EXTERNAL JOBS ON 10g WITH DBMS_SCHEDULER : Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch files
    Edited by: P. Forstmann on 3 juin 2010 12:41

    Edited by: P. Forstmann on 3 juin 2010 12:48

    Edited by: P. Forstmann on 3 juin 2010 12:52
  • 4. Re: Scheduler doesn't fetch returncode shell script
    776878 Newbie
    Currently Being Moderated
    My program:

    BEGIN
    SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
    program_name => 'JWS.TEST_SCHELL'
    ,program_type => 'EXECUTABLE'
    ,program_action => '/projects/temp/sh/testsched.sh'
    ,number_of_arguments => 1
    ,enabled => FALSE
    ,comments => ''
    );

    SYS.DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT
    (
    program_name => 'JWS.TEST_SCHELL'
    ,argument_name => 'A'
    ,argument_position => 1
    ,argument_type => 'VARCHAR2'
    ,default_value => 'tim'
    );

    SYS.DBMS_SCHEDULER.ENABLE
    (name => 'JWS.TEST_SCHELL');
    END;
    /



    My jobschedule :


    BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB
    (
    job_name => 'JWS.TEST_SHELL'
    ,start_date => NULL
    ,repeat_interval => NULL
    ,end_date => NULL
    ,program_name => 'JWS.TEST_SCHELL'
    ,comments => NULL
    );
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'JWS.TEST_SHELL'
    ,attribute => 'RESTARTABLE'
    ,value => FALSE);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'JWS.TEST_SHELL'
    ,attribute => 'LOGGING_LEVEL'
    ,value => SYS.DBMS_SCHEDULER.LOGGING_FULL);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name => 'JWS.TEST_SHELL'
    ,attribute => 'MAX_FAILURES');
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name => 'JWS.TEST_SHELL'
    ,attribute => 'MAX_RUNS');
    BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'JWS.TEST_SHELL'
    ,attribute => 'STOP_ON_WINDOW_CLOSE'
    ,value => FALSE);
    EXCEPTION
    -- could fail if program is of type EXECUTABLE...
    WHEN OTHERS THEN
    NULL;
    END;
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'JWS.TEST_SHELL'
    ,attribute => 'JOB_PRIORITY'
    ,value => 3);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name => 'JWS.TEST_SHELL'
    ,attribute => 'SCHEDULE_LIMIT');
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name => 'JWS.TEST_SHELL'
    ,attribute => 'AUTO_DROP'
    ,value => FALSE);
    END;
    /





    My script :


    #!/bin/ksh
    HOME=/projects/temp
    export HOME
    SPOOL=${HOME}/spool
    export SPOOL
    SH=${HOME}/sh
    export SH
    mkdir -p ${SPOOL}"/"`date +"%Y"`"/"`date +"%m"`"/"`date +%d`
    SPOOLFILE=${SPOOL}"/"`date +"%Y"`"/"`date +"%m"`"/"`date +%d`"/"${jobparms[0]}"."`date +%H%M%S`.log
    export SPOOLFILE
    exec >>${SPOOLFILE}
    /projects/temp/sh/exit_1.sh
    returncode=$?
    echo "${returncode}"
    if [[ ${returncode} -ne 0 ]] then
    exit 1
    else
    exit 0
    fi



    When i run :

    ./projects/temp/sh/testsched.sh

    on my command lin in UNIX i get in my spoolfile: 1

    When i rung my scheduled job with Toad, i get in my spoolfile: 0


    hope this helps.
  • 5. Re: Scheduler doesn't fetch returncode shell script
    776878 Newbie
    Currently Being Moderated
    select job_name, status, error#, req_start_Date
    from user_scheduler_job_run_details
    where job_name = 'TEST_SHELL'
    order by log_id desc;


    JOB_NAME
    -----------------------------------------------------------------
    STATUS ERROR # REQ_START_DATE
    ------------------------------ ---------- -----------------------------------
    TEST_SHELL
    SUCCEEDED 0 03-JUN-10 02.32.56.027647 PM EUROPE
    /BRUSSELS


    I expected the error to be 1 !
  • 6. Re: Scheduler doesn't fetch returncode shell script
    776878 Newbie
    Currently Being Moderated
    if i start a scheduled job with toad like:


    #!/bin/ksh
    /sh/exit_1.sh
    if [[ $? -ne 0 ]] then
    exit 1
    else
    exit 0
    fi




    i receive the exit 0 ?!?

    Edited by: user9157531 on 4-jun-2010 3:53
  • 7. Re: Scheduler doesn't fetch returncode shell script
    P.Forstmann Guru
    Currently Being Moderated
    I cannot reproduce your issue. I get return code 1 in Linux shell and in Oracle with Oracle 11.2.0.1:
    [oracle@lx01 tmp]$ uname -a
    Linux lx01.localdomain 2.6.18-92.el5 #1 SMP Fri May 23 22:17:30 EDT 2008 i686 i686 i386 GNU/Linux
    [oracle@lx01 tmp]$ cat test3.ksh
    #!/bin/ksh
    /tmp/exit1.ksh
    if [[ $? -ne 0 ]] then
    exit 1
    else
    exit 0
    fi
    [oracle@lx01 tmp]$ cat exit1.ksh
    #!/bin/ksh
    exit 1
    SQL> show errors
    No errors.
    SQL>
    SQL> begin
      2   dbms_scheduler.create_job('mytest3',
      3     job_action=>'/tmp/test3.ksh',
      4     job_type=>'executable', enabled=>true);
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    
    SQL> show error
    No errors.
    SQL>
    SQL> host sleep 5
    
    SQL>
    SQL> select log_date, job_name, status, error#, additional_info
      2  from user_scheduler_job_run_details
      3  where job_name = 'MYTEST3';
    
    LOG_DATE
    ---------------------------------------------------------------------------
    JOB_NAME
    --------------------------------------------------------------------------------
    STATUS                             ERROR#
    ------------------------------ ----------
    ADDITIONAL_INFO
    --------------------------------------------------------------------------------
    04-JUN-10 04.11.57.514321 PM +02:00
    MYTEST3
    FAILED                                  1
    ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted
    Edited by: P. Forstmann on 4 juin 2010 16:16
  • 8. Re: Scheduler doesn't fetch returncode shell script
    776878 Newbie
    Currently Being Moderated
    I cannot reproduce your issue. I get return code 1 in Linux shell and in Oracle with Oracle 11.2.0.1:

    That is because you start the job from the command line.

    If i do this, it works as well.

    But if Oracle scheduler starts the job, it doesn't work.

    Thx for your help
  • 9. Re: Scheduler doesn't fetch returncode shell script
    P.Forstmann Guru
    Currently Being Moderated
    In my scenario I do 2 things which are independant:
    1. I run shell script manually from shell command line: this works.
    2. I create the DBMS_SCHEDULER job using SQL*Plus and let Oracle Database (the scheduler) run it automatically: this also works.

    What is your 4 digits Oracle version ?
    What is your exact OS version (uname -n) ?
    How is exactly started the job in your case ?
  • 10. Re: Scheduler doesn't fetch returncode shell script
    776878 Newbie
    Currently Being Moderated
    OS version:

    AIX 1 6 0008B71AD400


    Oracle:

    11.2.0.1.0

    Edited by: user9157531 on 4-jun-2010 7:42
  • 11. Re: Scheduler doesn't fetch returncode shell script
    776878 Newbie
    Currently Being Moderated
    There could be a problem with the first line:

    if i run

    #!/bin/ksh
    /sh/exit_1.sh
    if [[ $? -ne 0 ]] then
    exit 1
    else
    exit 0
    fi


    i get exit 0


    if i run


    /sh/exit_1.sh
    if [[ $? -ne 0 ]] then
    exit 1
    else
    exit 0
    fi


    i get exit 1 !


    but i need #!/bin/ksh
    to make the rest of the code to work.
  • 12. Re: Scheduler doesn't fetch returncode shell script
    P.Forstmann Guru
    Currently Being Moderated
    I cannot test this on AIX and I don't understandby why it does not work (there was a limitation for DBMS_SCHEDULER on AIX only with Oracle 10.2.0.1 not with 11.2.0.1). I recommend to contact Oracle support and open a SR.
  • 13. Re: Scheduler doesn't fetch returncode shell script
    776878 Newbie
    Currently Being Moderated
    ok thx for you assistance, i'll get in touch with oracle and i'll let you know.
  • 14. Re: Scheduler doesn't fetch returncode shell script
    851977 Newbie
    Currently Being Moderated
    Does anyone have some more information on this issue? I am experiencing basically the same problem. 10g and AIX. We use the scheduler to start a shell script from an apex interface.

    In the shell script, we call some C programs. The C code is returning non 0, but when we check the return code with $? in the script it is always 0. When I run the script from the command line, everything works fine and the return codes are the expect values. Thanks!

    Edited by: 848974 on Mar 31, 2011 8:28 AM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points