Description of JobSchedulerPLSQLJob - Execute PL/SQL procedure

What is the purpose of this job?

...

• The PL/SQL code be 

  • saved to a separate file i.e. get_order.sql , and subsequently sql file can be referred as the value of the job parameter "command", this is a recommended  approach to achieve "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 parameter command, entire PL/CODE can be written as part of the Job.XML, this approach is preferred 

    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; "/>

• Before the script is executed, the script will be analyzed by the job. 
  • Should JobScheduler parameter names be containted in that script then they are substituted by their current value.
  • The following notations are supported: %parameter_name%, ${SCHEDULER_PARAM_parameter_name}

db_url

• JITL will need an standard JDBC database connection string i.e. jdbc:oracle:thin:@localhost:1521:XE

db_user

• DB Username to execute PL/SQL code should be executed, this db

db_password

...

db_url

• JITL will need an standard JDBC database connection string i.e. jdbc:oracle:thin:@localhost:1521:XE

db_user

• DB Username which has appropriate  database right to execute PL/SQL code. 

db_password

• Password for the DB user defined in the db_user parameter.

Database connection settings as parameter

It strongly recommend to create a db_connection parameter file i.e. database_connection.parameter.xml and store all the Database connection settings at common location. This approach enable user to manage settings at central location and refereed by multiple jobs.

It is  also easy to maintain different settings for  development, integration and production environment.

Following is the example of a 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 >

Following is  the example of a JITL job with database connection parameter stored in the external file. In following example an directory is being created in side the live folder as "common_settings/database". 

<?xml version="1.0" encoding="ISO-8859-1"?>
<job  title="Execute PL/SQL procedure" order="yes">
    <description >
        <include  file="jobs/JobSchedulerPLSQLJob.xml"/>
    </description>
    <params >
       <include  live_file="../common_settings/database/database_connection.params.xml" node=""/> 
       <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;
		"/>
        <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>

• Before the script is executed, the script will be analyzed by the job. 
  • Should JobScheduler parameter names be containted in that script then they are substituted by their current value.
  • The following notations are supported: %parameter_name%, ${SCHEDULER_PARAM_parameter_name}

How can results be used in subsequent jobs?

• The job makes use of the appropriate parameters in the order. 
• The first of these are static parameters, such as those described in the section "return parameters" of the template documentation. 
• At the same time these are dynamic parameters. The job anlyzes the output of the PL/SQL statements and extracts values by use of a regular expression that catches both names and values of these parameters. The regular expression is defined by the parameter variable_parser_reg_exp.

...