创建file watcher

郑全

1.创建用户

   CREATE USER sztech IDENTIFIED BY sztech;
   GRANT CREATE SESSION TO sztech;
   GRANT CREATE PROCEDURE TO sztech;
   GRANT CREATE TABLE TO sztech;
   GRANT CREATE JOB TO sztech;
   GRANT CREATE ANY DIRECTORY TO sztech;
   GRANT CREATE EXTERNAL JOB TO sztech;
   ALTER USER sztech QUOTA UNLIMITED ON users;

2.创建目录
   CREATE OR REPLACE DIRECTORY blob_dir AS '/home/oracle/jpg';

3.创建一个凭证对象

SQL> BEGIN
       DBMS_SCHEDULER.create_credential (
          'oracle_credential',
          'oracle',
          'oracle');
    END;
    /

4.创建一张表存储处理过的表的内容

SQL> CREATE TABLE images
    (
       file_name      VARCHAR2 (100),
       file_size      NUMBER,
       file_content   BLOB,
       uploaded_on    TIMESTAMP
    );

5.创建一个存储过程：
  处理文件并把他的内容存储到表中
  Scheduler_filewatcher_result is the data type of a file arrival event message.

SQL> CREATE OR REPLACE PROCEDURE process_image_files (
       payload IN SYS.scheduler_filewatcher_result)
    IS
       l_blob    BLOB;
       l_bfile   BFILE;
    BEGIN
       INSERT INTO images (
                 file_name,
                 file_size,
                file_content,
                uploaded_on)
           VALUES (
                payload.directory_path || '/' || payload.actual_file_name,
                payload.file_size,
                EMPTY_BLOB (),
                payload.file_timestamp)
        RETURNING file_content
             INTO l_blob;
  
      l_bfile := BFILENAME ('BLOB_DIR', payload.actual_file_name);
      DBMS_LOB.open (l_bfile, DBMS_LOB.lob_readonly);
      DBMS_LOB.open (l_blob, DBMS_LOB.lob_readwrite);
      DBMS_LOB.loadfromfile (
         dest_lob   => l_blob,
         src_lob    => l_bfile,
         amount     => DBMS_LOB.getlength (l_bfile));
      DBMS_LOB.close (l_blob);
      DBMS_LOB.close (l_bfile);
   END process_image_files;
   /

 

6.创建一个 program object,该program使用刚才创建的存储过程
  同时，也创建它的  event_message metadata argument

SQL> BEGIN
      DBMS_SCHEDULER.create_program (
          program_name      => 'image_watcher_p',
          program_type      => 'stored_procedure',
          program_action    => 'process_image_files',
          number_of_arguments   => 1,
          enabled       => FALSE);
       DBMS_SCHEDULER.define_metadata_argument (
          program_name     => 'image_watcher_p',
         metadata_attribute   => 'event_message',
         argument_position    => 1);
       DBMS_SCHEDULER.enable ('image_watcher_p');
   END;
   /

7.现在我们创建file watcher object.
   我们只处理后缀为jpg 的文件:

  SQL> BEGIN
       DBMS_SCHEDULER.create_file_watcher (
          file_watcher_name   => 'image_watcher_fw',
          directory_path      => '/home/oracle/jpg',
          file_name           => '*.jpg',
          credential_name     => 'oracle_credential',
          destination         => NULL,
          enabled         => FALSE);
       END;
   /

GRANT EXECUTE ON image_watcher_fw to SZTECH

8.创建基于事件的job,该job参考file watcher(indicated in the queue_spec)
  我们只对大小大于零的文件有兴趣(indicated by the event_condition).
  为了一次可以同时处理多个文件，需要设置 并行实例参数：parallel_instances=true
 
SQL> BEGIN
       DBMS_SCHEDULER.create_job (
          job_name      => 'image_load_j',
          program_name      => 'image_watcher_p',
          event_condition   => 'tab.user_data.file_size > 0',
          queue_spec        => 'image_watcher_fw',
          auto_drop         => FALSE,
          enabled       => FALSE);  
      DBMS_SCHEDULER.set_attribute('image_load_j','parallel_instances',TRUE);
   END;
   /

9.设置 file watcher 和 job 可用
SQL> BEGIN
       DBMS_SCHEDULER.enable ('image_watcher_fw,image_load_j,');
    END;
    /

 

10.修改观察间隔，默认为10分钟观察一次，修改为1分钟。

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    'FILE_WATCHER_SCHEDULE',
    'REPEAT_INTERVAL',
    'FREQ=MINUTELY;INTERVAL=1');
END;

11.查看文件查看器的情况
   select * from dba_scheduler_file_watchers
   OWNER         SYS
   FILE_WATCHER_NAME IMAGE_WATCHER_FW
   ENABLED         TRUE
   DESTINATION_OWNER 
   DESTINATION 
   DIRECTORY_PATH /home/oracle
   FILE_NAME         *.jpg
   CREDENTIAL_OWNER SYS
   CREDENTIAL_NAME ORACLE_CREDENTIAL
   MIN_FILE_SIZE 0
   STEADY_STATE_DURATION 
   LAST_MODIFIED_TIME 11-10月-12 06.10.49.270818 下午 +08:00
   COMMENTS 

12、删除Dropping File Watchers
   1、删除job
     
      begin
          DBMS_SCHEDULER.drop_job(job_name => 'image_load_j');
      end;
   2.删除file watcher

      begin
         DBMS_SCHEDULER.DROP_FILE_WATCHER('IMAGE_WATCHER_FW');
      end;
  
   3.删除program
      begin
 DBMS_SCHEDULER.drop_program(program_name => 'image_watcher_p');
      end;

   4.删除过程
      drop procedure process_image_files
     
       
   5.删除凭证
      begin
        DBMS_SCHEDULER.drop_credential(credential_name => 'ORACLE_CREDENTIAL');
      end;
  
   6.删除目录
      drop DIRECTORY blob_dir ;