Page History

...

<?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 requires the following parameters:

...

• 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" valuejava_class=" DECLAREsos.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 SELECT DBMS_OUTPUT.PUT_LINE(' +++ 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.

variable_parser_reg_expr 

Status
subtle true colour Yellow title TODO

This parameter defines a regular expression for parsing the dbms_output from the PL/ SQL script 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".

...

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 and can then be reused by multiple jobs.

...

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

 <params >

<?xml version="1.0" encoding="ISO-8859-1"?>
<job  title="Execute PL/SQL procedure" order="no">
    <description >
        <include  file="jobs/JobSchedulerPLSQLJob.xml"/>
    </description>
    <params >
 
    <param   <!-- Database connection parameters i.e. db_url, db_user, db_password --name="db_url" value="DORCL01"/>
    <param  name="db_user" value="sos_scheduler"/>
       <include<param  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(' +++      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 >
        <log_level ><![CDATA[debug9]]></log_level>
    </settings>
    <description >
        <include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>
    <params >
        +++');<include   
		END;
		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"/>
 
        <!-- dbms_output to JobScheduler Order parameter parser regex --</params>
    <script  language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass">
        <param  name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/><![CDATA[
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
SELECT TO_CHAR(SYSDATE,'dd.mm.yyyy DAY') FROM DUAL;
    </params>
    ]]>
 <script  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> </script>
    <run_time />
</job>

Passing parameters to the PL/SQL 

...