8 Replies Latest reply: Nov 7, 2012 9:57 AM by 971785 RSS

    Problem running DBMS_SCHEDULER external job

    971785
      I've recently stated working with Oracle DBMS_SCHEDULER and it's always worked with jobs that run inside the database. Now I need to run an external job and could manage to get it working at one server with the following configuration:

      BEGIN
      SYS.DBMS_SCHEDULER.CREATE_JOB
      (
      job_name => 'JOBNAME'
      ,start_date => TO_TIMESTAMP_TZ('2012/11/01 02:00:00.000000 -03:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=02; BYMINUTE=00; BYSECOND=00'
      ,end_date => NULL
      ,job_class => 'DEFAULT_JOB_CLASS'
      ,job_type => 'EXECUTABLE'
      ,job_action => 'D:\FOLDER1\FOLDER2\BatchFile.bat'
      ,comments => NULL
      );
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name => 'JOBNAME'
      ,attribute => 'RESTARTABLE'
      ,value => TRUE);
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name => 'JOBNAME'
      ,attribute => 'LOGGING_LEVEL'
      ,value => SYS.DBMS_SCHEDULER.LOGGING_FULL);
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
      ( name => 'JOBNAME'
      ,attribute => 'MAX_FAILURES');
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
      ( name => 'JOBNAME'
      ,attribute => 'MAX_RUNS');
      BEGIN
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name => 'JOBNAME'
      ,attribute => 'STOP_ON_WINDOW_CLOSE'
      ,value => FALSE);
      EXCEPTION
      WHEN OTHERS THEN
      NULL;
      END;
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name => 'JOBNAME'
      ,attribute => 'JOB_PRIORITY'
      ,value => 1);
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
      ( name => 'JOBNAME'
      ,attribute => 'SCHEDULE_LIMIT');
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name => 'JOBNAME'
      ,attribute => 'AUTO_DROP'
      ,value => FALSE);
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name => 'JOBNAME'
      ,attribute => 'RAISE_EVENTS'
      ,value => SYS.DBMS_SCHEDULER.JOB_STARTED + SYS.DBMS_SCHEDULER.JOB_SUCCEEDED + SYS.DBMS_SCHEDULER.JOB_FAILED + SYS.DBMS_SCHEDULER.JOB_BROKEN + SYS.DBMS_SCHEDULER.JOB_COMPLETED + SYS.DBMS_SCHEDULER.JOB_STOPPED + SYS.DBMS_SCHEDULER.JOB_SCH_LIM_REACHED + SYS.DBMS_SCHEDULER.JOB_DISABLED + SYS.DBMS_SCHEDULER.JOB_CHAIN_STALLED);

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

      The problem is: every other server I try to schedule it, the job just doesn't work appropriately. The job does run, but doens't seem to get permission on Windows environment to do what it is supposed to.
      --
      Doen anyone know anything that should be done to get this working?
        • 1. Re: Problem running DBMS_SCHEDULER external job
          896548
          Hi Karla

          below is link

          Re: what is wrong? dbms_scheduler

          last post.

          Most important, in windows must be: job_action => 'C:\windows\system32\cmd.exe',
          all scheduler scripts must be triggered by windows command line with parameter /c


          Hope to help you.

          Rgds
          • 2. Re: Problem running DBMS_SCHEDULER external job
            971785
            Hello, Krzysztof. Thank you for the reply.

            Just like you, I've tried lots of tutorials and read several posts made by others, but I just can't get it right.
            --
            Well... I had already tried setting the job action as a call to CMD, but I was using '/q' as argument number 1 and '/c' as argument number 2. Now I'm trying it with no '/q' argument but things are still the same. The one thing I find really odd about it, is that I have the very same job working just fine at one server, where I run the batch as the job action. All the other places I created this job it does not work at all!
            --
            To make a long story short, right now this is the scenario:
            The job does run;
            The batch file is executed;
            The .exe file appears at the task manager list os processes, but doesn't do what it is supposed to;
            On the other hand, if I manually run the batch through Windows Explorer, it works just fine.
            --
            Any other ideas?
            • 3. Re: Problem running DBMS_SCHEDULER external job
              896548
              Carla,

              could you present short piece of yr script from batch file, please?

              I think that you are trying run script by some program. As you can see in my above link, there is:
              C:\oraclexe\app\oracle\product....\bin\rman target sys/password@XE ...
              I passed full path to program which supposed to make code. Please check paths in batch file.

              Rgds
              • 4. Re: Problem running DBMS_SCHEDULER external job
                971785
                Hi, Krzysztof.

                I read it, but to be honest I didn't quite understand it...

                Me batch file is very simple and short:
                ----------
                CD\
                D:
                CD FOLDER1\FOLDER2\
                Program.exe 1
                Program.exe 2
                ----------
                The '1' and '2' parameters are codes that the .exe receives in order to generate a few reports. In other words, the batch runs the executable twice, sending different parameters.

                Again, through Windows Explorer it works just fine...
                • 5. Re: Problem running DBMS_SCHEDULER external job
                  896548
                  Karla

                  try:
                  D:\FOLDER1\FOLDER2\Program.exe 1 
                  first with only above and after (if everything will be good) add the second line.

                  good luck

                  Rgds
                  • 6. Re: Problem running DBMS_SCHEDULER external job
                    971785
                    I've already tried that too... :-(

                    Anyways... thanks for trying to help. I'll keep seeking for a solution and post here whenever I find it.
                    • 7. Re: Problem running DBMS_SCHEDULER external job
                      896548
                      it's strange, very strange

                      last question: did you start OracleJobScheduler service in windows?

                      I mean:
                      in windows command line run services.msc

                      find OracleJobScheduler service and check the status


                      Rgds
                      • 8. Re: Problem running DBMS_SCHEDULER external job
                        971785
                        Yes, I feel the very same way... There's got to be something I'm missing, but I believe it's related to the OS.

                        And the service is on and working. I have several different internal jobs running on all these servers.