Page History

Table of Contents

	outlinh1. true
	outlinh1. true
1	printablefalse
2	stylh1. none
3	indent20px

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 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}

How can results be reused 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.

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 parametervariable_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:

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

  <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>

Parameter used by JobSchedulerPLSQLJob

...

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

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

language	xml
title	Simple JobSchedulerPLSQLJob
linenumbers	true
collapse	true

<?xml version="1.0" encoding="ISO-8859-1"?>
<job  title="Execute PL/SQL procedure" order="yes">
    <description >
        <include  file="jobs/JobSchedulerPLSQLJob.xml"/>
    </description>
    <params >
        <!-- Database connection parameters i.e. db_url, db_user, db_password -->
        <param  name="db_url"      value="jdbc:oracle:thin:@:1521:DORCL01"/>
        <param  name="db_user"     value="sos_scheduler"/>
        <param  name="db_password" value="sos"/>			
 
       <!-- 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>

Parameters

The JobSchedulerPLSQLJob requires the following parameters:

Name	Title	Mandatory	Default	Example
command	PL/SQL statements to be executed	true		`select sysdate from dual`
db_url	JDBC connection string	true		`jdbc:oracle:thin:@localhost:1521:XE`
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)\\sISs(.)$

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

DB Username which has necessary database permission for executing the PL/SQL code.

db_password

The password for the DB user defined in the db_user parameter.

variable_parser_reg_expr

This parameter defines a regular expression for 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

language	sql
title	database_connection.parameter.xml
collapse	true

 <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

language	xml
title	JobSchedulerPLSQLJob with database_connection_settings file
collapse	true

<?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

language	xml
title	Passing variables to the PL/SQL
collapse	true

<?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:

%parameter_name%
${SCHEDULER_PARAM_parameter_name}

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

language	xml
title	Passing variables to the PL/SQL
collapse	true

<?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

language	xml
title	plsql_job_param_script.job.xml
collapse	true

<?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

language	xml
title	plsql_job_param_command.job.xml
collapse	true

<?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.

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 db_url:

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

Parameter db_user:

database user
Data-Type :

Parameter db_password: The user password for accessing the database

The user password for accessing the database

database password
Data-Type :

Parameter 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 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
sql_error	Error Messages of the client or the server	false
std_out_output	Content of STDOUT	false

Parameter 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

Page History

Versions Compared

Old Version 5

New Version Current

Key

Description of JobSchedulerPLSQLJob - Execute PL/SQL procedure

What is the purpose of this job?

How is the script defined?

How can results be reused in subsequent jobs?

Example: PL/SQL code

Parameter used by JobSchedulerPLSQLJob

Introduction

A Simple JITL PL/SQL Job Example

Parameters

command

db_url

db_user

db_password

variable_parser_reg_expr

Saving Database Connection Settings in a Parameter File

Passing parameters to the PL/SQL

Parameters can also be defined with following syntax:

PL/SQL script as an External File

Passing PL/SQL results to subsequent job steps as parameters

Advanced Configuration

Generic job for executing multiple PL/SQLs

Standalone PL/SQL Jobs

PL/SQL Code as script

PL/SQL Code as command

Return parameters created by the JobSchedulerPLSQLJob

sql_error

std_out_output

See also:

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