Work in progress
DRAFT
Introduction
The JITL JobSchedulerPLSQLJob job provide an standardized and parameterized interface to execute Oracle PL/SQLs. The JobScheduler offers out of the box capability to execute PL/SQLs, pass parameters to the PL/SQL or collect and passon the results of a PL/SQL execution to next job step as JobScheduler Order parameter. The JobSchedulerPLSQLJob can be used to execute existing PL/SQL files just by referring them in the command parameter.
Simple JITL PL/SQL Job Example
Following is the most basic example of the JITL JobSchedulerPLSQLJob. Following job is executing an PL/SQL unanimous code bloc. We are selecting current system date and displaying it on stdout as order_date.
Parameters
The JobSchedulerPLSQLJob needs following parameters.
Name | Title | Mandatory | Default | Example |
---|---|---|---|---|
command | PL/SQL statements to be executed | true |
|
|
db_url | JDBC connection string | true |
|
|
db_user | User name for database access | true |
| db username |
db_password | Password for database access | true |
| db password |
variable_parser_reg_expr | Regular expression to parse dbms_output and set order parameters for next job steps | false | ^SETs+(\\s)\\s*ISs(.*)$ |
|
command
- The PL/SQL code be
saved to a separate file i.e. get_order.sql , and subsequently sql file can be referred as the value of the job parameter "command", this is a recommended approach to achieve "separation of concern" in application architecture.
<param name="command" value="config/live/commn/sqls/get_order.sql"/>
PL/SQL code can also be specified as the value of the parameter command, entire PL/SQLcan be written as part of the Job.XML, this approach is preferred if PL/SQL code is very small and used by single job.
db_url
- JITL will need an standard JDBC database connection string i.e. jdbc:oracle:thin:@localhost:1521:XE
db_user
- DB Username which has appropriate database right to execute PL/SQL code.
db_password
- Password for the DB user defined in the db_user parameter.
variable_parser_reg_expr
- This parameter defines a reguler expression to parse dbms_output from PL/SQL execution and set the order parameters for subsequent job steps. i.e. the dbms ouput DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) displays the output on console SET order_date is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ will result as order parameter order_date="20140915".
Database connection settings as parameter file
It strongly recommend to create a db_connection parameter file i.e. database_connection.parameter.xml and store all the Database connection settings at common location. This approach enable user to manage settings at central location and refereed by multiple jobs.
It is also easy to maintain different settings for development, integration and production environment.
Following is the example of a database connection parameter file
Following is the example of a JITL job with database connection parameter stored in the external file. In following example an directory is being created in side the live folder as "common_settings/database".
Passing parameters to the PL/SQL
Jobscheduler order parameters can be passed to the PL/SQL. PL/SQL code can be parameterized by defining variables as ${SCHEDULER_PARAM_VARIABLE_NAME}. Variables can be set from environment variables, JobScheduler task parameter ( as described in the following example) or from JobScheduler order parameter.
parameters can also be defined with following syntax
- %parameter_name%
- ${SCHEDULER_PARAM_parameter_name}
- parameters are not case sensitive
PL/SQL script as external file
PL/SQL code can be defined directly inside the Job xml as value of command parameter or better PL/SQL scripts are stored at filesystem. JITL job can be configured to read PL/SQL script from filesystem by defining path of the script as value of command parameter i.e.
In Following example the PL/SQL code is save at filesystem in C:\app\executables\plsql\get_order_date.sql and subsequently refereed by the command parameter.
Passon Result of PL/SQL as parameter to subsequent job steps
JobScheduler jobs can create and update environment variable, JobScheduler Order parameters. The JobSchedulerPLSQLJob can also passon result of PL/SQL execution i.e. calculated date , calculated parameter from tables etc. By default the JobSchedulerPLSQL job defines a reguler expression to parse dbms_output from PL/SQL execution and set the order parameters for subsequent job steps. i.e. the dbms ouput DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) displays the output on console SET order_date is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ will result as order parameter order_date="20140915". All the dbms_output statements matching with reguler expression ^SETs+(\\s)\\s*ISs(.*)$ will be set as order_parameters.
Advance Topics
Generic Job to executed multiple PL/SQLs
The JobSchedulerPLSQLJob can be configured as generic node inside a JobChain and executable PL/SQL script can be defined as order parameter. Following is example of such a generic job
JobChain
- Job
- Order : get_order_date
- Order : get_last_booking_date
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 parameters can be modified by 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;
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
Name | Title | Mandatory | Default | Example |
---|---|---|---|---|
PL/SQL statements to be executed | false |
| select sysdate from dual | |
JDBC connection string | false |
| jdbc:oracle:thin:@localhost:1521:XE | |
User name for database access | false |
| ||
Password for database access | false |
| ||
Regular expression for parameter parsing | false | ^SET |
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:
Name | Title | Mandatory | Default |
---|---|---|---|
Error messages of the client or the server |
|
| |
Output to stdout |
|
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.