5 Replies Latest reply on Jul 5, 2017 4:08 PM by rp0428

    Using DBMS_SCHEDULER to run SQL Developer reports

    Helen21again

      Hello

      I'm using Oracle 12.2 EE on Windows 10 and SQL Developer 4.1.5

       

      I have created a very simple user defined report in SQL Developer called Demo - the query is select * from emp;

       

      I can run the report via the command line and it works no problem and produces the output in HTML format as expected

      C:\sqldeveloper\sqldeveloper\bin\sdcli64.exe reports generate -report "Demo" -db hr -file "C:\Users\Helen\Desktop\testHTML.html"

       

      I would now like to schedule this to run using DBMS_SCHEDULER - I have tried the following:

      BEGIN

      DBMS_SCHEDULER.create_program(

              program_name => 'DEMO_REPORTS',

              program_action => 'C:\sqldeveloper\sqldeveloper\bin\sdcli64.exe',

              program_type => 'EXECUTABLE',

              number_of_arguments => 4,

              comments => NULL,

              enabled => FALSE);

       

      DBMS_SCHEDULER.define_program_argument(

              program_name => 'DEMO_REPORTS',

              argument_name => '"reports"',

              argument_position => 1,

              argument_type => 'CHAR',

              default_value => 'generate',

              out_argument => FALSE);

       

          DBMS_SCHEDULER.define_program_argument(

              program_name => 'DEMO_REPORTS',

              argument_name => '"report"',

              argument_position => 2,

              argument_type => 'CHAR',

              default_value => 'Demo',

              out_argument => FALSE);

       

          DBMS_SCHEDULER.define_program_argument(

              program_name => 'DEMO_REPORTS',

              argument_name => '"db"',

              argument_position => 3,

              argument_type => 'CHAR',

              default_value => 'hr',

              out_argument => FALSE);

       

          DBMS_SCHEDULER.define_program_argument(

              program_name => 'DEMO_REPORTS',

              argument_name => '"file"',

              argument_position => 4,

              argument_type => 'CHAR',

              default_value => '"C:\Users\Helen\Desktop\testHTML.html"',

              out_argument => FALSE);

       

          DBMS_SCHEDULER.ENABLE(name=>'DEMO_REPORTS');

       

       

      DBMS_SCHEDULER.CREATE_JOB (

                  job_name => '"DEMO_JOB"',

                  program_name => '"DEMO_REPORTS"',

                  start_date => NULL,

                  repeat_interval => NULL,

                  end_date => NULL,

                  enabled => FALSE,

                  auto_drop => FALSE,

                  comments => '');

       

       

          DBMS_SCHEDULER.SET_ATTRIBUTE(

                   name => '"DEMO_JOB"',

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

       

          DBMS_SCHEDULER.enable(

                   name => '"DEMO_JOB"');

      END;

      /

      I don't get any syntax errors from this neither do I get a file generated. Any help as to where I have gone wrong or how to start debugging this would be much appreciated. Nothing shows up for this in the run log so I am assuming my parameters aren't correct?

      Thanks for any help

      Regards

      Helen