Page History

...

The JobSchedulerSQLPlusJob JITL job provides a standardized and parameterized interface for executing Oracle SQL*Plus scripts. The JobScheduler offers out of the box capability to execute SQL scripts, passing parameters to the SQL*Plus script or collecting and passing on the results of a script execution to next job step as a JobScheduler Order parameter. The JobSchedulerSQLPlusJob can be used to execute existing SQL*Plus scripts by referring them in the command parameter.

A Simple JITL

...

SQL*Plus Job Example

The following example shows a basic example of the JobSchedulerSQLPlusJob . It executes a simple SQL Statment - selecting the current system date and displaying it on stdout as order_date.

Code Block

language	xml
title	Simple JobSchedulerPLSQLJobJobSchedulerSQLPlusJob
linenumbers	true
collapse	true

<?xml version="1.0" encoding="ISO-8859-1"?>

<job  title="ExecuteStart PL/SQL procedure*Plus client and execute a sql*plus script" order="no" name="yesSQLPlusExampleSimple">
    <description<settings >
        <include  file="jobs/JobSchedulerPLSQLJob.xml"/><log_level ><![CDATA[debug1]]></log_level>
    </description>settings>
    <params<description >
        <!-- Database connection parameters i.e. db_url, db_user, db_password --<include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>
    <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>
   <!-- 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:

 <script  language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass">
        <![CDATA[
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
SELECT TO_CHAR(SYSDATE,'dd.mm.yyyy DAY') FROM DUAL;
        ]]>
    </script>
    <run_time />
</job>

Parameters

The JobSchedulerSQLPlusJob requires the following parameters:

#	Name	Title	Mandatory	Default	Example
1	command_script_file	SQL script to be executed	true		`c:\app\bin\sqls\get_upd_count.sql`
2	db_url	Database service or Instance name	true		DORCL01
3	db_user	User name for database access	true		db username
4	db_password	Password for database access	true		db password
5	shell_command	SQL plus client name	true		sqlplus
6	variable_parser_reg_expr	Regular expression to parse output and set order parameters for next job steps	false	^SETs+(\\s)\\sISs(.)$
7	Command_Line_options	SQL*Plus command line options	false		-S -L
8	ignore_ora_messages	Ignore ora error messages	false
9	ignore_sp2_messages	ignore sp2 error messages	false
10	include_files	Code blocks should be executed before SQL	false
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 script 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_script_file" 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;
	"/>

...

Space shortcuts

Page tree

Versions Compared

Old Version 2

New Version 3

Key

A Simple JITL

SQL*Plus Job Example

Parameters

Parameters

command