I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working. I am trying to figure out what I can check to troubleshoot why this isn't working. After scaling back the code to a bare minimum, here are somethings I have already done.
- Verify that files are being created in the folder monitored - They are.
- Verify that the procedure can be run by itself - It can.
- Verify that the File Watcher shows up in DBA_Scheduler_File_Watchers and is enabled. - It does and is.
- Verify that DBA_Scheduler_Job_Run_Details shows a successful execution - It does NOT show any entries for this job.
- Check the alert log - Nothing interesting.
- Check for other trace files - I am getting trace files mattching
*_j000_*.trc, but they just have this:FILE_TRANSFER error is:with nothing after it.
Could someone explain what I am doing wrong or give me a way to troubleshoot this further? Here is my test code:
--Create Credential.
BEGIN
sys.dbms_scheduler.create_credential(
username => 'oracle',
password => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
database_role => NULL,
windows_domain => NULL,
comments => NULL,
credential_name => 'TestCredential'
);
END;
/
CREATE TABLE FilesFound(FoundDate Date);
CREATE OR REPLACE PROCEDURE TestProcedure(iFileInfo In sys.scheduler_filewatcher_result) AS
BEGIN
INSERT INTO FilesFound VALUES (sysdate);
COMMIT;
END;
/
BEGIN
DBMS_SCHEDULER.create_program(
program_name => 'TestProgram',
program_type => 'stored_procedure',
program_action => 'TestProcedure',
number_of_arguments => 1,
enabled => False);
DBMS_SCHEDULER.define_metadata_argument(
program_name => 'TestProgram',
metadata_attribute => 'event_message',
argument_position => 1);
END;
/
BEGIN
dbms_scheduler.create_file_watcher(
file_watcher_name => 'TestFileWatcher',
directory_path => '/u01/test',
file_name => '*.*',
credential_name => 'TestCredential',
destination => NULL,
enabled => False);
END;
/
BEGIN
dbms_scheduler.create_job(
job_name => 'TestJob',
program_name => 'TestProgram',
event_condition => NULL,
queue_spec => 'TestFileWatcher',
auto_drop => False,
enabled => False);
--Enable Everything.
dbms_scheduler.enable('TestProgram, TestFileWatcher, TestJob');
end;
/
--Set a one minute check interval.
BEGIN
DBMS_SCHEDULER.set_attribute(
'file_watcher_schedule',
'repeat_interval',
'freq=minutely; interval=1');
END;
/
--Create a file.
DECLARE
vFile utl_file.file_type;
BEGIN
EXECUTE IMMEDIATE 'create or replace directory TESTDIRECTORY as ''/u01/test''';
vFile := utl_file.fopen('TESTDIRECTORY', 'TestFileWatcher.txt', 'w', NULL);
utl_file.put_line(vFile, 'File has arrived '||SYSTIMESTAMP, TRUE);
utl_file.fclose(vFile);
END;
/
--Delay to give time for file to appear.
BEGIN
DBMS_LOCK.SLEEP(120);
END;
/
--Check the table.
BEGIN
FOR vx IN (select count(*) ct from sys.FilesFound) LOOP
If (vx.ct = 0) Then
DBMS_Output.Put_Line('Failed - No File Watcher Activity.');
Else
DBMS_Output.Put_Line('Success - File Watcher Activity Found.');
End If;
END Loop;
END;
/
--Cleanup.
EXECUTE dbms_scheduler.drop_job(job_name => 'TestJob');
EXECUTE dbms_scheduler.drop_program(program_name => 'TestProgram');
EXECUTE dbms_scheduler.drop_file_watcher(file_watcher_name => 'TestFileWatcher');
EXECUTE DBMS_SCHEDULER.drop_credential(credential_name => 'TestCredential');
drop table FilesFound;
drop procedure TestProcedure;
drop directory TestDirectory;
FILE_TRANSFER error is:message in the*_j000_*.trclog files, aychin.wordpress.com/2014/08/21/… was very helpful for me.