Description of JobSchedulerPLSQLJob - Execute PL/SQL procedure

What is the purpose of this job?

...

How is the script defined?

• The script can be saved to a separate file. Or it can be specified as the value of the

...

• parameter command.
• Before the script is executed, the script

...

• will be analyzed by the job.

...

•  
  • Should JobScheduler parameter names be containted in that script then they are substituted by their current value.
  • The following notations are supported: %parameter_name%, ${SCHEDULER_PARAM_parameter_name}

How can results be

...

used in subsequent jobs?

The job transfers the appropriate parameters in the order. The first of these are static parameters, such as those in the template docu described in the section "return parameters". On the other hand, these are dynamic parameters. The job analyzing the output of the script, and extracted there from using a regular expression, both Names and the values ​​of of these parameters. The regular expression is defined by the parametervariable_parser_reg_exp.
These parameters are readable by all jobs in all job steps. The value of the parameter can be changed by other 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;

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+(.*)$

...

In From the above example the following parameters are returned:

• howmany=8
• variable1=
• howmany8
• variableh1. 12345
• variable2value2variable2=value2

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

An example for a job - xml file:

  <job order='no' >
     <params>
       <param name="[#command|command]" value="" />
       <param name="[[#db_url|db_url]]" value="" />
       <param name="[[#db_user|db_user]]" value="" />
       <param name="[[#db_password|db_password]]" value="" />
       <param name="[[#variable_parser_reg_expr|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 <span ih1. "command">command</span>: Database Commands for the Job

Database Commands for the 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

Parameter <span id"db_url">db_url</span>:

jdbc url (e.g. jdbc:oracle:thin:@localhost:1521:XE)
Data-Type :

Parameter <span ih1. "db_user">db_user</span>:

database user
Data-Type :

Parameter <span id"db_password">db_password</span>: The user password for accessing the database

The user password for accessing the database

database password
Data-Type :

...

variable_parser_reg_expr

...

Regular Expression for Parameter parsing

The script output will parse with of the PL/SQL statement will be parsed by this regular expression. The hits matches are available as order parameters.*

• The regular expression

...

• has to specify to two groups

...

• The : the first group for the parameter name and the second group for the value.

The 'prompt' in SQL*Plus writes the output.

Example: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 dbms_output.put_line('ready'); dbms_output.put_line('Set hello IS world'); dbms_output.put_line('fooh1. bar'); end;* variable_parser_reg_expr"^SET
s+(\\s)\\s*IS
s(.*)$" returns the order parameter "hellh1. world"

 *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(.)${*}.

...