1 2 Previous Next 18 Replies Latest reply: Oct 11, 2013 1:31 PM by Anar Godjaev RSS

    Error Trying execute shell from oracle

    708631

      Using 11g

       

      Shell script

       

      #!/bin/bash

      clear

      echo "testing123"

       

      named transfer2.sh (This is just a testing shell script. the real one will scp a file to a remote server, but I cannot even get this one to work first).

      I can execute in linux no issues

       

      > sh transfer2.sh

      testing123

       

      I want to be able to schedule this to run from oracle.

       

       

      my testing so far :

       

      BEGIN

         DBMS_SCHEDULER.CREATE_JOB (

            job_name          => 'TEST_SHELL',

            JOB_TYPE          => 'EXECUTABLE',

            job_action        => '/myfiles/transfer2.sh',

            start_date        => SYSDATE,

            REPEAT_INTERVAL   => 'FREQ=HOURLY;', 

            enabled           => FALSE,

            comments          => 'shell script from Oracle'

         );

      end;

      /

       

       

      now to run the job

       

      BEGIN

        dbms_scheduler.run_job('TEST_SHELL',TRUE);

      end;

      /

       

       

       

      Error starting at line 43 in command:

      BEGIN

        dbms_scheduler.run_job('TEST_SHELL',TRUE);

      end;

      Error report:

      ORA-27369: job of type EXECUTABLE failed with exit code: 255

      ORA-06512: at "SYS.DBMS_ISCHED", line 185

      ORA-06512: at "SYS.DBMS_SCHEDULER", line 486

      ORA-06512: at line 2

      27369. 00000 -  "job of type EXECUTABLE failed with exit code: %s"

      *Cause:    A problem was encountered while running a job of type EXECUTABLE.

                 The cause of the actual problem is identified by the exit code.

      *Action:   Correct the cause of the exit code and reschedule the job.

       

       

      I have set the myfiles directory to 0777

      I have set all files in the myfiles directory to 0777

      so everyone should have access to execute scripts here.

       

      I have also set the extjob file to 0777

       

       

       

       

      any help appreciated.

      Thank you.

        • 1. Re: Error Trying execute shell from oracle
          24c14495-35a9-4391-8c06-25ca6abf36bd

          Execute shell script from Oracle stored procedure :

           

          Step 1 : Java Procedure to Run the Shell Command :

           

          create or replace and compile
          java source named "Util"
          as
          import java.io.*;
          import java.lang.*;

          public class Util extends Object
          {
            public static int RunThis(String args)
            {
            Runtime rt = Runtime.getRuntime();
            int rc = -1;

            try
          {
                 Process p = rt.exec(args);
                 int bufSize = 4096;
                 BufferedInputStream bis = new BufferedInputStream(p.getInputStream(), bufSize);
                 int len;
                 byte buffer[] = new byte[bufSize];
           
                 // Echo back what the program spit out
                 while ((len = bis.read(buffer, 0, bufSize)) != -1)
                    System.out.write(buffer, 0, len);
           
                 rc = p.waitFor();
              }
              catch (Exception e)
              {
                 e.printStackTrace();
                 rc = -1;
              }
              finally
              {
                 return rc;
              }
              }
            }
          /

          Java created.

           

          Step 2: Wrapper PL/SQL function to call the Java Procedure:

           

          rt_test@ORA9I.WORLD> create or replace
          function RUN_CMD(p_cmd in varchar2) return number
          as
          language java
          name 'Util.RunThis(java.lang.String) return integer';
          /

          Function created.

           

          Step 3: Executing the Unix command by call the Oracle Function :


          rt_test@ORA9I.WORLD> variable x number;
          rt_test@ORA9I.WORLD> set serveroutput on
          rt_test@ORA9I.WORLD> exec dbms_java.set_output(100000);

          PL/SQL procedure successfully completed.


          rt_test@ORA9I.WORLD> exec :y := RUN_CMD('/usr/bin/ls /tmp');

          • 2. Re: Error Trying execute shell from oracle
            Anar Godjaev

            708631 wrote:

             

            Using 11g

             

            Shell script

             

            #!/bin/bash

            clear

            echo "testing123"

             

            named transfer2.sh (This is just a testing shell script. the real one will scp a file to a remote server, but I cannot even get this one to work first).

            I can execute in linux no issues

             

            > sh transfer2.sh

            testing123

             

            I want to be able to schedule this to run from oracle.

             

             

            my testing so far :

             

            BEGIN

               DBMS_SCHEDULER.CREATE_JOB (

                  job_name          => 'TEST_SHELL',

                  JOB_TYPE          => 'EXECUTABLE',

                  job_action        => '/myfiles/transfer2.sh',

                  start_date        => SYSDATE,

                  REPEAT_INTERVAL   => 'FREQ=HOURLY;',

                  enabled           => FALSE,

                  comments          => 'shell script from Oracle'

               );

            end;

            /

             

             

            now to run the job

             

            BEGIN

              dbms_scheduler.run_job('TEST_SHELL',TRUE);

            end;

            /

             

             

             

            Error starting at line 43 in command:

            BEGIN

              dbms_scheduler.run_job('TEST_SHELL',TRUE);

            end;

            Error report:

            ORA-27369: job of type EXECUTABLE failed with exit code: 255

            ORA-06512: at "SYS.DBMS_ISCHED", line 185

            ORA-06512: at "SYS.DBMS_SCHEDULER", line 486

            ORA-06512: at line 2

            27369. 00000 -  "job of type EXECUTABLE failed with exit code: %s"

            *Cause:    A problem was encountered while running a job of type EXECUTABLE.

                       The cause of the actual problem is identified by the exit code.

            *Action:   Correct the cause of the exit code and reschedule the job.

             

             

            I have set the myfiles directory to 0777

            I have set all files in the myfiles directory to 0777

            so everyone should have access to execute scripts here.

             

            I have also set the extjob file to 0777

             

             

             

             

            any help appreciated.

            Thank you.

            Hi,

             

            Please check oracle support External Job fails with ORA-27369: job of type EXECUTABLE failed with exit code: 255 (Doc ID 1494843.1)

             

            Thank you

            • 3. Re: Error Trying execute shell from oracle
              708631

              If I do it the java way. Then to execute that shell script I just change this ?

               


              exec :y := RUN_CMD('/usr/bin/ls /tmp');

              to

              exec :y := RUN_CMD('/myfiles/transfer2.sh');



              • 4. Re: Error Trying execute shell from oracle
                Anar Godjaev

                yes , Please change  and test again..

                • 5. Re: Error Trying execute shell from oracle
                  708631

                  exec :y := RUN_CMD('/myfiles/transfer2.sh');

                   

                   

                  Bind Variable "y" is NOT DECLARED

                  anonymous block completed

                   

                  I am doing all of this in sql developer.

                  Didnt seem to work

                  • 6. Re: Error Trying execute shell from oracle
                    Anar Godjaev

                    708631 wrote:

                     

                    exec :y := RUN_CMD('/myfiles/transfer2.sh');

                     

                     

                    Bind Variable "y" is NOT DECLARED

                    anonymous block completed

                     

                    I am doing all of this in sql developer.

                    Didnt seem to work

                     

                    can you please run in plsql

                     

                     

                    SQL> exec Schemas_name.TEST_SHELL;

                    • 7. Re: Error Trying execute shell from oracle
                      708631

                      No that does not work.

                       

                       

                      Does anyone know a way to schedule the shell script to run in oracle ? as I said i have tried this

                       

                      BEGIN

                         DBMS_SCHEDULER.CREATE_JOB (

                            job_name          => 'TEST_SHELL',

                            JOB_TYPE          => 'EXECUTABLE',

                            job_action        => '/myfiles/transfer2.sh',

                            start_date        => SYSDATE,

                            REPEAT_INTERVAL   => 'FREQ=HOURLY;',

                            enabled           => FALSE,

                            comments          => 'shell script from Oracle'

                         );

                      end;

                      /

                       

                      and it just does not work.

                      • 8. Re: Error Trying execute shell from oracle
                        Anar Godjaev

                        708631 wrote:

                         

                        No that does not work.

                         

                         

                        Does anyone know a way to schedule the shell script to run in oracle ? as I said i have tried this

                         

                        BEGIN

                           DBMS_SCHEDULER.CREATE_JOB (

                              job_name          => 'TEST_SHELL',

                              JOB_TYPE          => 'EXECUTABLE',

                              job_action        => '/myfiles/transfer2.sh',

                              start_date        => SYSDATE,

                              REPEAT_INTERVAL   => 'FREQ=HOURLY;',

                              enabled           => FALSE,

                              comments          => 'shell script from Oracle'

                           );

                        end;

                        /

                         

                        and it just does not work.

                         

                        Can you please cheek '/myfiles/transfer2.sh' execute permission.?

                         

                        please connect to OS system root user , and run this below command

                         

                        chmod +x /myfiles/transfer2.sh

                        • 9. Re: Error Trying execute shell from oracle
                          DK2010

                          Hi,

                          I checked at my End and its working perfect for me

                           

                          SQL> 

                          BEGIN

                             DBMS_SCHEDULER.CREATE_JOB (

                                job_name          => 'TEST_SHELL2',

                                JOB_TYPE          => 'EXECUTABLE',

                                job_action        => '/home/oracle/MYSHELL/a.sh',

                                start_date        => SYSDATE,

                                REPEAT_INTERVAL   => 'FREQ=HOURLY;',

                                enabled           => FALSE,

                                comments          => 'shell script from Oracle'

                             );

                          end;

                          /SQL>   2    3    4    5    6    7    8    9   10   11   12 

                           

                           

                          PL/SQL procedure successfully completed.

                           

                           

                          SQL> BEGIN

                            dbms_scheduler.run_job('TEST_SHELL2',TRUE);

                          end;

                          /  2    3    4 

                           

                           

                          PL/SQL procedure successfully completed.

                           

                           

                          SQL> !ls -lrt a.sh

                          -rwxr-xr-x 1 oracle oracle 36 Sep 13 19:35 a.sh

                           

                           

                          SQL> !cat a.sh

                          #!/bin/bash

                          clear

                          echo "testing123"

                           

                           

                          HTH

                          • 10. Re: Error Trying execute shell from oracle
                            Anar Godjaev

                            DK2010 wrote:

                             

                            Hi,

                            I checked at my End and its working perfect for me

                             

                            SQL>

                            BEGIN

                               DBMS_SCHEDULER.CREATE_JOB (

                                  job_name          => 'TEST_SHELL2',

                                  JOB_TYPE          => 'EXECUTABLE',

                                  job_action        => '/home/oracle/MYSHELL/a.sh',

                                  start_date        => SYSDATE,

                                  REPEAT_INTERVAL   => 'FREQ=HOURLY;',

                                  enabled           => FALSE,

                                  comments          => 'shell script from Oracle'

                               );

                            end;

                            /SQL>   2    3    4    5    6    7    8    9   10   11   12

                             

                             

                            PL/SQL procedure successfully completed.

                             

                             

                            SQL> BEGIN

                              dbms_scheduler.run_job('TEST_SHELL2',TRUE);

                            end;

                            /  2    3    4

                             

                             

                            PL/SQL procedure successfully completed.

                             

                             

                            SQL> !ls -lrt a.sh

                            -rwxr-xr-x 1 oracle oracle 36 Sep 13 19:35 a.sh

                             

                             

                            SQL> !cat a.sh

                            #!/bin/bash

                            clear

                            echo "testing123"

                             

                             

                            HTH

                            I know exactly It is execute permission problem.....

                            • 11. Re: Error Trying execute shell from oracle
                              708631

                              I have put the shell script in the oracle directory and tried it. Still doesnt work same error.

                               

                              execute permission on what ? I have made the shell script 0777.

                              • 12. Re: Error Trying execute shell from oracle
                                708631

                                Ive made everything 0777 permissions. Still same error

                                 

                                Error starting at line 37 in command:

                                BEGIN

                                  dbms_scheduler.run_job('TEST_SHELL',TRUE);

                                end;

                                Error report:

                                ORA-27369: job of type EXECUTABLE failed with exit code: 255

                                ORA-06512: at "SYS.DBMS_ISCHED", line 185

                                ORA-06512: at "SYS.DBMS_SCHEDULER", line 486

                                ORA-06512: at line 2

                                27369. 00000 -  "job of type EXECUTABLE failed with exit code: %s"

                                *Cause:    A problem was encountered while running a job of type EXECUTABLE.

                                           The cause of the actual problem is identified by the exit code.

                                *Action:   Correct the cause of the exit code and reschedule the job.

                                • 13. Re: Error Trying execute shell from oracle
                                  708631

                                  In all_scheduler_job_run_details im getting

                                   

                                  "ORA-27369: job of type EXECUTABLE failed with exit code: 255

                                  STANDARD_ERROR="execve: No such file or directory""

                                  • 14. Re: Error Trying execute shell from oracle
                                    Anar Godjaev

                                    Hi,

                                     

                                    The error you are seeing is because the job is failing. If you are on 11g, try using a credential to run the job or check the post below for a guide to running jobs on 10g

                                     

                                    Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi

                                     

                                    For example:

                                     

                                    BEGIN

                                    sys.dbms_scheduler.create_job(

                                    job_name => '"SYS"."MYJOB1"',

                                    job_type => 'EXECUTABLE',

                                    job_action => '/usr/bin/bash',

                                    start_date => systimestamp at time zone 'Asia/Baku',

                                    job_class => 'DEFAULT_JOB_CLASS',

                                    auto_drop => FALSE,

                                    number_of_arguments => 1,

                                    enabled => FALSE);

                                    sys.dbms_scheduler.set_job_argument_value( job_name => '"SYS"."MYJOB1"', argument_position => 1, argument_value => '/u01/app/ora10g/test1.sh');

                                    sys.dbms_scheduler.enable( '"SYS"."MYJOB1"' );

                                    END;

                                    1 2 Previous Next