set echo on
exec dbms_scheduler.drop_credential( 'watch_credential' );
drop table files;

clear screen
begin
  dbms_scheduler.create_credential(
     credential_name => 'watch_credential',
     username        => 'tkyte',
     password        => 'o8isgr8');
end;
/
pause

clear screen
create or replace directory MY_FILES as '/home/tkyte/files'
/
create table files 
(
   file_name varchar2(100), 
   loaded timestamp, 
   contents  clob
);
pause

clear screen
create or replace procedure process_files 
(p_payload in sys.scheduler_filewatcher_result) 
is 
    l_clob clob;
    l_bfile bfile;
begin
    insert into files 
    (loaded, file_name, contents )
    values (p_payload.file_timestamp,
     p_payload.directory_path || '/' || p_payload.actual_file_name,
     empty_clob() 
    ) returning contents into l_clob;

    l_bfile := bfilename( 'MY_FILES', p_payload.actual_file_name );
    dbms_lob.fileopen( l_bfile );
    dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength(l_bfile) );
    dbms_lob.fileclose( l_bfile );
end;
/
pause


clear screen
begin
  dbms_scheduler.create_program(
    program_name        => 'file_watcher',
    program_type        => 'stored_procedure',
    program_action      => 'Process_Files',
    number_of_arguments => 1,
    enabled             => false);
  dbms_scheduler.define_metadata_argument(
    program_name        => 'file_watcher',
    metadata_attribute  => 'event_message',
    argument_position   => 1);
  dbms_scheduler.enable('file_watcher');
end;
/


begin
  dbms_scheduler.create_file_watcher(
    file_watcher_name => 'my_file_watcher',
    directory_path    => '/home/tkyte/files', 
    file_name         => '*',
    credential_name   => 'watch_credential',
    destination       => null,
    enabled           => false);
end;
/
 
begin
  dbms_scheduler.create_job(
    job_name        => 'my_file_job',
    program_name    => 'file_watcher',
    event_condition => 'tab.user_data.file_size > 10',
    queue_spec      => 'my_file_watcher',
    auto_drop       => false,
    enabled         => false);
  dbms_scheduler.set_attribute('my_file_job','parallel_instances',true);
end;
/
 
exec dbms_scheduler.enable('my_file_watcher,my_file_job');
pause

exec dbms_scheduler.disable('my_file_watcher,my_file_job');
exec dbms_scheduler.drop_job('my_file_job');
exec dbms_scheduler.drop_file_watcher( 'my_file_watcher' );
exec dbms_scheduler.disable( 'file_watcher' );
exec dbms_scheduler.drop_program( 'file_watcher' );
exec dbms_scheduler.drop_file_watcher( 'my_file_watcher' );
