7 Replies Latest reply: Oct 25, 2013 10: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

      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

          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

            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

              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

                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

                  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

                    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

                      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.