This discussion is archived
7 Replies Latest reply: Oct 25, 2013 8:57 PM by Barbara Boehmer RSS

Help: I want to auto schedule a load using file watcher but it runs only once for the first time and after that it is not running at all

75e48d66-ba77-433a-b64d-b9150016f65e Newbie
Currently Being Moderated

Hi All,

 

I am trying  to execute the below code as provided from one of the blogs. i am able to run the job only once based on a file watcher object(i.e. for very first time) and after that the job is not running at all and if  i schedule the job to run automatically based on interval of 10 or more minutes it is executing properly). Please let me know or guide me if i have missed any step or configuration.that is needed.

Version of Oracle 11.2.0.1.0

OS : Windows 7 Prof

Given all the necessary privileges

BEGIN

  DBMS_SCHEDULER.CREATE_CREDENTIAL(

     credential_name => 'cred',

     username        => 'XXXX',

     password        => 'XXXX');

END;

/

 

CREATE TABLE ZZZZ (WHEN timestamp, file_name varchar2(100),

   file_size number, processed char(1));

 

CREATE OR REPLACE PROCEDURE YYYY

  (payload IN sys.scheduler_filewatcher_result) AS

BEGIN

  INSERT INTO ZZZZ VALUES

     (payload.file_timestamp,

      payload.directory_path || '/' || payload.actual_file_name,

      payload.file_size,

      'N');

END;

/

 

BEGIN

  DBMS_SCHEDULER.CREATE_PROGRAM(

    program_name        => 'prog1',

    program_type        => 'stored_procedure',

    program_action      => 'YYYY',

    number_of_arguments => 1,

    enabled             => FALSE);

  DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT(

    program_name        => 'prog1',

    metadata_attribute  => 'event_message',

    argument_position   => 1);

  DBMS_SCHEDULER.ENABLE('prog1');

END;

/

 

BEGIN

  DBMS_SCHEDULER.CREATE_FILE_WATCHER(

    file_watcher_name => 'file_watcher1',

    directory_path    => 'D:\AAAA',

    file_name         => '*.txt',

    credential_name   => 'cred',

    destination       => NULL,

    enabled           => FALSE);

END;

/

 

BEGIN

  DBMS_SCHEDULER.CREATE_JOB(

    job_name        => 'job1',

    program_name    => 'prog1',

    queue_spec      => 'file_watcher1',

    auto_drop       => FALSE,

    enabled         => FALSE);

  DBMS_SCHEDULER.SET_ATTRIBUTE('job1','PARALLEL_INSTANCES',TRUE);

END;

/

 

EXEC DBMS_SCHEDULER.ENABLE('file_watcher1,job1');

Regards,

kumar.

  • 1. Re: Help: I want to auto schedule a load using file watcher but it runs only once for the first time and after that it is not running at all
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    Please try the following and see if it helps.

     

    CONN / AS SYSDBA

    -- set file watcher interval to one minute:

    BEGIN

      DBMS_SCHEDULER.SET_ATTRIBUTE

        ('file_watcher_schedule',

         'repeat_interval',

         'freq=minutely; interval=1');

    END;

    /

  • 2. Re: Help: I want to auto schedule a load using file watcher but it runs only once for the first time and after that it is not running at all
    75e48d66-ba77-433a-b64d-b9150016f65e Newbie
    Currently Being Moderated

    Hi Barbara,

     

    Thanks for your reply but after setting the above parameter also it is not running and one thing i observed is that the job is running only once for each day though i am loading multiple files. I even set the attribute parallel instances for the job to TRUE but no luck.

     

    With Regards,

    kumar.

  • 3. Re: Help: I want to auto schedule a load using file watcher but it runs only once for the first time and after that it is not running at all
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    Please post a copy and paste of a complete run of a test case, similar to what I have shown below.

     

    SCOTT@orcl12c> SELECT banner FROM v$version

      2  /

     

    BANNER

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

    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

    PL/SQL Release 12.1.0.1.0 - Production

    CORE    12.1.0.1.0    Production

    TNS for 64-bit Windows: Version 12.1.0.1.0 - Production

    NLSRTL Version 12.1.0.1.0 - Production

     

    5 rows selected.

     

    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> CONNECT scott/tiger

    Connected.

    SCOTT@orcl12c> BEGIN

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

      3    DBMS_SCHEDULER.CREATE_CREDENTIAL

      4       (credential_name     => 'cred',

      5        username          => '...',

      6        password          => '...');

      7  END;

      8  /

     

    PL/SQL procedure successfully completed.

     

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

    SCOTT@orcl12c> CREATE TABLE ZZZZ

      2    (WHEN      timestamp,

      3      file_name varchar2(100),

      4      file_size number,

      5      processed char(1))

      6  /

     

    Table created.

     

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

    SCOTT@orcl12c> CREATE OR REPLACE PROCEDURE YYYY

      2    (payload IN sys.scheduler_filewatcher_result)

      3  AS

      4  BEGIN

      5    INSERT INTO ZZZZ VALUES

      6        (payload.file_timestamp,

      7         payload.directory_path || '/' || payload.actual_file_name,

      8         payload.file_size,

      9         'N');

    10  END;

    11  /

     

    Procedure created.

     

    SCOTT@orcl12c> -- create program, define metadata, and enable:

    SCOTT@orcl12c> BEGIN

      2    DBMS_SCHEDULER.CREATE_PROGRAM

      3       (program_name          => 'prog1',

      4        program_type          => 'stored_procedure',

      5        program_action      => 'YYYY',

      6        number_of_arguments => 1,

      7        enabled          => FALSE);

      8    DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT(

      9       program_name         => 'prog1',

    10       metadata_attribute  => 'event_message',

    11       argument_position   => 1);

    12    DBMS_SCHEDULER.ENABLE ('prog1');

    13  END;

    14  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> BEGIN

      2    -- create file watcher:

      3    DBMS_SCHEDULER.CREATE_FILE_WATCHER

      4       (file_watcher_name   => 'file_watcher1',

      5        directory_path      => 'c:\my_oracle_files',

      6        file_name          => 'f*.txt',

      7        credential_name     => 'cred',

      8        destination          => NULL,

      9        enabled          => FALSE);

    10  END;

    11  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> BEGIN

      2    -- create job:

      3    DBMS_SCHEDULER.CREATE_JOB

      4       (job_name          => 'job1',

      5        program_name          => 'prog1',

      6        queue_spec          => 'file_watcher1',

      7        auto_drop          => FALSE,

      8        enabled          => FALSE);

      9    -- set attributes:

    10    DBMS_SCHEDULER.SET_ATTRIBUTE ('job1', 'PARALLEL_INSTANCES', TRUE);

    11  END;

    12  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> -- enable:

    SCOTT@orcl12c> EXEC DBMS_SCHEDULER.enable ('file_watcher1, job1');

     

    PL/SQL procedure successfully completed.

     

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

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

      2  /

     

    Directory created.

     

    SCOTT@orcl12c> declare

      2    filtyp utl_file.file_type;

      3  begin

      4    filtyp := utl_file.fopen ('UPNCOMMON_DIR', 'file1.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.

     

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

    SCOTT@orcl12c> EXEC DBMS_LOCK.SLEEP (100)

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> -- check for results:

    SCOTT@orcl12c> SELECT * FROM zzzz

      2  /

     

    WHEN

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

    FILE_NAME

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

    FILE_SIZE P

    ---------- -

    22-OCT-13 10.12.28.309000 PM

    c:\my_oracle_files/file1.txt

            57 N

     

     

    1 row selected.

     

    SCOTT@orcl12c> declare

      2    filtyp utl_file.file_type;

      3  begin

      4    filtyp := utl_file.fopen ('UPNCOMMON_DIR', 'file2.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.

     

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

    SCOTT@orcl12c> EXEC DBMS_LOCK.SLEEP (100)

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> -- check for results:

    SCOTT@orcl12c> SELECT * FROM zzzz

      2  /

     

    WHEN

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

    FILE_NAME

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

    FILE_SIZE P

    ---------- -

    22-OCT-13 10.12.28.309000 PM

    c:\my_oracle_files/file1.txt

            57 N

     

    22-OCT-13 10.14.08.580000 PM

    c:\my_oracle_files/file2.txt

            57 N

     

     

    2 rows selected.

  • 4. Re: Help: I want to auto schedule a load using file watcher but it runs only once for the first time and after that it is not running at all
    75e48d66-ba77-433a-b64d-b9150016f65e Newbie
    Currently Being Moderated

    Hi Barbara,

     

    Thanks for your reply but i am still facing the same issue as i was facing earlier i.e. only once the job is being executed.When i tried to execute the below code

    the very first time it got executed and the file details were loaded in to the table zzzz but from second time on wards it is not being executed

     

     

     

     

     

    conn / as sysdba

     

     

     

     

    SELECT banner FROM v$version;

     

     

     

     

    BANNER

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

     

     

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

    PL/SQL Release 11.2.0.1.0 - Production

    CORE    11.2.0.1.0      Production

    TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

     

     

     

     

     

     

    BEGIN

         DBMS_SCHEDULER.SET_ATTRIBUTE

          ('file_watcher_schedule',

             'repeat_interval',

              'freq=minutely; interval=1');

       END;

        /

     

     

     

     

    conn scott/tiger

     

     

     

     

    BEGIN

      DBMS_SCHEDULER.CREATE_CREDENTIAL

            (credential_name     => 'cred',

             username          => 'osuser',

              password          => 'ospwd');

        END;

        /

     

     

     

     

     

     

    CREATE TABLE ZZZZ

         (WHEN      timestamp,

            file_name varchar2(100),

            file_size number,

            processed char(1))

       /

     

     

     

     

    CREATE OR REPLACE PROCEDURE YYYY

          (payload IN sys.scheduler_filewatcher_result)

        AS

          BEGIN

         INSERT INTO ZZZZ VALUES

             (payload.file_timestamp,

              payload.directory_path || '/' || payload.actual_file_name,

               payload.file_size,

              'N');

      END;

      /

     

     

     

     

     

     

    BEGIN

         DBMS_SCHEDULER.CREATE_PROGRAM

             (program_name          => 'prog1',

              program_type          => 'stored_procedure',

              program_action      => 'YYYY',

             number_of_arguments => 1,

             enabled          => FALSE);

         DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT(

            program_name         => 'prog1',

           metadata_attribute  => 'event_message',

           argument_position   => 1);

        DBMS_SCHEDULER.ENABLE ('prog1');

      END;

      /

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    BEGIN

        

         DBMS_SCHEDULER.CREATE_FILE_WATCHER

           (file_watcher_name   => 'file_watcher1',

            directory_path      => 'c:\my_oracle_files',

             file_name          => 'f*.txt',

             credential_name     => 'cred',

             destination          => NULL,

            enabled          => FALSE);

    END;

      /

     

     

     

     

     

     

     

     

     

     

     

     

    BEGIN

     

          DBMS_SCHEDULER.CREATE_JOB

             (job_name          => 'job1',

              program_name          => 'prog1',

              queue_spec          => 'file_watcher1',

              auto_drop          => FALSE,

              enabled          => FALSE);

         

       DBMS_SCHEDULER.SET_ATTRIBUTE ('job1', 'PARALLEL_INSTANCES', TRUE);

      END;

    /

     

     

     

     

    EXEC DBMS_SCHEDULER.enable ('file_watcher1, job1');

     

     

    CREATE OR REPLACE DIRECTORY upncommon_dir AS 'c:\my_oracle_files'

    /

     

     

     

     

     

     

    declare

          filtyp utl_file.file_type;

        begin

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

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

          utl_file.fclose (filtyp);

        end;

        /

     

     

     

     

    EXEC DBMS_LOCK.SLEEP (100);

     

     

     

     

     

     

     

     

    SQL> select * from zzzz;

     

     

    WHEN

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

    FILE_NAME

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

     

     

    FILE_SIZE P

    ---------- -

    24-OCT-13 07.47.41.237000 AM

    c:\my_oracle_files/file189.txt

            28 N

     

     

     

     

     

     

     

     

    declare

          filtyp utl_file.file_type;

         begin

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

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

          utl_file.fclose (filtyp);

        end;

        /

     

     

     

     

    EXEC DBMS_LOCK.SLEEP (100);

     

     

    select * from zzzz;

     

     

     

     

     

     

    WHEN

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

    FILE_NAME

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

     

     

    FILE_SIZE P

    ---------- -

    24-OCT-13 07.47.41.237000 AM

    c:\my_oracle_files/file189.txt

            28 N

     

     

     

     

     

    With Regards,

    kumar.

  • 5. Re: Help: I want to auto schedule a load using file watcher but it runs only once for the first time and after that it is not running at all
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    I ran the same script that I ran previously on 12c, but this time on 11g, and encountered the same problem that you did, no second file, as shown below.  So, I suspect this is an 11g bug.  If you have Oracle support, I would contact them.

     

     

    SCOTT@orcl> SELECT banner FROM v$version

      2  /

     

    BANNER

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

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

    PL/SQL Release 11.2.0.1.0 - Production

    CORE    11.2.0.1.0    Production

    TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

     

    5 rows selected.

     

    SCOTT@orcl> CONN / AS SYSDBA

    Connected.

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

    SYS@orcl> 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@orcl> CONNECT scott/tiger

    Connected.

    SCOTT@orcl> BEGIN

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

      3    DBMS_SCHEDULER.CREATE_CREDENTIAL

      4       (credential_name     => 'cred',

      5        username          => '...',

      6        password          => '...');

      7  END;

      8  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl> -- create table to insert results into:

    SCOTT@orcl> CREATE TABLE ZZZZ

      2    (WHEN      timestamp,

      3      file_name varchar2(100),

      4      file_size number,

      5      processed char(1))

      6  /

     

    Table created.

     

    SCOTT@orcl> -- create procedure to insert results:

    SCOTT@orcl> CREATE OR REPLACE PROCEDURE YYYY

      2    (payload IN sys.scheduler_filewatcher_result)

      3  AS

      4  BEGIN

      5    INSERT INTO ZZZZ VALUES

      6        (payload.file_timestamp,

      7         payload.directory_path || '/' || payload.actual_file_name,

      8         payload.file_size,

      9         'N');

    10  END;

    11  /

     

    Procedure created.

     

    SCOTT@orcl> -- create program, define metadata, and enable:

    SCOTT@orcl> BEGIN

      2    DBMS_SCHEDULER.CREATE_PROGRAM

      3       (program_name          => 'prog1',

      4        program_type          => 'stored_procedure',

      5        program_action      => 'YYYY',

      6        number_of_arguments => 1,

      7        enabled          => FALSE);

      8    DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT(

      9       program_name         => 'prog1',

    10       metadata_attribute  => 'event_message',

    11       argument_position   => 1);

    12    DBMS_SCHEDULER.ENABLE ('prog1');

    13  END;

    14  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl> BEGIN

      2    -- create file watcher:

      3    DBMS_SCHEDULER.CREATE_FILE_WATCHER

      4       (file_watcher_name   => 'file_watcher1',

      5        directory_path      => 'c:\my_oracle_files',

      6        file_name          => 'f*.txt',

      7        credential_name     => 'cred',

      8        destination          => NULL,

      9        enabled          => FALSE);

    10  END;

    11  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl> BEGIN

      2    -- create job:

      3    DBMS_SCHEDULER.CREATE_JOB

      4       (job_name          => 'job1',

      5        program_name          => 'prog1',

      6        queue_spec          => 'file_watcher1',

      7        auto_drop          => FALSE,

      8        enabled          => FALSE);

      9    -- set attributes:

    10    DBMS_SCHEDULER.SET_ATTRIBUTE ('job1', 'PARALLEL_INSTANCES', TRUE);

    11  END;

    12  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl> -- enable:

    SCOTT@orcl> EXEC DBMS_SCHEDULER.enable ('file_watcher1, job1');

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl> -- write file (file must not exist previously):

    SCOTT@orcl> CREATE OR REPLACE DIRECTORY upncommon_dir AS 'c:\my_oracle_files'

      2  /

     

    Directory created.

     

    SCOTT@orcl> declare

      2    filtyp utl_file.file_type;

      3  begin

      4    filtyp := utl_file.fopen ('UPNCOMMON_DIR', 'file1.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.

     

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

    SCOTT@orcl> EXEC DBMS_LOCK.SLEEP (100)

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl> -- check for results:

    SCOTT@orcl> SELECT * FROM zzzz

      2  /

     

    WHEN

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

    FILE_NAME

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

    FILE_SIZE P

    ---------- -

    24-OCT-13 08.43.36.735000 PM

    c:\my_oracle_files/file1.txt

            57 N

     

     

    1 row selected.

     

    SCOTT@orcl> declare

      2    filtyp utl_file.file_type;

      3  begin

      4    filtyp := utl_file.fopen ('UPNCOMMON_DIR', 'file2.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.

     

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

    SCOTT@orcl> EXEC DBMS_LOCK.SLEEP (100)

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl> -- check for results:

    SCOTT@orcl> SELECT * FROM zzzz

      2  /

     

    WHEN

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

    FILE_NAME

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

    FILE_SIZE P

    ---------- -

    24-OCT-13 08.43.36.735000 PM

    c:\my_oracle_files/file1.txt

            57 N

     

     

    1 row selected.

  • 6. Re: Help: I want to auto schedule a load using file watcher but it runs only once for the first time and after that it is not running at all
    75e48d66-ba77-433a-b64d-b9150016f65e Newbie
    Currently Being Moderated

    Hi Barbara,

    Thanks for confirming the same and can you let me know is there any way to call a batch file based on arrival of a file.

    With Regards,

    Kumar.

  • 7. Re: Help: I want to auto schedule a load using file watcher but it runs only once for the first time and after that it is not running at all
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    75e48d66-ba77-433a-b64d-b9150016f65e wrote:

     

    ... can you let me know is there any way to call a batch file based on arrival of a file. ...

     

     

     

    SCOTT@orcl12c> SELECT banner FROM v$version

      2  /

     

    BANNER

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

    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

    PL/SQL Release 12.1.0.1.0 - Production

    CORE    12.1.0.1.0      Production

    TNS for 64-bit Windows: Version 12.1.0.1.0 - Production

    NLSRTL Version 12.1.0.1.0 - Production

     

    5 rows selected.

     

    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> CONNECT scott/tiger

    Connected.

    SCOTT@orcl12c> BEGIN

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

      3    DBMS_SCHEDULER.CREATE_CREDENTIAL

      4      (credential_name     => 'cred',

      5       username            => '...',

      6       password            => '...');

      7  END;

      8  /

     

    PL/SQL procedure successfully completed.

     

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

    SCOTT@orcl12c> CREATE TABLE zzzz

      2    (message  VARCHAR2(4000))

      3  /

     

    Table created.

     

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

    SCOTT@orcl12c> CREATE OR REPLACE PROCEDURE yyyy

      2  AS

      3  BEGIN

      4    INSERT INTO zzzz (message)

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

      6    COMMIT;

      7  END yyyy;

      8  /

     

    Procedure created.

     

    SCOTT@orcl12c> -- sql file to run procedure:

    SCOTT@orcl12c> HOST TYPE c:\my_oracle_files\test.sql

    EXEC yyyy

    EXIT

     

    SCOTT@orcl12c> -- batch file to run sql file:

    SCOTT@orcl12c> HOST TYPE c:\my_oracle_files\test.bat

    C:\app\baboehme\product\12.1.0\dbhome_2\BIN\sqlplus.exe scott/tiger @c:\my_oracle_files\test.sql

     

    SCOTT@orcl12c> BEGIN

      2    -- create file watcher:

      3    DBMS_SCHEDULER.CREATE_FILE_WATCHER

      4      (file_watcher_name   =>  'file_watcher1',

      5       directory_path      =>  'c:\my_oracle_files',

      6       file_name           =>  'f*.txt',

      7       credential_name     =>  'cred',

      8       destination         =>  NULL,

      9       enabled             =>  FALSE);

    10  END;

    11  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> BEGIN

      2    -- create job:

      3    DBMS_SCHEDULER.CREATE_JOB

      4      (job_name            =>  'job1',

      5       job_type            =>  'EXECUTABLE',

      6       job_action          =>  'c:\windows\system32\cmd.exe',

      7       queue_spec          =>  'file_watcher1',

      8       auto_drop           =>  FALSE,

      9       number_of_arguments =>  3,

    10       enabled             =>  FALSE,

    11       credential_name     =>  'cred');

    12  END;

    13  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> BEGIN

      2    -- set job argument values:

      3    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ('job1', 1, '/q');

      4    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ('job1', 2, '/c');

      5    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ('job1', 3, 'c:\my_oracle_files\test.bat >nul');

      6  END;

      7  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> -- enable:

    SCOTT@orcl12c> EXEC DBMS_SCHEDULER.ENABLE ('file_watcher1, job1');

     

    PL/SQL procedure successfully completed.

     

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

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

      2  /

     

    Directory created.

     

    SCOTT@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.

     

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

    SCOTT@orcl12c> EXEC DBMS_LOCK.SLEEP (100)

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl12c> -- check for results:

    SCOTT@orcl12c> SELECT * FROM zzzz

      2  /

     

    MESSAGE

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

    File has arrived 25-OCT-13 08.51.43.451000 PM -07:00

     

    1 row selected.

     

    SCOTT@orcl12c> spool off

    SCOTT@orcl12c> SELECT *

      2  FROM   (SELECT TO_CHAR (log_date, 'hh24:mi:ss') dat,

      3                 status,

      4                 additional_info

      5          FROM   user_scheduler_job_run_details o

      6          WHERE  job_name = 'JOB1'

      7          ORDER  BY log_date DESC)

      8  WHERE  ROWNUM = 1

      9  /

     

    DAT      STATUS

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

    ADDITIONAL_INFO

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

    20:51:43 SUCCEEDED

    EXTERNAL_LOG_ID="job_110847_62284",

    USERNAME="owner"

     

     

    1 row selected.

Legend

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