Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Stuck with filewatcher

Nag AswadhatiOct 1 2013 — edited Oct 2 2013

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.

Comments

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

1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 30 2013
Added on Oct 1 2013
#database-key-features, #scheduler
1 comment
1,619 views