This discussion is archived
1 Reply Latest reply: Oct 2, 2013 3:07 PM by Barbara Boehmer RSS

Stuck with filewatcher

Nag Aswadhati Explorer
Currently Being Moderated

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 Oracle ACE
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points