...
Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
...
Introduction
The 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 collect and passon 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.
Simple JITL PL/SQL Job Example
Following is simple the most basic example of the JITL JobSchedulerPLSQLJob. Following job is simple example of executing an PL/SQL unanimous code bloc. Following example is We are selecting current system date and displaying is it on stdout as a order_date.
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
<?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="yes"> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <param name="db_url" value="jdbc:oracle:thin:@:1521:DORCL01"/> <param name="db_user" value="sos_scheduler"/> <param name="db_password" value="sos"/> <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> |
Parameters
The JobSchedulerPLSQLJob needs following parameters.
Name | Title | Mandatory | Default | Example |
---|---|---|---|---|
command | PL/SQL statements to be executed | true |
|
|
db_url | JDBC connection string | true |
|
|
db_user | User name for database access | true |
| db username |
db_password | Password for database access | true |
| db password |
variable_parser_reg_expr | Regular expression to parse dbms_output and set order parameters for next job steps | false | ^SETs+(\\s)\\s*ISs(.*)$ |
|
...
- 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 SQLcan be written as part of the Job.XML, this approach is preferred 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; "/>
...
- JITL will need an standard JDBC database connection string i.e. jdbc jdbc:oracle:thin:@localhost:1521:XE
db_user
- DB Username which has appropriate database right to execute PL/SQL code.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?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_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> <run_time /> </job> |
Tip |
---|
parameters can also be defined with following |
...
syntax
|
PL/SQL script as external file
...
Code Block | ||||
---|---|---|---|---|
| ||||
declare howmany NUMBER; p_id varchar2(20) := null; begin dbms_output.put_line('set variable1=value1'); p_id := '12345'; -- -- If the job or order contains the parameter -- -- table_name=scheduler_variables -- -- then all following selects are substituted to 'select count(*) into howmany from scheduler_variables;' -- select count(*) into howmany from $\{SCHEDULER_PARAM_table_name\}; select count(*) into howmany from %table_name%; select count(*) into howmany from %TABLE_NAME%; select count(*) into howmany from $\{SCHEDULER_PARAM_TABLE_NAME\}; select count(*) into howmany from $\{scheduler_param_table_name\}; select count(*) into howmany from $\{sChEdUlEr_pArAm_tAbLe_nAmE\}; -- -- now put the results to the buffer -- JS will get the results from the buffer -- dbms_output.put_line('The table %table_name% has ' || howmany || ' rows.'); dbms_output.put_line('set howmany is ' || howmany); dbms_output.put_line('set variable1 is ' || p_id); dbms_output.put_line('set variable2 is value2'); end; |
...
Example: PL/SQL code
- If the job or order contains the parameter table_name with the value
scheduler_variables
then all following SQL "select" statements are substituted to:select count(*) into howmany from scheduler_variables
; - If the job is started in a job chain by an order then the following parameters are added to the order
- see the parameter variable_parser_reg_expr
- For this purpose, the output of the PL/SQL statement is parsed with the regular expression:
^SET\\s+([^\\s]+)\\s*IS\\s+(.*)$
...