Page History

...

What is the purpose of this job?

The job executes PL/SQL code.JITL JobSchedulerPLSQLJob job provide an standardized and parameterized  interface to execute Oracle PL/SQLs.  The JobScheduler offers out of the box capability to execute PL/SQLs, pass parameters to the PL/SQL or retrive and pass on the results of a PL/SQL execution to next job step as JobScheduler Order parameter.  The JobSchedulerPLSQLJob can be used to execute existing PL/SQL files just by referring them in the command parameter. 

How is the PL/SQL script defined?

• The PL/SQL code be 

  • saved to a separate file i.e. get_order.sql , and subsiquently 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"/>

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

...