Table of Contents

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

Introduction

The JobSchedulerPLSQLJob JITL job provides a standardized and parameterized The JITL PLSQLJob provides a parameterizable interface for executing Oracle® PL/SQLs SQL statements. The JobScheduler

JS7 offers out-of-the box capability to execute -box capabilities:

for executing PL/

...

SQL,
for passing

...

arguments to

...

PL/SQL

...

,
for collecting and passing on the results of

...

PL/SQL execution to subsequent jobs,
for using a JS7 - Credential Store.

The PLSQLJob next job step as a JobScheduler Order parameter. The JobSchedulerPLSQLJob can be used to execute existing files that include 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

...

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_password

...

Password for database access

...

true

...

variable_parser_reg_expr

...

Regular expression to parse dbms_output and

set order parameters for next job steps

...

statements.

The job supports encryption of database credentials if used with a Hibernate configuration file, see JS7 - How to encrypt and decrypt Database Credentials.

Changes

Starting from Release 2.0.0 the PLSQLJob
- is implemented on top of the JDBC Interface of the Oracle® JDBC Driver,
- with a commit operation being performed by the Oracle® JDBC Driver when a connection is closed without an explicit commit or rollback.
Starting from Release 2.7.1 the PLSQLJob
- is implemented using Hibernate,
- with no commit or rollback operation being performed when the connection is closed.

Starting from Release 2.7.2 the PLSQLJob

makes use of the Hikari connection pool that ships with Hibernate,
performs a rollback operation when a connection is closed,
closes the connection in the event that a cancel/force or suspend/force operation is performed on the order.

Jira

server	SOS JIRA
columnIds	issuekey,summary,issuetype,created,updated,duedate,assignee,reporter,priority,status,resolution
columns	key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution
serverId	6dc67751-9d67-34cd-985b-194a8cdc9602
key	JITL-743

Usage

When defining the job either:

invoke the Wizard that is available from the job properties tab in the Configuration view and select the JITL PLSQLJob and respective arguments from the Wizard

or

specify the JITL job class and com.sos.jitl.jobs.db.oracle.PLSQLJob Java class name and add arguments as explained in the documentation below.

Example

Download: dbPLSQLExecution.json

The following example explains the basic use of the PLSQLJob. The job executes PL/SQL anonymous code blocks - selecting the current system date and writing it to the stdout channel.

Image Added

Documentation

The Job Documentation including the full list of arguments can be found under: https://www.sos-berlin.com/doc/JS7-JITL/PLSQLJob.xml

The PLSQLJob supports the following arguments:

Name	Purpose	Required	Default Value	Example
`command`	PL/SQL statements to be executed	One of these arguments has to be specified		`DECLARE` `return_cursor SYS_REFCURSOR;` `BEGIN` `OPEN return_cursor FOR 'SELECT FIRST_NAME, LAST_NAME, AGE FROM EMPLOYEES';` `END;`
`command_script_file`	PL/SQL statements from a file	One of these arguments has to be specified		`/home/sos/some_script.sql`
`db_url`	JDBC connection string	Either a DB URL, location of a Hibernate configuration file or a Credential Store reference is used. User and password for database access can be specified by arguments with the same name and can be omitted if an Oracle Wallet is used or can be specified with the Hibernate configuration file, from a Job Resource or from a Credential Store Reference.		`jdbc:oracle:thin:@localhost:1521:XE`
`db_user`	User name for database access			`scott`
`db_password`	Password for database access			`tiger`
`hibernate_configuration_file`	Replaces `db_url, db_user, db_password` arguments from a hibernate configuration file.			`./config/private/hibernate.cfg.xml`
`variable_parser_reg_expr`	Regular expression to parse output from the `DBMS_OUTPUT` package and to set order variables for next jobs	no	`^SETs+(\\s)\\sISs(.)$`
`result_set_as_variables`	Specifies how a result set is forwarded to order variables for subsequent jobs: `false`: no result set is processed (default) `column_value`: for the first record of the result set from each column name a variable is created that holds the column value. `name_value`: the first two columns of the result set will be added to order variables. The values of the first column of each record included with the result set will become the variable names, the values of the second column will become the variable values. `csv`: the column names will be written to the first line of the file specified with the `result_file` argument, any column values of the result set will be written to subsequent lines of this file. Column names and values will be enclosed with double quotes and will be separated by commas. Example: `"FIRST_NAME","LAST_NAME","AGE" "John","Doe","30"` `xml:` rows are created as a sequence of `<ROW>` elements of the `<RESULTSET>` root element. The column names will be created as XML element names. XML output is written to the file specified with the `result_file` argument. Example: `<?xml version="1.0" encoding="UTF-8"?>` `<RESULTSET>` `<ROW>` `<FIRST_NAME><![CDATA[John]]></FIRST_NAME>` `<LAST_NAME><![CDATA[Doe]]></LAST_NAME>` `<AGE><![CDATA[30]]></AGE>` `</ROW>` `</RESULTSET>` `json:` the column names will be written as an array of JSON objects to the file specified with the `result_file` argument, any column values of the result set will be written as object properties to this file. Example: `[` `{` `"FIRST_NAME" : "John",` `"LAST_NAME" : "DOE",` `"AGE" : 30` `}` `]`	no
`result_file`	Specifies the path to a result file if the `resultset_as_variables` argument is used with one of the values `csv, xml, json`. An absolute path can be specified and a relative path can be used that starts from the Agent's working directory.	no
`credential_store_file`	Location of a credential store database (*.kdbx)	false		`./config/private/jobs.kdbx`
`credential_store_key`	Location of a credential store key file (*.key)	false		`./config/private/jobs.key`

The PLSQLJob can be used with a credential store to hold sensitive arguments. For use of the credential_store_* arguments see JS7 - Use of Credential Store with JITL Jobs.

Argument: `command`

PL/SQL code can be specified as the value of the command argument.

The following PL/SQL code creates output that can be parsed by use of the variable_parser_reg_expr argument to create workflow variables:

Code Block

language	sql
title	PL/SQL procedure returning parsable output

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;

The following PL/SQL code creates a cursor that can be used to store the result set to a file when specifying the result_set_as_variables argument with one of the values csv, xml or json. In addition, the result_file argument is used to specify the file which selected data is written to.

Code Block

language	sql
title	PL/SQL procedure returning result set cursor

DECLARE
  return_cursor SYS_REFCURSOR;
BEGIN
  OPEN return_cursor FOR 'SELECT FIRST_NAME, LAST_NAME, AGE FROM EMPLOYEES';
END;

Argument: `command_script_file`

PL/SQL code can be used from from a file that is assigned this argument, for example:

Code Block

language	sql

./config/get_order.sql

Argument: `db_url`

The job requires a standard JDBC database connection string such as jdbc:oracle:thin:@localhost:1521:XE

Argument: `db_user`

This argument specifies the user account for a database schema which has been assigned the required privileges to execute the PL/SQL code.

Argument: `db_password`

This argument specifies the password for the user account given with the db_user argument.

Argument: `variable_parser_reg_expr`

This argument specifies a regular expression for parsing the output created by the the DBMS_OUTPUT package from the PL/SQL statements. For each match an order variable is created that is available for subsequent jobs. For example, the statement:

EXEC DBMS_OUTPUT.PUT_LINE( 'SET order_date IS ' || TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') );

writes the following output to the console:

SET order_date is 2021-05-04

The output will be parsed by the regular expression:

^SETs+(\\s)\\s*ISs(.*)$

and will result in an order variable:

order_date = "2021-05-04"

Subsequent jobs can make use of the $order_date variable.

Manage Arguments

Arguments from Job Resources

For example, database connection settings can be added to JS7 - Job Resources in order to be shared by a number of jobs. This allows central management of connections settings and limits visibility of passwords with individual database jobs.

Image Added

The Job Resource is then assigned the job or workflow. As the same argument names have been used as for the Pl/SQL job they are automatically mapped to the job.

Passing Arguments to PL/SQL Code

Arguments can be passed to PL/SQL statements. Such arguments can be added to the PL/SQL code and will be replaced at run-time. The syntax for arguments includes stating ${VARIABLE} - such variables are replaced from any location in the PL/SQL script code. For example, a job argument or order variable with the name $dateFormat specifying the output format of a date can be added to PL/SQL script code like this:

Code Block

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

DECLARE 
    v_order_date VARCHAR2(16) := SYSDATE;       
BEGIN 
    /* pass variables to PL/SQL by using the syntax: ${VARIABLE} */
    SELECT to_char(SYSDATE, '${dateFormat}' )    
      INTO v_order_date       
      FROM DUAL;  

      DBMS_OUTPUT.PUT_LINE(' +++              +++');
      DBMS_OUTPUT.PUT_LINE('

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 above example can be used:

for PL/SQL code included as the value of the command argument.
for PL/SQL code included with a script file that is referenced by the command_script_file argument.

Manage Return Values

Passing PL/SQL results to subsequent instructions and jobs

JS7 jobs can create return values that are available for later instructions and jobs in a workflow. The PLSQLJob can pass on the result of PL/SQL execution, e.g. calculated dates, by use of the DBMS_OUTPUT package.

This package allows writing to the stdout channel that is checked by the PLSQLJob for patterns which indicate return values. The output is parsed by the PLSQLJob using a regular expression that is specified by the argument variable_parser_reg_expr. This defaults to ^SETs+(\\s)\\s*ISs(.*)$.

The following line of Pl/SQL code:

Code Block

language	sql
title	Example how to specify a return variable

EXEC DBMS_OUTPUT.PUT_LINE( 'SET order_date IS ' || TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') );

results in output such as:

Code Block

language	sql
title	Example for a return variable

SET order_date IS 2021-05-04

The output will be parsed by the regular expression:

^SETs+(\\s)\\s*ISs(.*)$

and will result in an order variable:

$order_date = "2021-05-04"

Subsequent instructions and jobs can make use of the $order_date variable.

Return Variables automatically available from the PLSQLJob

This job automatically creates the following order variables which are available to subsequent instructions and jobs.

Return Variable: `sql_error`

The sql_error order variable contains the error messages generated during PL/SQL execution. This variable will be empty if no errors occur.

Return Variable: `std_out_output`

The std_out_output order variable contains the messages spooled to the stdout channel by PL/SQL.

Cancellation

The following operations are available:

The cancel operation is applied to the order, not to the PLSQLJob. In case of cancellation the job will continue until completion and the order will be set to the failed state.
The cancel/force operation is applied to the order and to the job. The PLSQLJob will be forcibly terminated, transactions are rolled back, the database connection will be closed and the DBMS session will be terminated. The order will be set to the failed state.

Further Resources

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.

Space shortcuts

Page tree

Page History

Versions Compared

Old Version 1

New Version Current

Key

Introduction

A Simple JITL PL/SQL Job Example

Parameters

Changes

Usage

Example

Documentation

Argument: command

Argument: command_script_file

Argument: db_url

Argument: db_user

Argument: db_password

Argument: variable_parser_reg_expr

Manage Arguments

Arguments from Job Resources

Passing Arguments to PL/SQL Code

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

Manage Return Values

Passing PL/SQL results to subsequent instructions and jobs

Return Variables automatically available from the PLSQLJob

Return Variable: sql_error

Return Variable: std_out_output

Cancellation

Further Resources

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:

Argument: `command`

Argument: `command_script_file`

Argument: `db_url`

Argument: `db_user`

Argument: `db_password`

Argument: `variable_parser_reg_expr`

Return Variable: `sql_error`

Return Variable: `std_out_output`