...
The password for the DB user defined in the db_user parameter.
shell_command
xThe value of this parameter specifies the name of SQL*Plus client. The SQL sctipt will be processed by the client. This parameter is useful when executing legacy SQL code i.e. Oracle 9i, 10g
variable_parser_reg_expr
By default the JobSchedulerSQLPlus job defines a regular expression to parse console output from the execution of SQL Script and sets order parameters for subsequent job steps. For example, the SQL*Plus client stdout prompt SET period_prev IS ${PN_YEAR_PREV_PERIOD} displays the output on console; if period_prev is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ and return the period_prev="20140915" order parameter. All stdout statements matching the ^SETs+(\\s)\\s*ISs(.*)$ regular expression will be set as order_parameters.
command_Line_options
x
ignore_ora_messages
x
The value of this parameter specifies all the command line parameters required by the SQL*Plus client i.e. -V SQL*Plus version , -S silent no banner , -M automatic HTML output etc.
ignore_ora_messages
Its not always the case when you want to ignore error messages but if need be arise, value of the parameter ignore_ora_message will specify which ORA messages Jobscheduler can ignore.
ignore_sp2_messages
If there are some expected Oracle SP2 errors which you want to ignore, use a comma separated list of all the SP2 errors as value for the ignore_sp2_messages parameter.
x
include_files
If you have some common code need to be executed before SQL*Plus code, you can define with include_files
...
parameter, Multiple files can be specify by semicolon separated.
x
Saving Database Connection Settings in a Parameter File
...
| 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 >
<param name="BCY_DATE" value="28.07.2014"/>
<param name="PN_YEAR_PERIOD" value="2014"/>
<param name="PN_YEAR_PREV_PERIOD" value="2013"/>
<param name="command_script_file" value="C:\app\executables\sqls\get_booking_cycle.sql"/>
<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"/>
<run_time />
</job>
|
...
JobChain
Code Block language xml title JITL-PLSQLSQLPlus.job_chain.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <job_chain orders_recoverable="yes" visible="yes"> <job_chain_node state="execute_plsqlsqlplus_script" job="JITL-PLSQLSQLPlus" next_state="sucess" error_state="error"/> <job_chain_node state="sucess"/> <job_chain_node state="error"/> </job_chain>Job
Code Block language xml title JITL-PLSQLSQLPlus.job.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <job title="ExecuteStart PL/SQL procedureSQL*Plus client and execute a sql*plus script" order="yes"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > </settings> <description > <include <!-- Parameter can be passed by task or as order param -- file="jobs/SOSSQLPlusJob.xml"/> </description> <params > <param name="dateBCY_maskDATE" value="YYYYMMDD_HH24MI28.07.2014"/> <!-- Database connection parameters<param i.e. db_url, db_user, db_password -- name="PN_YEAR_PERIOD" value="2014"/> <include<param live_filename="../common_settings/database/database_connection.params.xml" node="PN_YEAR_PREV_PERIOD" value="2013"/> <!-- dbms_output to JobScheduler Order parameter parser regex -- <param name="command_script_file" value="C:\app\executables\sqls\get_booking_cycle.sql"/> <param<include namefile="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml" node=""/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClassSOSSQLPlusJobJSAdapterClass"/> <run_time /> </job>
Order : get get_orderbooking_datecycle
Code Block language xml title JITL-PLSQLSQLPlus,get_orderbooking_datecycle.order.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate Order DateExecute get_booking_cycle.sql"> <params > <!-- PL/SQL script file --> <param name="command_script_file" value="C:/\app/executables/plsql/\executables\sqls\get_last_booking_datecycle.sql"/> </params> <run_time let_run="no"> <period single_start="08:00"/> </run_time> </order>
Order : get_lasttrx_booking_datecount
Code Block language xml title JITL-PLSQLSQLPlus,get_lasttrx_order_datecount.order.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <order title="CalculateExecute last booking dateget_booking_cycle.sql"> <params > <!-- PL/SQL script file --> <param name="command_script_file" value="C:/\app/executables/plsql/\executables\sqls\get_lasttrx_booking_datecount.sql"/> </params> <run_time let_run="no"> <period single_start="1108:00"/> </run_time> </order>
Return parameters created by the JobSchedulerSQLPlusJob
...