Page History

...

PL/SQL script as external file 

...

• JobChain

  Code Block
  language xml collapse true
  <?xml version="1.0" encoding="ISO-8859-1"?> <job_chain orders_recoverable="yes" visible="yes"> <job_chain_node state="execute_plsql" job="JITL-PLSQL" next_state="sucess" error_state="error"/> <job_chain_node state="sucess"/> <job_chain_node state="error"/> </job_chain>

• Job

  Code Block
  language xml collapse true
  <?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="yes"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params >

...

...

• <!-- Parameter can be passed by task or as order param --> <param name="date_mask" value="YYYYMMDD_HH24MI"/>

...

...

• <!-- Database connection parameters i.e. db_url, db_user, db_password --> <include live_file="../common_settings/database/database_connection.params.xml" node=""/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> <run_time /> </job>

...

• Job

  Code Block
  language xml collapse true

• Order : get_order_date

<?xml version="1.0" encoding="ISO-8859-1"?>
<order  title="Calculate last booking date">
    <params >
        <param  name="command" value="C:/app/executables/plsql/get_last_booking_date.sql"/>
    </params>
    <run_time  let_run="no">
        <period  single_start="11:00"/>
    </run_time>
</order>

...

<?xml version="1.0" encoding="ISO-8859-1"?>
<order  title="Calculate Order Date">
    <params >
        <param  name="command" value="C:/app/executables/plsql/get_last_booking_date.sql"/>
    </params>
    <run_time  let_run="no">
        <period  single_start="08:00"/>
    </run_time>
</order>

How can results be used in subsequent jobs?

• The job makes use of the appropriate parameters in the order. 
• The first of these are static parameters, such as those described in the section "return parameters" of the template documentation. 
• At the same time these are dynamic parameters. The job anlyzes the output of the PL/SQL statements and extracts values by use of a regular expression that catches both names and values of these parameters. The regular expression is defined by the parameter variable_parser_reg_exp.

...

Return parameters created by JobSchedulerPLSQLJob

The JobScheduler automatically creates  following order parameters. Below described parameters will be available to subsequent job steps as order parameter. 

sql_error

• The parameter sql_error will contain all the error messages during PL/SQL execution. If no error occurs then the parameter will be empty.

std_out_output

• The parameter std_out_output will contain all the messages spooled to stdout by PL/SQL. 

    declare
      howmany NUMBER;
      p_id varchar2(20) := null;
    begin
      dbms_output.put_line('set variable1=value1');
      p_id := '12345';
      --
      -- If the job or order contains the parameter
      --
      -- table_name=scheduler_variables
      --
      -- then all following selects are substituted to 'select count(*) into howmany from scheduler_variables;'
      --
      select count(*) into howmany from $\{SCHEDULER_PARAM_table_name\};
      select count(*) into howmany from %table_name%;
      select count(*) into howmany from %TABLE_NAME%;
      select count(*) into howmany from $\{SCHEDULER_PARAM_TABLE_NAME\};
      select count(*) into howmany from $\{scheduler_param_table_name\};
      select count(*) into howmany from $\{sChEdUlEr_pArAm_tAbLe_nAmE\};
      --
      -- now put the results to the buffer
      -- JS will get the results from the buffer
      --
      dbms_output.put_line('The table %table_name% has ' || howmany || ' rows.');
      dbms_output.put_line('set howmany is ' || howmany);
      dbms_output.put_line('set variable1 is ' || p_id);
      dbms_output.put_line('set variable2 is value2');
    end;

Example: PL/SQL code

• If the job or order contains the parameter table_name with the value scheduler_variables then all following SQL "select" statements are substituted to: select count(*) into howmany from scheduler_variables;
• If the job is started in a job chain by an order then the following parameters are added to the order 
  • see the parameter variable_parser_reg_expr 
  • For this purpose, the output of the PL/SQL statement is parsed with the regular expression: ^SET\\s+([^\\s]+)\\s*IS\\s+(.*)$

From the above example the following parameters are returned:

• howmany=8
• variable1=12345
• variable2=value2

If the same parameter is set multiple times then the last value is used as the order parameter.

An example for a job xml file:

  <job>
     <params>
       <param name="command" value="select sysdate from dual" />
       <param name="db_url" value="jdbc:oracle:thin:@localhost:1521:XE" />
       <param name="db_user" value="test" />
       <param name="db_password" value="test" />
       <param name="variable_parser_reg_expr" value="^SET\\s+([^\\s]+)\\s*IS\\s+(.*)$" />
     </params>
     <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass" />
  </job>

Parameters used by JobSchedulerPLSQLJob

...

Parameter: command

• It is possible to define more than one PL/SQL statement in the command parameter. Such statements are then carried out in the order in which they are specified and have to be separated by a semicolon and a subsequent new line.
• Parameters are substituted in database statements. This is effected by using parameter names in the form §{param} at any given position in a statement. The parameter is then substituted by the value of the job parameter with the same name. Substitution takes place before database statements are executed.

Parameter: variable_parser_reg_expr

• The output of the PL/SQL statement will be parsed by this regular expression. The matches are available as order parameters.
• The regular expression has to specify to two groups: the first group for the parameter name and the second group for the value.

The prompt statement in SQL*Plus writes some output like this:

WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
prompt ready;
prompt foo bar;
prompt SET hello IS world;
exit;

The dbms_output.put_line() function in PL/SQL writes some output like this:

begin
  dbms_output.put_line('ready');
  dbms_output.put_line('Set hello IS world');
  dbms_output.put_line('fooh1. bar');
end;

Return parameters created by JobSchedulerPLSQLJob

The order parameters described below are automatically created and returned by the job:

...

Parameter: sql_error

• If during the execution of the PL/SQL statements one or multiple errors occur, then the error message will be stored in this order parameter. If no error occurs then the contents of the parameter will be empty.

Parameter: std_out_output

• The output of the PL/SQL statements to stdout is reported as the value of this parameter.