...
| # | Name | Title | Mandatory | Default | Example |
|---|---|---|---|---|---|
| 1 | command_script_file | SQL script to be executed | true |
|
|
| 2 | db_url | Database service or Instance name | true |
| DORCL01 |
| 3 | db_user | User name for database access | true |
| db username |
| 4 | db_password | Password for database access | true |
| db password |
| 5 | shell_command | SQL plus client name | true | sqlplus | |
| 6 | variable_parser_reg_expr | Regular expression to parse output and set order parameters for next job steps | false | ^SETs+(\\s)\\s*ISs(.*)$ |
|
| 7 | Commandcommand_Line_options | SQL*Plus command line options | false | -S -L | |
| 8 | ignore_ora_messages | Ignore ora error messages | false | ||
| 9 | ignore_sp2_messages | ignore sp2 error messages | false | ||
| 10 | include_files | Code blocks should be executed before SQL | false |
command_script_file
- The SQL script can be:
saved to a separate file such as get_order.sql . This file can subsequently be referred to as the value of the "command_script_file" job parameter. This is a recommended approach for achieving "separation of concern" in application architecture.
Code Block language sql <param name="command_script_file" value="config/live/commn/sqls/get_order.sql"/>
SQL code can also be specified as the value of the CDATA tag as part of the Job.XML. This approach is preferred only if the SQL code is very small and only used by a single job.
Code Block language sql <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>
...
The password for the DB user defined in the db_user parameter.
shell_command
x
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
ignore_sp2_messages
x
include_files
x
Saving Database Connection Settings in a Parameter File
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
<params >
<param name="db_url" value="DORCL01"/>
<param name="db_user" value="sos_scheduler"/>
<param name="db_password" value="sos"/>
</params> |
...