9 Replies Latest reply: Apr 6, 2013 9:00 PM by user1066901 RSS

    External Job

    811414
      I am doing a POC and and attempting to to execute the following external job on a windows installation of oracle
      I am on 11g r2
      BEGIN
      -- Windows
      DBMS_SCHEDULER.create_job(
      job_name => 'win_command_job',
      job_type => 'EXECUTABLE',
      number_of_arguments => 3,
      job_action => 'C:\windows\system32\cmd.exe',
      auto_drop => FALSE,
      enabled => FALSE);

      DBMS_SCHEDULER.set_job_argument_value('win_command_job',1,'/c');
      DBMS_SCHEDULER.set_job_argument_value('win_command_job',2,'mkdir');
      DBMS_SCHEDULER.set_job_argument_value('win_command_job',3,'C:\TEMP\test_dir');
      DBMS_SCHEDULER.set_attribute('win_command_job', 'credential_name', 'MY_CRED');
      DBMS_SCHEDULER.enable('win_command_job');
      END;
      /

      I have setup the credentials: exec dbms_scheduler.create_credential('my_cred','oracle','Test1234'); in the current schema

      I am getting the following error
      "EXTERNAL_LOG_ID="job_75255_1263",
      ORA-27369: job of type EXECUTABLE failed with exit code: -10737409
      "
      Unfortunately, Windows error codes do not list this error...

      I did a test by purposely not entering the correct password and when I did that I received the following

      "EXTERNAL_LOG_ID="job_75244_1252",
      ORA-27369: job of type EXECUTABLE failed with exit code: The storage control blocks were destroyed.
      STANDARD_ERROR="Launching external job failed: Invalid username or password""

      I have the OracleJobSchedulerORCL running... though when I stop it I get the same error..
        • 1. Re: External Job
          978204
          I have never run an external job on a windows box - only on a UNIX server. I struggled quite a bit to get the syntax of my command correct. I would begin with the simplest command possible and get it working first. Then you will know it is not a proplem with the Credential.
          • 2. Re: External Job
            811414
            BEGIN
            -- Windows
            DBMS_SCHEDULER.create_job(
            job_name => 'win_command_job',
            job_type => 'EXECUTABLE',
            job_action => 'C:\windows\system32\cmd.exe',
            auto_drop => FALSE,
            enabled => FALSE);
            DBMS_SCHEDULER.set_attribute('win_command_job', 'credential_name', 'MY_CRED');
            DBMS_SCHEDULER.enable('win_command_job');
            END;
            /

            Same result running this...
            • 3. Re: External Job
              811414
              BEGIN
              -- Windows
              DBMS_SCHEDULER.create_job(
              job_name => 'win_command_job',
              job_type => 'EXECUTABLE',
              job_action => 'C:\windows\system32\cmd.exe',
              auto_drop => FALSE,
              enabled => FALSE);

              DBMS_SCHEDULER.set_attribute('win_command_job', 'credential_name', 'MY_CRED');
              DBMS_SCHEDULER.enable('win_command_job');
              END;
              /

              Same result
              • 4. Re: External Job
                811414
                I tried a different user on my local machine and behold it worked... However, I attempted to run it again, and I am back to the same issue....
                • 5. Re: External Job
                  811414
                  not answered
                  • 6. Re: External Job
                    811414
                    I created a new user and reran the job and it succeeded. I reran the job again with a creating different directory and it failed again

                    "EXTERNAL_LOG_ID="job_75457_1296",
                    ORA-27369: job of type EXECUTABLE failed with exit code: -10737409
                    "

                    So it seems as though something is going on with the credentials
                    • 7. Re: External Job
                      Mike Kutz
                      Did you remove the directory before you re-ran it?
                      If you don't, you ARE going to get an error.

                      Also, try calling a batch file instead.
                      Last thing you want to do is to create a series of jobs that 'remote call' one command at a time.
                      • 8. Re: External Job
                        user1066901
                        I run on 11gR2 Windows 7.

                        I came across this very same thing.

                        I did try to run your code using my credentials. The result was no error and no directory was created either.

                        My conclusion is that 'create credential' is not needed while running SQL Plus since it's already running on your local machine and outside the database. If your script will not run without under your current logon credentials then try running AS SYSDBA. Also, double check your grants for user. I did and still nothing.

                        The script below ran on both logins for me: user/login and user/login as sysdba.

                        I also changed parameter auto_drop to TRUE so the job would drop from the scheduler when the job has completed.

                        Although not needed I deleted the directory that the script produced between login runs, one with normal login and another with AS DYSDBA login, to make sure the code worked in both runs without using credentials.

                        conn user/login
                        BEGIN
                        -- Windows
                        DBMS_SCHEDULER.create_job(
                        job_name => 'win_command_job',
                        job_type => 'EXECUTABLE',
                        number_of_arguments => 4,
                        job_action => 'C:\windows\system32\cmd.exe',
                        auto_drop => TRUE,
                        enabled => FALSE);
                        DBMS_SCHEDULER.set_job_argument_value('win_command_job',1,'/q');
                        DBMS_SCHEDULER.set_job_argument_value('win_command_job',2,'/c');
                        DBMS_SCHEDULER.set_job_argument_value('win_command_job',3,'mkdir');
                        DBMS_SCHEDULER.set_job_argument_value('win_command_job',4,'C:\TEMP\test_dir');
                        DBMS_SCHEDULER.enable('win_command_job');
                        END;
                        /

                        I'm currently working on using credentials within a package which seems to working somewhat. Again, credentials do not seem to be needed while using system commands like copy, move, erase. But when it comes to an executable that I created in FoxPro (test.exe) credentials are needed and currently nothing is being created from the test.exe like it should be. Again, no errors to be found.

                        Sorry this doesn't solve your issue. just know there are others working through this very same issue.

                        ----------

                        A few updates...

                        It does matter if the directory is previously existing. I checked my logs and I did get an error if the directory is preexisting: ORA-27369: job of type EXECABLE failed with exit code: Incorrect function. STANARD_ERROR="A subdirectory or file C:\TEMP\test_dir already exists."

                        When trying to use the credential statements I got an error in my logs: ORA-27369: job type executable failed with exit code: -10737418.

                        Edited by: user1066901 on Apr 6, 2013 6:59 PM
                        • 9. Re: External Job
                          user1066901
                          ---

                          Edited by: user1066901 on Apr 6, 2013 6:59 PM