Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Description of JobSchedulerPLSQLJob - Execute PL/SQL procedure
What is the purpose of this job?
The job executes PL/SQL code.
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 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.
Introduction
The JobSchedulerPLSQLJob JITL job provides a standardized and parameterized interface for executing Oracle PL/SQLs statements. The JobScheduler offers out of the box capability to execute PL/SQLs, passing parameters to the PL/SQL or collecting and passing on the results of a PL/SQL execution to next job step as a JobScheduler Order parameter. The JobSchedulerPLSQLJob can be used to execute existing PL/SQL files just by referring them in the command parameter.
A Simple JITL PL/SQL Job Example
The following example shows a basic example of the JobSchedulerPLSQLJob. It executes PL/SQL anonymous code blocks - selecting the current system date and displaying it on stdout as order_date.
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Execute PL/SQL procedure" order="yes">
<description >
<include file="jobs/JobSchedulerPLSQLJob.xml"/>
</description>
<params > | ||||||||
Code Block | ||||||||
| ||||||||
declare howmany NUMBER; p_id varchar2(20) := null; <!-- Database connection parameters i.e. db_url, db_user, db_password --> begin <param dbms_output.put_line('set variable1=value1');name="db_url" value="jdbc:oracle:thin:@:1521:DORCL01"/> p_id := '12345'; <param name="db_user" --value="sos_scheduler"/> -- If the<param job or order contains the parameter name="db_password" value="sos"/> <!-- PL/SQL Code -- table_name=scheduler_variables > <param name="command" -- value=" DECLARE -- then all following selects are substituted to 'select count(*) into howmany from scheduler_variables;' -- select count(*) into howmany from $\{SCHEDULER_PARAM_table_name\}; v_order_date DATE := SYSDATE; BEGIN SELECT SYSDATE INTO v_order_date FROM DUAL; DBMS_OUTPUT.PUT_LINE(' +++ select count(*) into howmany from %table_name%; +++'); select count(*) into howmany from %TABLE_NAME%; select count(*) into howmany from $\{SCHEDULER_PARAM_TABLE_NAME\}; DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date); DBMS_OUTPUT.PUT_LINE(' +++ 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:
Code Block | ||
---|---|---|
| ||
<job order='no' >
<params>
<param name="command" value="" />
<param name="db_url" value="" />
<param name="db_user" value="" />
<param name="db_password" value="" />
<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
...
...
PL/SQL statements to be executed
...
false
...
...
...
JDBC connection string
...
false
...
...
+++');
END;
"/>
<!-- dbms_output to JobScheduler Order parameter parser regex -->
<param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Parameters
The JobSchedulerPLSQLJob requires the 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 can be:
saved to a separate file such as get_order.sql . This file can subsequently be referred to as the value of the "command" job parameter. This is a recommended approach for achieving "separation of concern" in application architecture.
Code Block language sql <param name="command" value="config/live/commn/sqls/get_order.sql"/>
PL/SQL code can also be specified as the value of the command parameter, with the entire PL/SQL being written as part of the Job.XML. This approach is preferred if the PL/SQL code is very small and only used by a single job.
Code Block language sql <param name="command" value=" DECLARE v_order_date DATE := SYSDATE; BEGIN SELECT SYSDATE INTO v_order_date FROM DUAL; DBMS_OUTPUT.PUT_LINE(' +++ +++'); DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date); DBMS_OUTPUT.PUT_LINE(' +++ +++'); END; "/>
db_url
JITL needs a standard JDBC database connection string such as jdbc:oracle:thin:@localhost:1521:XE
db_user
...
User name for database access
...
false
...
DB Username which has necessary database permission for executing the PL/SQL code.
db_password
...
Password for database access
...
false
...
...
The password for the DB user defined in the db_user parameter.
variable_parser_reg_expr
...
This parameter defines a regular expression for
...
false
...
parsing the dbms_output from the PL/SQL execution and sets the order parameters for subsequent job steps. For example, 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".
Saving Database Connection Settings in a Parameter File
It strongly recommend that a db_connection parameter file such as database_connection.parameter.xml is used to store all the database connection settings in a common location. This approach enables the user to manage settings at central location which can then be reused by multiple jobs.
This approach also makes it easy to maintain different settings for development, integration and production environments.
The following shows an example database connection parameter file:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<params >
<param name="db_url" value="jdbc:oracle:thin:@:1521:DORCL01"/>
<param name="db_user" value="sos_scheduler"/>
<param name="db_password" value="sos"/>
<params > |
The next example shows a JITL job where the database connection parameters are stored in an external file. In this example a "common_settings/database" directory has been created inside the JobScheduler's live folder.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Execute PL/SQL procedure" order="no">
<description >
<include file="jobs/JobSchedulerPLSQLJob.xml"/>
</description>
<params >
<!-- Database connection parameters i.e. db_url, db_user, db_password -->
<include live_file="../common_settings/database/database_connection.params.xml" node=""/>
<!-- PL/SQL Code -->
<param name="command" value="
DECLARE
v_order_date DATE := SYSDATE;
BEGIN
SELECT SYSDATE
INTO v_order_date
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(' +++ +++');
DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date);
DBMS_OUTPUT.PUT_LINE(' +++ +++');
END;
"/>
<!-- dbms_output to JobScheduler Order parameter parser regex -->
<param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
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 such as ${SCHEDULER_PARAM_VARIABLE_NAME}. Variables can be set using environment variables, JobScheduler task parameters ( as described in the following example) or from JobScheduler order parameters.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Execute PL/SQL procedure" order="no">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<description >
<include file="jobs/JobSchedulerPLSQLJob.xml"/>
</description>
<params >
<!-- Database connection parameters i.e. db_url, db_user, db_password -->
<include live_file="../common_settings/database/database_connection.params.xml" node=""/>
<!-- Parameter can be passed by task or as order param -->
<param name="date_mask" value="YYYYMMDD_HH24MI"/>
<!-- PL/SQL Code -->
<param name="command" value="
DECLARE
v_order_date VARCHAR2(16) := SYSDATE;
BEGIN
/* recommended to set variables in the PL/SQL is with ${SCHEDULER_PARAM_VARIABLE_NAME} */
SELECT to_char(SYSDATE, '\${SCHEDULER_PARAM_DATE_MASK}' )
INTO v_order_date
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(' +++ +++');
DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date);
DBMS_OUTPUT.PUT_LINE(' +++ +++');
END; "/>
<!-- dbms_output to JobScheduler Order parameter parser regex -->
<param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Tip |
---|
Parameters can also be defined with following syntax:
Parameters are not case sensitive. |
Warning |
---|
When PL/SQL code is part of Job XML file, then parameters should be defined in the form \${SCHEDULER_PARAM_PARAMETER_NAME}. If PL/SQL code is read from file system, the parameter can be defined without the "\" |
PL/SQL script as an External File
PL/SQL code can be defined directly inside the Job xml as a command parameter value but is generally better stored on the file system. JITL jobs can be configured to read PL/SQL scripts from the file system by defining the script path as a value for the command parameter i.e.
In the following example the PL/SQL code is saved to the filesystem in C:\app\executables\plsql\get_order_date.sql and subsequently referenced using the command parameter.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Execute PL/SQL procedure" order="no">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<description >
<include file="jobs/JobSchedulerPLSQLJob.xml"/>
</description>
<params >
<!-- Database connection parameters i.e. db_url, db_user, db_password -->
<include live_file="../common_settings/database/database_connection.params.xml" node=""/>
<!-- Parameter can be passed by task or as order param -->
<param name="date_mask" value="YYYYMMDD_HH24MI"/>
<!-- PL/SQL script from filesystem -->
<param name="command" value="C:/app/executables/plsql/get_order_date.sql"/>
<!-- dbms_output to JobScheduler Order parameter parser regex -->
<param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Passing PL/SQL results to subsequent job steps as parameters
JobScheduler jobs can create and update JobScheduler Order parameters. The JobSchedulerPLSQLJob can also pass on the result of PL/SQL execution i.e. calculated dates, parameters calculated from tables, etc. By default the JobSchedulerPLSQL job defines a regular expression to parse dbms_output from the execution of PL/SQLs and sets order parameters for subsequent job steps. For example, the DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) dbms ouput displays the output on console; if SET order_date is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ and return the order_date="20140915" order parameter. All dbms_output statements matching the ^SETs+(\\s)\\s*ISs(.*)$ regular expression will be set as order_parameters.
Advanced Configuration
Generic job for executing multiple PL/SQLs
The JobSchedulerPLSQLJob can be configured as a generic node inside a job chain and executable PL/SQL script can be defined as an order parameter. The following example shows such a generic job. The job chain has a job node- execute_plsql - two orders - get_order_date and get_last_booking_date. Each order is scheduled to be executed at a different time. Both the orders are configured to use a different PL/SQL script file i.e. get_order_date.sql and get_last_booking_date.sql.
JobChain
Code Block language xml title JITL-PLSQL.job_chain.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 title JITL-PLSQL.job.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=""/> <!-- dbms_output to JobScheduler Order parameter parser regex --> <param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> <run_time /> </job>
Order : get_order_date
Code Block language xml title JITL-PLSQL,get_order_date.order.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate Order Date"> <params > <!-- PL/SQL script file --> <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>
Order : get_last_booking_date
Code Block language xml title JITL-PLSQL,get_last_order_date.order.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate last booking date"> <params > <!-- PL/SQL script file --> <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>
Standalone PL/SQL Jobs
If PL/SQL code needs to be parameterized by a job parameter the syntax for parameter substitute is different compare to order jobs.
Since the syntax of suffixing an order parameter for SCHEDULER_PARAM is not required the parameter name can directly be substituted in the PL/SQL code.
See the following example for two variant for standalone PL/SQL code.
PL/SQL Code as script
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job order="no" title="test">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<params >
<param name="testparam" value="test"/>
<param name="db_class" value="SOSOracleConnection"/>
<param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/>
<param name="db_url" value="jdbc:oracle:thin:@8of9:1521:TEST"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
</params>
<script language="java" java_class_path="" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass">
<![CDATA[
BEGIN
INSERT INTO T_SOS_TEST VALUES ('${testparam}');
END;
]]>
</script>
<run_time />
</job>
|
PL/SQL Code as command
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job order="no" title="test">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<params >
<param name="testparam" value="test_command"/>
<param name="db_class" value="SOSOracleConnection"/>
<param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/>
<param name="db_url" value="jdbc:oracle:thin:@8of9:1521:TEST"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
<param name="command" value="BEGIN INSERT INTO T_SOS_TEST VALUES ('\${testparam}'); END;"/>
</params>
<script language="java" java_class_path="" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Return parameters created by the JobSchedulerPLSQLJob
The JobScheduler automatically creates the following order parameters, which will be available to subsequent job steps as order parameters.
sql_error
- The sql_error parameter contains all the error messages generated during the PL/SQL execution. This parameter will be empty if no errors occur.
std_out_output
- The std_out_output parameter contains all the messages spooled to stdout by PL/SQL.
See also:
- How to work with PL/SQL and the Oracle DBMS
- How to run Oracle Stored Procedures using PL/SQL
- The JobSchedulerPLSQLJob JITL job documentation
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' 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"
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
s+(\\s)\\s*IS
s(.)${*}.
Return parameter JobSchedulerPLSQLJob
The order parameter described below are returned by the job to the JobScheduler. JobSchedulerPLSQLJob
...
| Name | title | mandatory | default |
---|---|---|---|---|
Error Messages of the client or the server | false |
| ||
Content of STDOUT | false |
|
Parameter <span id"sql_error">sql_error</span>: 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 <span id="std_out_output">std_out_output</span>: Content of STDOUT
Content of STDOUT
The output of the process into STDOUT is reported as the value of this parameter.
Data-Type :