5 Replies Latest reply: Oct 2, 2013 5:11 PM by Barbara Boehmer RSS

    file watcher is not working in 11g R2

    Nag Aswadhati

      Version details

      -----------------------

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      PL/SQL Release 11.2.0.3.0 - Production

      "CORE 11.2.0.3.0 Production"

      TNS for Linux: Version 11.2.0.3.0 - Production

      NLSRTL Version 11.2.0.3.0 - Production

       

      Below is the code which I have tried with file watcher. The job is not running after file arrival.

      Please suggest me.

       

      BEGIN

        DBMS_SCHEDULER.create_file_watcher(

          file_watcher_name => 'test_file_watcher',

          directory_path    => '/home/upncommon/workfile',

          file_name         => '*tempfl',

          credential_name   => 'UPNCOMMON_CREDENTIAL',

          destination       => NULL,

          enabled           => FALSE);

      END;

      /

      BEGIN

        DBMS_SCHEDULER.create_program(

          program_name        => 'file_watcher_test_prog',

          program_type        => 'stored_procedure',

          program_action      => 'INS_EMP',

          number_of_arguments => 1,

          enabled             => FALSE);

      END;

      /

      BEGIN

        DBMS_SCHEDULER.define_metadata_argument(

          program_name       => 'file_watcher_test_prog',

          metadata_attribute => 'event_message',

          argument_position  => 1);

      END;

      /

      BEGIN

        DBMS_SCHEDULER.create_job(

          job_name        => 'file_watcher_test_job',

          program_name    => 'file_watcher_test_prog',

          event_condition => NULL,

          queue_spec      => 'test_file_watcher',

          auto_drop       => FALSE,

          enabled         => FALSE);

      END;

      /

      BEGIN

        DBMS_SCHEDULER.set_attribute('file_watcher_test_job','parallel_instances',TRUE);

      END;

      /

      EXEC DBMS_SCHEDULER.enable('test_file_watcher');

      EXEC DBMS_SCHEDULER.enable('file_watcher_test_prog');

      EXEC DBMS_SCHEDULER.enable('file_watcher_test_job');

        • 1. Re: file watcher is not working in 11g R2
          sybrand_b

          according to the demo here http://www.morganslibrary.org/reference/pkgs/dbms_scheduler.html

          a DIRECTORY object is required.

          You have a hardcoded path.

          Also in Oracle about everything is logged, you should have consulted log files and posted them here first.

          Just stating it is not working doesn't tell much.

           

          -------------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: file watcher is not working in 11g R2
            Nag Aswadhati

            I tried with directory object but unfortunately got below error when I run the job manually.

             

            Error starting at line 20 in command:

            exec dbms_scheduler.run_job('UW_FILE_JOB')

            Error report:

            ORA-27473: argument 1 does not exist

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

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

            ORA-06512: at line 1

            27473. 00000 -  "argument %s does not exist"

            *Cause:    An argument which was specified does not exist.

            *Action:   Reissue the command using an argument name defined by the program

                       or using a valid argument position.

             

            Below is the code

            -------------------------------------------------------

             

            EXEC dbms_scheduler.create_program('file_watcher', 'stored_procedure', 'EMP_INS', 1);

             

             

            exec dbms_scheduler.define_metadata_argument('file_watcher', 'EVENT_MESSAGE', 1);

             

             

            exec dbms_scheduler.enable('file_watcher');

             

             

            exec dbms_scheduler.create_file_watcher('UW_FWatch', 'UPNCOMMON_DIR', 'TEMPFL', 'UPNCOMMON_CREDENTIAL');

             

             

            BEGIN

            dbms_scheduler.create_job('UW_File_Job', 'file_watcher',

            event_condition => 'tab.user_data.file_size > 10',

            queue_spec => 'UW_FWatch', enabled => TRUE, auto_drop => FALSE);

            END;

            /

             

             

            exec dbms_scheduler.set_attribute('UW_File_Job', 'parallel_instances', TRUE);

             

             

            exec dbms_scheduler.enable('file_watcher');

             

             

            exec dbms_scheduler.run_job('UW_FILE_JOB');

             

             

            BEGIN

            dbms_scheduler.set_attribute('UW_File_Job', 'repeat_interval', 'FREQ=MINUTELY;INTERVAL=2');

            END;

            /

            • 3. Re: file watcher is not working in 11g R2
              DK2010

              Hi,

               

               

               

              What about the other execution was it successful if yes, then you can try to change the order

               

               

               

              BEGIN

              dbms_scheduler.set_attribute('UW_File_Job', 'repeat_interval', 'FREQ=MINUTELY;INTERVAL=2');

              END;

              /

              exec dbms_scheduler.run_job('UW_FILE_JOB');

               

               

              HTH

              • 4. Re: file watcher is not working in 11g R2
                Nag Aswadhati

                Thanks for your answer. It is running manually with this exec dbms_scheduler.run_job('UW_FILE_JOB');

                 

                I want to run the job automatically when the file arrives into the path.

                 

                 

                The below job is not running automatically.

                 

                BEGIN

                    SYS.DBMS_SCHEDULER.CREATE_JOB (

                            job_name => '"UPN_COMMON"."EMPJOBS"',

                            job_type => 'STORED_PROCEDURE',

                            job_action => '"UPN_COMMON"."INS_EMP"',

                            number_of_arguments => 0,

                            start_date => TO_TIMESTAMP_TZ('2013-09-30 08:57:20 America/New_York','YYYY-MM-DD HH24.MI.SS TZR'),

                            event_condition => '(1=1)',

                            queue_spec => '"UPN_COMMON"."FILE_WATCHER"',

                            end_date => TO_TIMESTAMP_TZ('2013-09-30 10:52:20 America/New_York','YYYY-MM-DD HH24.MI.SS TZR'),

                            job_class => '"SYS"."DEFAULT_JOB_CLASS"',

                            enabled => FALSE,

                            auto_drop => FALSE,

                            comments => 'TESTING A PROCEDURE',

                            credential_name => NULL,

                            destination_name => NULL);

                 

                 

                        

                    

                 

                    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

                             name => '"UPN_COMMON"."EMPJOBS"',

                             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

                     

                 

                    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

                             name => '"UPN_COMMON"."EMPJOBS"',

                             attribute => 'max_run_duration', value => INTERVAL '1' HOUR);

                    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

                             name => '"UPN_COMMON"."EMPJOBS"',

                             attribute => 'schedule_limit', value => INTERVAL '1' HOUR);   

                    SYS.DBMS_SCHEDULER.enable(

                             name => '"UPN_COMMON"."EMPJOBS"');

                END;

                /

                 

                Please suggest me.

                • 5. Re: file watcher is not working in 11g R2
                  Barbara Boehmer

                  I didn't see this thread until I had already posted a response to your other thread, so please see my reply there:

                   

                  Stuck with filewatcher