14 Replies Latest reply on Mar 14, 2014 8:24 AM by GregV

    FILE WATCHER - JOB IS NOT RUNNING

    Ramin Hashimzadeh

      Hi All.

      I have below problem with dbms_scheduler,  file watcher job.

      I am creating file watcher like this :

      BEGIN

        dbms_scheduler.create_credential(credential_name => 'cred_file_watcher',

                                         username        => 'test',

                                         password        => 'test');

                       

        dbms_scheduler.create_program(program_name        => 'prog_file_watcher',

                                      program_type        => 'stored_procedure',

                                      program_action      => 'rr_app_am.am_pack_ott_box.load_ott_boxes',

                                      number_of_arguments => 1,

                                      enabled             => FALSE);     

       

       

        dbms_scheduler.define_metadata_argument(program_name       => 'prog_file_watcher',

                                                metadata_attribute => 'event_message',

                                                argument_position  => 1);

                          

                          

        dbms_scheduler.create_file_watcher(file_watcher_name => 'file_watcher',

                                           directory_path    => 'D:\AMIN',

                                           file_name         => '*.txt',

                                           credential_name   => 'cred_file_watcher',

                                           destination       => NULL,

                                           enabled           => FALSE);

       

       

        dbms_scheduler.create_job(job_name        => 'job_file_watcher',

                                  program_name    => 'prog_file_watcher',                           

                                  event_condition => NULL,

                                  queue_spec      => 'file_watcher',

                                  auto_drop       => FALSE,

                                  enabled         => FALSE);

                                

        dbms_scheduler.enable('file_watcher, prog_file_watcher, job_file_watcher');

      END;

       

      and JOB is not running. When i am trying to run it manually , ORACLE return error

       

      SQL> begin

        2   dbms_scheduler.run_job('JOB_FILE_WATCHER');

        3  end;

        4 

        5  /

      begin

      dbms_scheduler.run_job('JOB_FILE_WATCHER');

      end;

      ORA-00942: table or view does not exist

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

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

      ORA-06512: at line 2


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

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

       

      I looked this threads

      file watcher is not working in 11g R2

      Stuck with filewatcher

      but can not find solution. Can you help me.

       

      Thanks!

       

      ----

      Ramin Hashimzade

        • 2. Re: FILE WATCHER - JOB IS NOT RUNNING
          onkar.nath

          run the called proc i.e. "prog_file_watcher" in the job manually and you will find the issue. The table referred by this proc seems to be inaccessible to this proc.


          Onkar

          • 3. Re: FILE WATCHER - JOB IS NOT RUNNING
            Ramin Hashimzadeh

            No problem not on this. I run it and it is executed successufully.

             

            ----

            Ramin Hashimzade

            • 4. Re: FILE WATCHER - JOB IS NOT RUNNING
              Sven W.

              two ideas:

               

              1) privs problem

              Are the tables that are accessed in a different schema?

              Then grant the needed privs to your schema directly, not through a role.

               

              You can test it in sql*plus / sql developer by

              SET ROLE NONE

               

              then execute your procedure.

               

              2) Object name not recognized

              Also I noticed that the job is spelled in lower case but you called it in upper case. Try to create all the programs and job names in uppercase.

              • 5. Re: FILE WATCHER - JOB IS NOT RUNNING
                Ramin Hashimzadeh

                Hi

                Before create new thread i check it. I create simple procedure withour any code.

                 

                ----

                Ramin Hashimzade

                • 6. Re: FILE WATCHER - JOB IS NOT RUNNING
                  GregV

                  Hi Ramin,

                   

                  Since there's no grant execute on the credential, I'm assuming the creator of the job is also the one who runs it.

                  So if you run the rr_app_am.am_pack_ott_box.load_ott_boxes procedure with this user, it executes without error?

                  • 7. Re: FILE WATCHER - JOB IS NOT RUNNING
                    Ramin Hashimzadeh

                    GregV wrote:

                     

                    Hi Ramin,

                     

                    Since there's no grant execute on the credential, I'm assuming the creator of the job is also the one who runs it.

                    So if you run the rr_app_am.am_pack_ott_box.load_ott_boxes procedure with this user, it executes without error?

                    YES

                    here is execution :

                     

                    SQL> grant execute on rr_app_am.am_pack_ott_box to rr_app_resource;

                    Grant succeeded

                     

                    SQL>

                    on rr_app_resource

                     

                    SQL>

                    SQL> declare

                      2    -- Non-scalar parameters require additional processing

                      3    p_payload sys.scheduler_filewatcher_result;

                      4  begin

                      5    -- Call the procedure

                      6    rr_app_am.am_pack_ott_box.load_ott_boxes(p_payload => p_payload);

                      7  end;

                      8  /

                    PL/SQL procedure successfully completed

                     

                    SQL>

                    ---AND HERE IS MANUALLY RUNNING JOB WHICH IS CALLING PROCE rr_app_am.am_pack_ott_box.load_ott_boxes

                    SQL> /

                    begin

                    dbms_scheduler.run_job('job_file_watcher');

                    end;

                    ORA-00942: table or view does not exist

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

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

                    ORA-06512: at line 2

                     

                    SQL>

                     

                    ----

                    Ramin Hashimzade

                    • 8. Re: FILE WATCHER - JOB IS NOT RUNNING
                      GregV

                      With user  rr_app_resource connected, can you post the result of the following queries:

                       

                      SELECT * FROM user_scheduler_credentials;

                       

                      SELECT file_watcher_name, destination, directory_path, file_name, credential_name

                      FROM user_scheduler_file_watchers;

                      • 9. Re: FILE WATCHER - JOB IS NOT RUNNING
                        GregV

                        I've tried to reproduce the issue but I'm not getting the same error as you:

                        exec dbms_scheduler.run_job('job_file_watcher');

                         

                        begin dbms_scheduler.run_job('job_file_watcher'); end;

                         

                        ORA-27473: l'argument 1 n'existe pas

                        ORA-06512: à "SYS.DBMS_ISCHED", ligne 185

                        ORA-06512: à "SYS.DBMS_SCHEDULER", ligne 486

                        ORA-06512: à ligne 2

                         

                        But with hindsight, I believe it's kind of normal, because we're not supposed to run the file watcher job since its very purpose is to run automatically on regular intervals (10 min default) to look for file arrival.

                        To test it, you need to place files in the folder your file watcher points to.

                        • 10. Re: FILE WATCHER - JOB IS NOT RUNNING
                          Ramin Hashimzadeh

                          GregV wrote:

                           

                          With user  rr_app_resource connected, can you post the result of the following queries:

                           

                          SELECT * FROM user_scheduler_credentials;

                           

                          SELECT file_watcher_name, destination, directory_path, file_name, credential_name

                          FROM user_scheduler_file_watchers;

                           

                          SQL> SELECT * FROM user_scheduler_credentials;

                          CREDENTIAL_NAME                USERNAME                                                         DATABASE_ROLE WINDOWS_DOMAIN                 COMMENTS

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

                          CRED_FILE_WATCHER              appuser                                                                                                      

                           

                          SQL>

                           

                          SQL> SELECT file_watcher_name, destination, directory_path, file_name, credential_name

                          FROM user_scheduler_file_watchers;

                           

                          FILE_WATCHER_NAME          DESTINATION                                                                  DIRECTORY_PATH                                                               FILE_NAME                                                                    CREDENTIAL_NAME

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

                          FILE_WATCHER                                                                                                D:\AMIN                                                                      *.txt                                                                        CRED_FILE_WATCHER

                           

                          SQL>

                           

                           

                          ----

                          Ramin Hashimzade

                          • 11. Re: FILE WATCHER - JOB IS NOT RUNNING
                            Ramin Hashimzadeh

                            GregV wrote:

                             

                            I've tried to reproduce the issue but I'm not getting the same error as you:

                            exec dbms_scheduler.run_job('job_file_watcher');

                             

                            begin dbms_scheduler.run_job('job_file_watcher'); end;

                             

                            ORA-27473: l'argument 1 n'existe pas

                            ORA-06512: à "SYS.DBMS_ISCHED", ligne 185

                            ORA-06512: à "SYS.DBMS_SCHEDULER", ligne 486

                            ORA-06512: à ligne 2

                             

                            But with hindsight, I believe it's kind of normal, because we're not supposed to run the file watcher job since its very purpose is to run automatically on regular intervals (10 min default) to look for file arrival.

                            To test it, you need to place files in the folder your file watcher points to.

                            Problem is that it is not running automatically  tooş Bercause of this i have tried to run it manually and it have to run. İ think this is BUG. Today i will report it to support

                             

                             

                            -----

                            Ramin Hashimzade

                            • 12. Re: FILE WATCHER - JOB IS NOT RUNNING
                              GregV

                              Hi Ramin,

                               

                              Right. Let us know the outcome of the SR.

                               

                              Thanks

                              • 13. Re: FILE WATCHER - JOB IS NOT RUNNING
                                Ramin Hashimzadeh

                                Support OPEN new Bug 18395147Please note that this BUG for WindowsOS  in UX working fine


                                ----

                                Ramin Hashimzade

                                 

                                • 14. Re: FILE WATCHER - JOB IS NOT RUNNING
                                  GregV

                                  Hi Ramin,

                                   

                                  Thanks for letting us know. I can indeed see the bug in MOS.