1 Reply Latest reply: Oct 2, 2013 5:07 PM by Barbara Boehmer RSS

    Stuck with filewatcher

    Nag Aswadhati

      Hi Experts,

       

      I have created a job which has to run on a file arrival. But the job is not giving any response.

      I am able to run the same job manually by using run_job procedure.

       

      Please help me to fix this. Do I miss any steps to follow, suggest me.

       

      version details

      ----------------------------------------------------------

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      PL/SQL Release 11.2.0.3.0 - Production

      "CORE 11.2.0.3.0 Production"

      TNS for Linux: Version 11.2.0.3.0 - Production

      NLSRTL Version 11.2.0.3.0 - Production

      -------------------------------------------------------------------------------------------

       

       

      code

      ------------------------------------------------------------------------

      BEGIN

          SYS.DBMS_SCHEDULER.CREATE_JOB (

                  job_name => '"UPN_COMMON"."WATCHJOB"',

                  job_type => 'STORED_PROCEDURE',

                  job_action => '"UPN_COMMON"."INS_EMP"',

                  number_of_arguments => 0,

                  start_date => SYSTIMESTAMP,

                  event_condition => '(1=1)',

                  queue_spec => '"UPN_COMMON"."FILE_WATCHER"',

                  end_date => SYSTIMESTAMP+0.1,

                  job_class => '"SYS"."DEFAULT_JOB_CLASS"',

                  enabled => FALSE,

                  auto_drop => FALSE,

                  comments => 'TESTING A PROCEDURE',

                  credential_name => NULL,

                  destination_name => NULL);

       

          SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

                   name => '"UPN_COMMON"."WATCHJOB"',

                   attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

          

       

          SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

                   name => '"UPN_COMMON"."WATCHJOB"',

                   attribute => 'max_run_duration', value => INTERVAL '1' MINUTE);

          SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

                   name => '"UPN_COMMON"."WATCHJOB"',

                   attribute => 'schedule_limit', value => INTERVAL '1' MINUTE);  

          SYS.DBMS_SCHEDULER.enable(

                   name => '"UPN_COMMON"."WATCHJOB"');

      END;

      /

       

       

      declare

        filtyp utl_file.file_type;

      begin

        filtyp := utl_file.fopen('UPNCOMMON_DIR', 'file.txt', 'W', NULL);

        utl_file.put_line(filtyp, 'File has arrived '||SYSTIMESTAMP, TRUE);

        utl_file.fclose(filtyp);

      end;

      /

       

       

      exec dbms_scheduler.run_job('WATCHJOB');

      ----------------------------------------------------------------------------------------------------

       

      It would be great appreciation if you give me an example with solution.

       

       

      Thanks all.

        • 1. Re: Stuck with filewatcher
          Barbara Boehmer

          Please see the following modification of what you posted.

           

           

          SCOTT@orcl12c> CONN / AS SYSDBA

          Connected.

          SYS@orcl12c> -- set file watcher interval to one minute:

          SYS@orcl12c> BEGIN

            2    DBMS_SCHEDULER.SET_ATTRIBUTE

            3       ('file_watcher_schedule',

            4        'repeat_interval',

            5        'freq=minutely; interval=1');

            6  END;

            7  /

           

          PL/SQL procedure successfully completed.

           

          SYS@orcl12c> -- create user with dba privileges:

          SYS@orcl12c> CREATE USER upn_common IDENTIFIED BY upn_common

            2  /

           

          User created.

           

          SYS@orcl12c> GRANT DBA TO upn_common

            2  /

           

          Grant succeeded.

           

          SYS@orcl12c> CONNECT upn_common/upn_common

          Connected.

          UPN_COMMON@orcl12c> -- create table to insert results into:

          UPN_COMMON@orcl12c> CREATE TABLE file_watcher_output

            2    (message VARCHAR2(4000))

            3  /

           

          Table created.

           

          UPN_COMMON@orcl12c> -- create procedure to insert results:

          UPN_COMMON@orcl12c> CREATE OR REPLACE PROCEDURE ins_emp

            2  AS

            3  BEGIN

            4    INSERT INTO file_watcher_output (message)

            5    VALUES ('File has arrived ' || SYSTIMESTAMP);

            6    COMMIT;

            7  END;

            8  /

           

          Procedure created.

           

          UPN_COMMON@orcl12c> BEGIN

            2    -- create credential using operating system user and password (fill in your own):

            3    DBMS_SCHEDULER.CREATE_CREDENTIAL

            4       (credential_name     => 'local_credential',

            5        username          => '...',

            6        password          => '...');

            7    -- create file watcher:

            8    DBMS_SCHEDULER.CREATE_FILE_WATCHER

            9       (file_watcher_name   => 'test_file_watcher',

          10        directory_path      => 'c:\my_oracle_files',

          11        file_name          => 'file.txt',

          12        credential_name     => 'local_credential',

          13        enabled          => FALSE);

          14    -- create job:

          15    DBMS_SCHEDULER.CREATE_JOB

          16       (job_name          => 'watchjob',

          17        job_type          => 'stored_procedure',

          18        job_action          => 'ins_emp',

          19        start_date          => SYSTIMESTAMP,

          20        queue_spec          => 'test_file_watcher',

          21        end_date          => SYSTIMESTAMP+0.1,

          22        auto_drop          => FALSE,

          23        comments          => 'TESTING A PROCEDURE');

          24    -- set attributes:

          25    DBMS_SCHEDULER.SET_ATTRIBUTE

          26       (name              => 'watchjob',

          27        attribute          => 'logging_level',

          28        value           => DBMS_SCHEDULER.LOGGING_OFF);

          29    DBMS_SCHEDULER.SET_ATTRIBUTE

          30       (name              => 'watchjob',

          31        attribute          => 'max_run_duration',

          32        value           => INTERVAL '1' MINUTE);

          33    DBMS_SCHEDULER.SET_ATTRIBUTE

          34       (name              => 'watchjob',

          35        attribute          => 'schedule_limit',

          36        value           => INTERVAL '1' MINUTE);

          37    -- enable:

          38    DBMS_SCHEDULER.enable ('test_file_watcher');

          39    DBMS_SCHEDULER.enable ('watchjob');

          40  END;

          41  /

           

          PL/SQL procedure successfully completed.

           

          UPN_COMMON@orcl12c> -- write file (file must not exist previously):

          UPN_COMMON@orcl12c> CREATE OR REPLACE DIRECTORY upncommon_dir AS 'c:\my_oracle_files'

            2  /

           

          Directory created.

           

          UPN_COMMON@orcl12c> declare

            2    filtyp utl_file.file_type;

            3  begin

            4    filtyp := utl_file.fopen ('UPNCOMMON_DIR', 'file.txt', 'W', NULL);

            5    utl_file.put_line (filtyp, 'File has arrived ' || SYSTIMESTAMP, TRUE);

            6    utl_file.fclose (filtyp);

            7  end;

            8  /

           

          PL/SQL procedure successfully completed.

           

          UPN_COMMON@orcl12c> -- wait long enough (may take more than one minute) for job to run:

          UPN_COMMON@orcl12c> EXEC DBMS_LOCK.SLEEP (75)

           

          PL/SQL procedure successfully completed.

           

          UPN_COMMON@orcl12c> -- check for results:

          UPN_COMMON@orcl12c> SELECT * FROM file_watcher_output

            2  /

           

          MESSAGE

          --------------------------------------------------------------------------------

          File has arrived 02-OCT-13 03.02.37.345000 PM -07:00

           

          1 row selected.

           

          UPN_COMMON@orcl12c> -- clean-up:

          UPN_COMMON@orcl12c> EXEC DBMS_SCHEDULER.DROP_JOB ('WATCHJOB')

           

          PL/SQL procedure successfully completed.

           

          UPN_COMMON@orcl12c> EXEC DBMS_SCHEDULER.DROP_FILE_WATCHER ('test_file_watcher')

           

          PL/SQL procedure successfully completed.

           

          UPN_COMMON@orcl12c> EXEC DBMS_SCHEDULER.DROP_CREDENTIAL ('local_credential')

           

          PL/SQL procedure successfully completed.

           

          UPN_COMMON@orcl12c> DROP PROCEDURE ins_emp

            2  /

           

          Procedure dropped.

           

          UPN_COMMON@orcl12c> DROP TABLE file_watcher_output

            2  /

           

          Table dropped.

           

          UPN_COMMON@orcl12c> CONN / AS SYSDBA

          Connected.

          SYS@orcl12c> DROP USER upn_common CASCADE

            2  /

           

          User dropped.

           

          SYS@orcl12c> -- remove file:

          SYS@orcl12c> HOST DEL c:\my_oracle_files\file.txt