Page History
Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Introduction
The job template SOSSQLPLUSJobSQLPLUSJob provides a standardized and parameterized interface for executing Oracle® SQL*Plus scripts. The job template offers out-of-the-box capabilities
- to execute an SQL*Plus script,
- to pass arguments to an SQL*Plus script,
- to collect results,
- to pass results as Order Variables to the next job.
Usage
The following example explains the basic use of the SOSSQLPLUSJobSQLPLUSJob. It executes a simple SQL statement selecting the current system date and writing it to stdout.
...
- to select the JITL job class and
- to specify the
com.sos.jitl.jobs.db.oracle.SOSSQLPLUSJobSQLPLUSJob
Java class name.
The job arguments can be specified as follows:
...
- The
command_script_file
argument points to a file e.g. with the following contents:WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
SELECT 'SET business_date IS ' || TO_CHAR(SYSDATE,'dd.mm.yyyy DAY') FROM DUAL;
Arguments
The com.sos.jitl.jobs.db.oracle.SOSSQLPLUSJobSQLPLUSJob
class accepts the following arguments:
...
Name | Purpose | Required | Default Value | Example |
---|---|---|---|---|
| Path to the SQL script that should be executed | true |
| |
| Database service name or instance name | true |
| |
| User name for database access | true | scott | |
| Password for database access | true | tiger | |
shell_command | Path to the SQL*Plus command line utility | true | sqlplus | |
| Regular expression to parse output and to set order variables for subsequent jobs | false | ^SETs+(\\s)\\s*ISs(.*)$ | |
command_line_options | SQL*Plus command line options | false | -S -L | |
ignore_ora_messages | Ignore Oracle error messages | false | ||
ignore_sp2_messages | Ignore sp2 error messages | false | ||
include_files | Code blocks that should be included before executing the SQL*Plus script | false |
Argument: command_script_file
This argument expects a path to the SQL*Plus script that holds the SQL statements to be executed.
Argument: db_url
The db_url
argument holds the Oracle service name or instance name.
Argument: db_user
The database user account for which the SQL*Plus script is executed.
Argument: db_password
The password for the database user account specified with the db_user
argument.
Argument: shell_command
The value of this variable specifies the location of the SQL*Plus command line utility. The SQL*Plus script will be processed by the indicated SQL*Plus binary.
Argument: variable_parser_reg_expr
This argument specifies a regular expression for parsing the output created by the the DBMS_OUTPUT
package from the PL/SQL statements. For each match an order variable is created that is available for subsequent jobs. For example, the statement
...
Subsequent instructions and jobs can use of the $order_date
order variable.
Argument: command_line_options
The value of this variable specifies the command line parameters required by SQL*Plus, e.g. -V SQL*Plus version , -S silent no banner , -M automatic HTML output
etc.
Argument: ignore_ora_messages
This argument is used to ignore certain Oracle errors and expects a comma separated list of ORA-* error codes that should be ignored.
Argument: ignore_sp2_messages
This argument is used to ignore certain Oracle SP2 errors and expects a comma separated list of SP2* error codes that should be ignored.
Argument: include_files
Should some common SQL code be executed before the execution of the SQL*Plus script then the respective SQL script files can be specified by use of the include_files
argument. A number of files can be specified by separating each file name with a semicolon.
Passing Arguments to SQL*Plus Scripts
Job arguments can be passed to the SQL*Plus script.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
WHENEVER SQLERROR EXIT SQL.SQLCODE WHENEVER OSERROR EXIT 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; |
Passing SQL execution results to subsequent Jobs
The job offers use of the variable_parser_reg_expr
argument. The argument's default value specifies a regular expression:
...
As a result an Order Variable with the name my_variable
and the value some_value
is created. This pattern can be applied to any number of lines each creating a new Order Variable for subsequent instructions and jobs.
Return Variables created by the
...
SQLPLUSJob
The job automatically returns the following Order Variables that are available to subsequent instructions and jobs.
Return Variable: sql_error
The sql_error
variable holds the error messages occurring during SQL*Plus execution. This variable is empty if no errors occur.
Return Variable: std_out_output
The std_out_output
variable holds the messages spooled to stdout by SQL*Plus.
Return Variable: std_err_output
The std_err_output
variable contains the messages spooled to stderr by SQL*Plus.
Return Variable: exit_code
- The
exit_code
will be 0 If the SQL*Plus script terminates successfully. - If
std_err_output
is not empty then the exit code will be set to the value99
. - If either SP2* or ORA-* errors occur then the exit code will be set to the value
98
.
Further References
- How to work with PL/SQL and the Oracle DBMS
- How to run Oracle Stored Procedures using PL/SQL
- The SOSSQLPlusJob SQLPlusJob JITL job documentation