Page History

...

Code Block

language	xml
title	Simple JobSchedulerPLSQLJob
linenumbers	true
collapse	true

<?xml version="1.0" encoding="ISO-8859-1"?>
<job  title="Execute PL/SQL procedure" order="yes">
    <description >
        <include  file="jobs/JobSchedulerPLSQLJob.xml"/>
    </description>
    <params >
        <!-- Database connection parameters i.e. db_url, db_user, db_password -->
        <param  name="db_url"      value="jdbc:oracle:thin:@:1521:DORCL01"/>
        <param  name="db_user"     value="sos_scheduler"/>
        <param  name="db_password" value="sos"/>			
 
       <!-- 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;
		"/>
 
        <param  name=<!-- 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>

...

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/SQLcan be written as part of the Job.XML, this approach is preferred if PL/SQL code is very small and used by single job.

Code Block

language	sql	collapse	true

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

...

Code Block

language	xml
title	Simple JobSchedulerPLSQLJob
collapse	true

<?xml version="1.0" encoding="ISO-8859-1"?>
<job  title="Execute PL/SQL procedure" order="yesno">
    <description >
        <include  file="jobs/JobSchedulerPLSQLJob.xml"/>
    </description>
    <params >
 
       <include  live_file="../common_settings/database/database_connection.params.<!-- Database connection parameters i.e. db_url, db_user, db_password -->
       <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<!-- 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>

...

Code Block

language	xml
title	Passing variables to the PL/SQL
collapse	true

<?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"/>
	   
        <param  name="command" 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_date       
			  FROM DUAL;  
			DBMS_OUTPUT.PUT_LINE(' +++              +++');
			DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date);
			DBMS_OUTPUT.PUT_LINE(' +++              +++');
		END;   "/>
 
    </params>
    <script  language="java" java_    <!-- 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>

...

Code Block

language	xml
title	Passing variables to the PL/SQL
collapse	true

<?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-- Parameter can be passed by task or as order param -->
		<param  name="date_mask" value="YYYYMMDD_HH24MI"/>
		
        <!-- PL/SQL script from filesystem -->
		    <param  name="date_maskcommand" value="YYYYMMDD_HH24MIC:/app/executables/plsql/get_order_date.sql"/>
		       	
 
        < <!-- PL/SQL script from filesystem- dbms_output to JobScheduler Order parameter parser regex -->
	        <param  name="commandvariable_parser_reg_expr" value="C:/app/executables/plsql/get_order_date.sql"/>       	^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
    </params>
    <script  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
    <run_time />
</job>

...

JobChain

Code Block

language	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
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 by task or as order param -->
        <param  name="date_mask" value="YYYYMMDD_HH24MI"/>       
 
        <!-- Database connection parameters i.e. db_url, db_user, db_password -->
        <param<include  namelive_file="date_mask" value="YYYYMMDD_HH24MI"/>       
 ../common_settings/database/database_connection.params.xml" node=""/>
 
        < <!-- Databasedbms_output connectionto parameters i.e. db_url, db_user, db_passwordJobScheduler Order parameter parser regex -->
        <include<param  live_filename="../common_settings/database/database_connection.params.xml" node="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
    </params>
    <script  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
    <run_time />
</job>

Job
Code Block
language xml
collapse true

...

Space shortcuts

Page tree

Versions Compared

Old Version 21

New Version 22

Key