Page History

...

following line

DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date);   

will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$  will result in

command

command

• The PL/SQL code be 

  • saved to a separate file i.e. get_order.sql ,

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

...

db_user

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

db_password

• appropriate  database right to execute PL/SQL code. 

db_password

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

variable_parser_reg_expr

• This parameter defines a reguler expression to parse dbms_output from PL/SQL execution and set the order parameters for subsequent job steps.  i.e. 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"Password for the DB user defined in the db_user parameter.

Database connection settings as parameter file

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.

...

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

Passing parameters to the PL/SQL 

• 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}

...