Page History

...

The script can be saved to a separate file. Or it can be specified as the value of the parameter command parametercommand.
Before the script is executed, the script is modified by the job. All directly specified name of JobScheduler parameters are substituted by their current value. The following notations are supported: %parameter_name%, ${SCHEDULER_PARAM_parameter_name}

...

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 these parameters. The regular expression is defined by the parameter variableparametervariable_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.

Code Block

    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_namh1. scheduler_variables" then all following selects 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 as additional parameters (see the parameter variableparametervariable_parser_reg_expr). For this purpose, the output of the script is parsed with the regular expression "^SET
s+(\\s)\\s*IS
s(.*)$".
In the above example the following parameters are returned: howmanh1. 8variable112345variableh1. value2

howmany8
variableh1. 12345
variable2value2

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

An example for a job-xml file:

Code Block

language	html/xml


  &lt;job order='no' &gt;
     &lt;params&gt;
       &lt;param name="[[#command|command]]" value="" /&gt;
       &lt;param name="[[#db_url|db_url]]" value="" /&gt;
       &lt;param name="[[#db_user|db_user]]" value="" /&gt;
       &lt;param name="[[#db_password|db_password]]" value="" /&gt;
       &lt;param name="[[#variable_parser_reg_expr|variable_parser_reg_expr]]" value="^SET\\s+([^\\s]+)\\s*IS\\s+(.*)$" /&gt;
     &lt;/params&gt;
     &lt;script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass" /&gt;
  &lt;/job&gt;

Parameter used by JobSchedulerPLSQLJob

Panel

	title titlh1. List of parametertitle
	titlh1. List of parameter
1	borderStylh1. dashedborderStyledashed
2	borderColor#cccborderColoh1. #ccc
3	bgColoh1. #fffbgColor#fff

	Name	title	mandatory	default
command	Database Commands for the Job	false
db_url		false
db_user		false
db_password	The user password for accessing the database	false
variable_parser_reg_expr	Regular Expression for Parameter parsing	false	^SET s+(\\s)\\sIS s(.)$

Parameter <span

...

ih1. "command">command: Database Commands for the Job

...

Database Commands for the Job

It is possible to define more than one instruction in the COMMAND field. Such instructions are then carried out in the order in which they are written and must be separated by a semicolon and a subsequent new line. Parameters can be replaced in database commands. This is done by the addition of a variable in the form §{param} at any given point in a command. This variable is then given the value of the instruction parameter with the name param before execution.
Data-Type :

Parameter <span

...

id"db_url">db_url:

...

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

Parameter <span

...

ih1. "db_user">db_user:

...

database user
Data-Type :

Parameter <span

...

id"db_password">db_password: The user password for accessing the database

...

The user password for accessing the database

database password
Data-Type :

Parameter <span

...

ih1. "variable_parser_reg_expr">variable_parser_reg_expr: Regular Expression for Parameter parsing

...

Regular Expression for Parameter parsing

...

The script output will parse with this regular expression. The hits are available as order parameters.* The regular expression must have two groups.

The

...

first

...

group

...

for

...

the

...

parameter

...

name

...

and

...

the

...

second

...

for

...

the

...

value.

The 'prompt' in SQL*Plus writes the output. Example:WHENEVER SQLERROR EXIT SQL.SQLCODE WHENEVER OSERROR EXIT FAILURE prompt ready; prompt fooh1. 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('foo fooh1. bar'); end;* variable_parser_reg_exph1. expr"^SET\\
s+([^\\s]+)\\s*IS\\
s+(.*)$" returns the order parameter "helloworld"hellh1. world"

No Format
*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\\^SET
s+([^\\s]+)\\s*IS\\
s+(.*)${*}.

Return parameter JobSchedulerPLSQLJob

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

Panel

	titlh1. title List of parametertitlh1.
	title List of parameter
1	borderStyledashedborderStylh1. dashed
2	borderColoh1. #cccborderColor#ccc
3	bgColor#fffbgColoh1. #fff

	Name	title	mandatory	default
sql_error	Error Messages of the client or the server	false
std_out_output	Content of STDOUT	false

Parameter <span

...

id"sql_error">sql_error: Error Messages of the client or the server

...

Error Messages of the client or the server

If during the execution of the code one (or more) errors occurs, the error-text will be stored on this variable in the order. If no error occurs then the contents of the variable will remain empty.
Data-Type : SOSOptionString
Use together with parameter:

Parameter std_out_output: Content of STDOUT

...

Content of STDOUT

The output of the process into STDOUT is reported as the value of this parameter.
Data-Type :

Space shortcuts

Page tree

Versions Compared

Old Version 2

New Version 3

Key

Example: PL/SQL code

Parameter used by JobSchedulerPLSQLJob

Parameter <span

ih1. "command">command</span>: Database Commands for the Job

Database Commands for the Job

Parameter <span

id"db_url">db_url</span>:

Parameter <span

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

Parameter <span

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

The user password for accessing the database

Parameter <span

ih1. "variable_parser_reg_expr">variable_parser_reg_expr</span>: Regular Expression for Parameter parsing

Regular Expression for Parameter parsing

Return parameter JobSchedulerPLSQLJob

Parameter <span

id"sql_error">sql_error</span>: Error Messages of the client or the server

Error Messages of the client or the server

Parameter <span id="std_out_output">std_out_output</span>: Content of STDOUT

Content of STDOUT