1 2 Previous Next 18 Replies Latest reply: Aug 6, 2013 1:49 PM by user499554 RSS

    Scheduler doesn't fetch returncode shell script

    776878
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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