Page History

...

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

...

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

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

...

• 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

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

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

...