Page History

Introduction 

The JobSchedulerSQLPlusJob SOSSQLPlusJob 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  The SOSSQLPlusJob 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 SOSSQLPlusJob . It executes a simple SQL Statment  - selecting the current system date and displaying it on stdout as order_date.

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

<job  title="Start SQL*Plus client and execute a sql*plus script" order="no" name="SQLPlusExampleSimple">
    <settings >
        <log_level ><![CDATA[debug1]]></log_level>
    </settings>
    <description >
        <include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>
    <params >
        <param  name="db_url" value="DORCL01"/>
        <param  name="db_user" value="sos_scheduler"/>
        <param  name="db_password" value="sos"/>
    </params>
    <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 SOSSQLPlusJob requires the following parameters:

command_script_file

• The SQL 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_script_file" value="config/live/commn/sqls/get_order.sql"/>

  • SQL code can also be specified as the value of the command parameter, with the entire PL/SQL being written CDATA tag as part of the Job.XML. This approach is preferred only if the PL/ SQL code  code is very small and only used by a single job.

    Code Block
    language sql
    <param<script namelanguage="commandjava" value=" DECLAREjava_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass"> v_order_date DATE := SYSDATE; BEGIN <![CDATA[ WHENEVER SQLERROR EXIT SELECT SYSDATESQL.SQLCODE INTO v_order_date FROM DUAL; WHENEVER OSERROR EXIT FAILURE DBMS_OUTPUT.PUT_LINE(' +++ SELECT TO_CHAR(SYSDATE,'dd.mm.yyyy DAY') FROM DUAL; ]]> +++'); 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

• • </script>

db_url

For SQLPlus job db_url is only the Oracle DB service name or instance name

db_user

DB Username which has necessary database permission to execute SQL script.

db_password

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

shell_command

The value of this parameter specifies the name of SQL*Plus client. The SQL sctipt will be processed by the client. This parameter is useful when executing legacy SQL code i.e. Oracle 9i, 10g

variable_parser_reg_expr 

By default the SOSSQLPlusJob job defines a regular expression to parse console output from the execution of SQL Script and sets order parameters for subsequent job steps. For example, the SQL*Plus client stdout  prompt SET period_prev IS ${PN_YEAR_PREV_PERIOD}  displays the output on console; if  period_prev is 20140915, it will be parsed by regular expression ^SETsThis 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$ and return the period_prev="20140915" order parameter.

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

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

  All stdout statements matching the ^SETs+(\\s)\\s*ISs(.*)$ regular expression will be set as order_parameters.

command_Line_options

 The value of this parameter specifies all the command line parameters required by the SQL*Plus client i.e.  -V SQL*Plus version , -S silent  no banner , -M automatic HTML output etc.

ignore_ora_messages

Its not always the case when you want to ignore error messages but if need be arise, value of the parameter ignore_ora_message will specify which ORA messages Jobscheduler can ignore.

ignore_sp2_messages

If there are some expected Oracle SP2 errors which you want to ignore, use a comma separated list of all the SP2 errors as value for the ignore_sp2_messages parameter.

include_files

If you have some common code need to be executed before SQL*Plus code, you can define with include_files parameter. Multiple files can be specified, seperated by semicolons. 

Saving Database Connection Settings in a Parameter File

It strongly recommend that a db_connection parameter file such as database_connection_sqlplus.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:

 <params >
    <param  name="db_url"      value="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. 

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

<job  title="Start SQL*Plus client and execute a sql*plus script" order="no">
    <settings >

<?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 --<log_level ><![CDATA[debug9]]></log_level>
    </settings>
    <description >
        <param  name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$<include  file="jobs/SOSSQLPlusJob.xml"/>
    </params>
description>
    <params >
        <script<include  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClassfile="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/>
    <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.

<?xml version="1.0" encoding="ISO-8859-1"?>
<job  title="Execute PL/SQL procedure" order="no">
    <settings params>
    <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>
    <log<run_level ><![time />
</job>

Passing parameters to the SQL Script

JobScheduler order parameters can be passed to the SQL Script. SQL Script 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.

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

<job  title="Start SQL*Plus client and execute a sql*plus script" order="no" name="SQLPlusExample3"CDATA[debug9]]></log_level>
    </settings>
    <description >
        <include  file="jobs/JobSchedulerPLSQLJob.xml"/>
    </description>
    <params >
	
	    <!-- Database connection parameters i.e. db_url, db_user, db_password -->
    <settings >
   <include     live_file="../common_settings/database/database_connection.params.xml" node=""/>
		
	<log_level ><![CDATA[debug9]]></log_level>
    <!-- Parameter can be passed by task or as order param -->
		<param  name="date_mask" value="YYYYMMDD_HH24MI"/>
	   /settings>
    <description >
        <include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>
   <!-- PL/SQL Code --> 
 <params >
        <param  name="commandBCY_DATE" 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_date28.07.2014"/>
        <param  name="PN_YEAR_PERIOD" value="2014"/>
        <param  name="PN_YEAR_PREV_PERIOD" value="2013"/>
        
			<include  FROM DUAL;  
			DBMS_OUTPUT.PUT_LINE(' +++   file="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/>
    </params>
           +++');
			DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date);
			DBMS_OUTPUT.PUT_LINE(' +++<script  language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass">
        <![CDATA[
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
column end_date  +++');
		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"/>new_value BCY_DATE
column period new_value PN_YEAR_PERIOD
column period_prev new_value PN_YEAR_PREV_PERIOD
select '0' as end_date from dual;
prompt SET end_date IS ${BCY_DATE};
/
select '0' as period from dual;
prompt SET period IS ${PN_YEAR_PERIOD};
/
select '0' as period_prev from dual;
prompt SET period_prev IS ${PN_YEAR_PREV_PERIOD};
/
select sysdate from dual;
prompt SET end_date IS 1;
exit;
        ]]>
    </script>
    <run_time />
</job>

...

SQL Script as an External File 

PL/SQL code script can be defined directly inside the Job xml as value ofa 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 Following the following example the PL/SQL code is save at saved to the filesystem in C:\app\executables\plsqlsqls\get_orderbooking_datecycle.sql sql and subsequently refereed by the command  referenced using the command_script_file  parameter.

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

<job  title="Start SQL*Plus client and execute title="Execute PL/SQL procedurea sql*plus script" order="no">
    <settings >
        <log_level ><![CDATA[debug9]]></log_level>
    </settings>
    <description >
        <include  file="jobs/JobSchedulerPLSQLJob.xml"/>
    </description>
    <params >
	
	/log_level>
    <!-- Database connection parameters i.e. db_url, db_user, db_password --/settings>
    <description >
        <include  live_file="../common_settings/database/database_connection.params.xml" node=""/>
		
	    <!-- Parameter can be passed by task or as order param -->
		jobs/SOSSQLPlusJob.xml"/>
    </description>
    <params >
        <param  name="dateBCY_maskDATE" value="YYYYMMDD_HH24MI28.07.2014"/>
		
        <param  <!-- PL/SQL script from filesystem -->
	name="PN_YEAR_PERIOD" value="2014"/>
        <param  name="commandPN_YEAR_PREV_PERIOD" value="C:/app/executables/plsql/get_order_date.sql2013"/>       	
 


        <!-- dbms_output to JobScheduler Order parameter parser regex --> <param  name="command_script_file" value="C:\app\executables\sqls\get_booking_cycle.sql"/>

        <param<include  namefile="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/>
    </params>
    <script  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClassSOSSQLPlusJobJSAdapterClass"/>
    <run_time />
</job>

Passing

...

SQL script results to subsequent job steps as parameters

JobScheduler jobs can create and update JobScheduler Order parameters. The JobSchedulerPLSQLJob  can SOSSQLPlusJob can also pass on the result of PL/ SQL script execution i.e. calculated dates, parameters calculated from tables, etc. By default the JobSchedulerPLSQL JobSchedulerSQLPlus job defines a regular expression to parse dbms_console output from the execution of PL/SQLs SQL Script and sets order parameters for subsequent job steps. For example, the DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) dbms ouput SQL*Plus client stdout  prompt SET period_prev IS ${PN_YEAR_PREV_PERIOD}  displays the output on console; if  SET orderperiod_date prev is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ and return the orderperiod_dateprev="20140915" order parameter.  All dbms_output stdout statements matching the ^SETs+(\\s)\\s*ISs(.*)$ regular expression will be set as order_parameters.

...

Advanced Configuration

Generic job for executing multiple

...

SQL scripts

The JobSchedulerPLSQLJob SOSSQLPlusJob 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 sql - two orders - get_orderbooking_date cycle and getcount_lasttrx_booking_daterecords . 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_orderbooking_datecycle.sql and get count_lasttrx_booking_daterecords.sql. 

• JobChain

  Code Block
  language xml title JITL-PLSQLSQLPlus.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_plsqlsqlplus_script" job="JITL-PLSQLSQLPlus" 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-PLSQLSQLPlus.job.xml collapse true
  <?xml version="1.0" encoding="ISO-8859-1"?> <job title="ExecuteStart PL/SQL procedureSQL*Plus client and execute a sql*plus script" order="yes"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/JobSchedulerPLSQLJobSOSSQLPlusJob.xml"/> </description> <params > <!-- Parameter can be passed by task</description> or as order param<params --> <param name="dateBCY_maskDATE" value="YYYYMMDD_HH24MI28.07.2014"/> <param <!-- Database connection parameters i.e. db_url, db_user, db_password -- name="PN_YEAR_PERIOD" value="2014"/> <include<param live_filename="../common_settings/database/database_connection.params.xml" node="PN_YEAR_PREV_PERIOD" value="2013"/>    <!-- dbms_output to JobScheduler Order parameter parser regex -- <param name="command_script_file" value="C:\app\executables\sqls\get_booking_cycle.sql"/> <param<include namefile="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml" node=""/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClassSOSSQLPlusJobJSAdapterClass"/> <run_time /> </job>

• Order : get get_orderbooking_datecycle

  Code Block
  language xml title JITL-PLSQLSQLPlus,get_orderbooking_datecycle.order.xml collapse true
  <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate Order DateExecute get_booking_cycle.sql"> <params > <!-- PL/SQL script file<params --> <param name="command_script_file" value="C:/\app/executables/plsql/\executables\sqls\get_last_booking_datecycle.sql"/> </params> <run_time let_run="no"> <period single_start="08:00"/> </run_time> </order>

• Order : get_lasttrx_booking_datecount

  Code Block
  language xml title JITL-PLSQLSQLPlus,get_lasttrx_order_datecount.order.xml collapse true
  <?xml version="1.0" encoding="ISO-8859-1"?> <order title="CalculateExecute last booking date"> <params > <!-- PL/SQL script file --get_booking_cycle.sql"> <params > <param name="command_script_file" value="C:/\app/executables/plsql/\executables\sqls\get_lasttrx_booking_datecount.sql"/> </params> <run_time let_run="no"> <period single_start="1108:00"/> </run_time> </order>

Return parameters created by the

...

SOSSQLPlusJob

The JobScheduler automatically creates the following order parameters, which will then 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 script execution. This parameter will be empty if no errors occurempty if errors do not occur.

std_out_output

• The std_out_output parameter contains all the messages spooled to stdout by SQL Script.

std_

...

err_output

• The std_out_output parameter contains all the messages spooled to stdout by PL/ SQL . Script.

exit_code

• The exit_code will be 0 If script ended without any error 
• If std_error_output is not empty the exit code will be set to 99
• If either SP2 or an ORA- error occurs the exit code will be set to 98

See also:

• How to work with PL/SQL and the Oracle DBMS
• How to run Oracle Stored Procedures using PL/SQL
• The JobSchedulerPLSQLJob SOSSQLPlusJob JITL job documentation