Forum Stats

  • 3,827,532 Users
  • 2,260,790 Discussions
  • 7,897,294 Comments

Discussions

FILE WATCHER - JOB IS NOT RUNNING

Ramin Hashimzadeh
Ramin Hashimzadeh Member Posts: 1,631 Silver Trophy
edited Mar 14, 2014 4:24AM in Scheduler

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

Tagged:
«1

Answers

  • onkar.nath
    onkar.nath Member Posts: 733 Silver Badge
    edited Mar 7, 2014 6:21AM

    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

  • Ramin Hashimzadeh
    Ramin Hashimzadeh Member Posts: 1,631 Silver Trophy

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

    ----

    Ramin Hashimzade

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    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.

  • Ramin Hashimzadeh
    Ramin Hashimzadeh Member Posts: 1,631 Silver Trophy

    Hi

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

    ----

    Ramin Hashimzade

  • GregV
    GregV Member Posts: 3,085 Gold Crown

    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?

  • Ramin Hashimzadeh
    Ramin Hashimzadeh Member Posts: 1,631 Silver Trophy
    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

  • GregV
    GregV Member Posts: 3,085 Gold Crown

    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;

  • GregV
    GregV Member Posts: 3,085 Gold Crown

    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.

  • Ramin Hashimzadeh
    Ramin Hashimzadeh Member Posts: 1,631 Silver Trophy
    edited Mar 10, 2014 2:21AM
    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

This discussion has been closed.