Page History

...

• 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
    <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_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.

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".

Database connection settings as parameter file

...