3 Replies Latest reply: Feb 12, 2007 4:28 PM by Rnr-Oracle RSS

    SQL Output from Job Scheduler

    549859
      We are currently doing our ETL for a data warehouse via a scheduler called Opalis. This scheduler is no longer supported within our organization. This is why I am trying to convert to the Oracle Job Scheduler, which makes far more sense anyway as all will then be in the database. Among other challenges, I am having trouble trying to find a way to see the messages produced by SQL code executed within the Job Scheduler. We currently run SQL*PLUS and PL/SQL code by executing .sql scripts. For example we would run "sqlplus logon/password@instance @sqlscript.sql" as a command. Within the sqlscript.sql would be something like the following. Once the step has run, we can look at the dws_office.log to see what actually occurred. I have not been able to find a way to do this with the Job Scheduler. I have been able to run some SQL*PLUS within the PL/SQL block using EXECUTE IMMEDIATE. However I have not been able to find a way to spool the output. Any ideas? Sorry for the lengthy post, and thanks - Susan:

      spool dws_office.log
      SET HEADING ON
      SET ECHO ON
      SET VERIFY ON
      SET FEEDBACK ON


      select TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS AM') from DUAL;

      TRUNCATE TABLE OFFICE;
      INSERT INTO office
      (SELECT office_id,
      region_id,
      office_name,
      office_type,
      primary_phone,
      date_active,
      '',
      '',
      '',
      agency,
      default_email,
      zip,
      city,
      agency_office_code,
      county,
      fax,
      office_description,
      office_JSC_id,
      secondary_phone,
      zip_plus_four,
      state,
      address1,
      address2,
      '',
      '',
      '',
      maintenance_user,
      maintenance_date,
      date_inactive
      FROM office_data);

      UPDATE office o
      SET o.OfficeGroupID =
      (SELECT og.OfficeGroupID
      FROM OfficeGroup og
      WHERE o.officeid = og.officeid),
      o.OfficeGroupName =
      (SELECT og.OfficeGroupName
      FROM OfficeGroup og
      WHERE o.officeid = og.officeid),
      o.regionname =
      (SELECT r.region_name
      FROM regions r
      WHERE o.regionid = r.region_id),
      o.georegionid =
      (SELECT g.georegionid
      FROM georegion g
      WHERE o.regionid = g.regionid),
      o.georegionname =
      (SELECT g.georegionname
      FROM georegion g
      WHERE o.regionid = g.regionid),
      o.arearollupname =
      (SELECT e.arearollupname
      FROM esdrolluparea e
      WHERE o.officeid = e.officeid);
      commit;

      select TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS AM') from DUAL;

      BEGIN
      dbms_stats.gather_table_stats(ownname=> 'DWSCHEMA', tabname=> 'office', partname=> NULL);
      END;
      /

      spool off
      exit
        • 1. Re: SQL Output from Job Scheduler
          Rnr-Oracle
          Hi Susan,

          The problem is that PL/SQL does not support formatting or file output/logging directly.

          There are 2 approaches that can be used to get around this.

          - The Scheduler supports running external shell scripts. What you can do is have the scheduler execute a shell script that calls sqlplus with your script and redirect/spool the output.

          - The other way is to implement a simple logging infrastructure in PL./SQL. e.g. implement util_logging.log(text) which logs sysdate and the text provided to either an output table or a file (using utl_file), then just call the logging function regularly in your script when you want the status of some operation logged.

          Since this is a generic PL/SQL problem, you may be ale to get some ideas on the PL/SQL forum.

          Hope this helps,
          Ravi.
          • 2. Re: SQL Output from Job Scheduler
            553048
            Has anyone gotten this to work.

            10g scheduler job_action = '/home/oracle/test.sh > /home/oracle/test.log'

            test.sh
            ======
            #!/usr/bin/ksh

            echo "Hello !"
            ======

            Job runs fine without the "> /home/oracle/test.log", but fails with it:

            ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory

            Have an SR open, but this is obviously not very important to them, but I have lots of cron jobs that use this syntax that I want to schedule via 10g scheduler. I know there are workarounds, but should this or should it not work ? I'm probably just missing something ?

            Any and all "helpful" sugggestions appreciated.
            • 3. Re: SQL Output from Job Scheduler
              Rnr-Oracle
              Hi,

              I don't think this is supposed to work.

              The job_action for an external job is supposed to be the path to and filename of an executable. Pathnames can contain spaces and special characters so your job_action is trying to run a file called
              "/home/oracle/test.sh > /home/oracle/test.log"

              Unfortunately you will have to use one of the workarounds.

              -Ravi