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

Ramin Hashimzadeh

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

Ramin Hashimzadeh

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

----

Ramin Hashimzade

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.

Ramin Hashimzadeh

Hi

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

----

Ramin Hashimzade

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?

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

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;

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.

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                                                                                                
\AMIN                                                                      
*.txt                                                                        
CRED_FILE_WATCHER




SQL> 

----

Ramin Hashimzade