...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?> <job title="Start SQL*Plus client and execute a sql*plus script" order="no"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/SOSSQLPlusJob.xml"/> </description> <params > <include file="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/> </params> <script language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass"> <![CDATA[ WHENEVER SQLERROR EXIT SQL.SQLCODE WHENEVER OSERROR EXIT FAILURE SELECT TO_CHAR(SYSDATE,'dd.mm.yyyy DAY') FROM DUAL; ]]> </script> <run_time /> </job> |
Passing parameters to the
...
SQL Script
JobScheduler order parameters can be passed to the PL/ SQL Script. PL/ SQL Script code can be parameterized by defining variables such as ${SCHEDULER_PARAM_VARIABLE_NAME}. Variables can be set using environment variables, JobScheduler task parameters ( as described in the following example) or from JobScheduler order parameters.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?> <job title="ExecuteStart PL/SQL procedure*Plus client and execute a sql*plus script" order="no" name="SQLPlusExample3"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/JobSchedulerPLSQLJobSOSSQLPlusJob.xml"/> </description> <params > <!-- Database connection parameters i.e. db_url, db_user, db_password --<param name="BCY_DATE" value="28.07.2014"/> <include<param live_filename="../common_settings/database/database_connection.params.xml" node="PN_YEAR_PERIOD" value="2014"/> <!-- Parameter can be passed by task<param or as order param --> <param name="date_mask name="PN_YEAR_PREV_PERIOD" value="YYYYMMDD_HH24MI2013"/> <include <!-- PL/SQL Code --> <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(' +++file="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/> </params> <script language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass"> <![CDATA[ WHENEVER SQLERROR EXIT SQL.SQLCODE WHENEVER OSERROR EXIT +++'); END; "/> <!-- 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"/>FAILURE column end_date new_value BCY_DATE column period new_value PN_YEAR_PERIOD column period_prev new_value PN_YEAR_PREV_PERIOD select '0' as end_date from dual; prompt SET end_date IS ${BCY_DATE}; / select '0' as period from dual; prompt SET period IS ${PN_YEAR_PERIOD}; / select '0' as period_prev from dual; prompt SET period_prev IS ${PN_YEAR_PREV_PERIOD}; / select sysdate from dual; prompt SET end_date IS 1; exit; ]]> </script> <run_time /> </job> |
Tip |
---|
Parameters can also be defined with following syntax:
Parameters are not case sensitive. |
...