Page History

...

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.

These parameters are readable by all jobs in all job steps. The value of the parameter parameters can be changed modified by other subsequent jobs.

    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;

...

  <job order='no' ><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>

...

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 the output.Example:WHENEVER SQLERROR EXIT 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 the output. Example:begin 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:

 *variable_parser_reg_expr"^([h1. ]+)\\s*\\s+(.*)$" returns the order parameter "foh1. bar" 

Some remarks on regular expression, used in JobScheduler.
A regular expression is not awildcard. To get an impression of the differences one have a look on the meaning of the wildcard .txt, which will select all filenames with the filename-extension ".txt". A regular expression to match, e.g. works the same way, this "wildcard" must look like "^.\.txt$". That looks a little bit strange but it is much more flexible and powerfull on filtering filenames than the concept of wildcards, if one want to filter more complex names or pattern.
The general syntax of anregular expression, also referred to as regex or regexp, is describedhere. It is different to other RegExp definitions, e.g. as for Perl.
For further information on regular expressions seejava.util.regex.Pattern
Data-Type : SOSOptionRegExp
The default value for this parameter is ^SET
s+(\\s)\\s*IS
s(.)${*}.

Return parameter JobSchedulerPLSQLJob

The order parameter described below are returned by the job to the JobScheduler. JobSchedulerPLSQLJob

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.

Data-Type :