← Microsoft Windows Definition TypesUsing the FTP Definition Type →

Using the SQLPLUS Definition Type

SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with Oracle.

The process server needs to have the SQLPLUS definition type, this is not set by default on platform agent process servers.

The platform agent host needs to have Oracle Software installed with an sqlplus binary and the Oracle environment must be set.

Variables and Parameters

  • Parameters to the script are used in the SQLPlus source as substitution variables, using the standard &PARAMETER. syntax. Note that the variable names are case-insensitive.
  • SQLPlus substitution variables are string-based. Defines are limited to 240 characters.
  • OUT parameters are supported; just define a variable with the name of the parameter using the regular method. To set an output parameter use define PARAMETER = 'VALUE'. If you are using date parameters, they must be in the standard Oracle date format, SYYYMMDDHH24MISS.
note

When a process reaches status Error, the Out values of parameters are not always set. You can use a Post Running action to set them if required.

Returning an Exception

If your PL/SQL code exits with an exception, this is ignored in the server unless you put whenever sqlerror exit failure into your code.

Security

The SQLPLUS definition type is integrated with the credential system. Processes run on a platform agent (directed by the process queue and optionally process definition resources), and it always runs under the default OS account. The credential Run As User field is used to look up the Oracle account and database that the process will start off with.

If the Run As User and Remote Run As User fields and JCS_USER as well as JCS_REMOTE_USER parameters are left empty, which is the default, the process starts off not connected to any database.

note

The parameters take precedence over the [Remote] Run As User fields.

User and Password Authentication

The following process definition fields on the Source tab can be used to specify the OS and database users:

  • Run As User - OS user if Remote Run As User is also set, otherwise the database user
  • Remote Run As User - database user

The following special parameters can be used to specify the OS and database users:

  • JCS_USER - OS user if JCS_REMOTE_USER is also set, otherwise the database user
  • JCS_PASSWORD - password for the OS user if JCS_REMOTE_USER is also set, otherwise the database user
  • JCS_ENDPOINT - the endpoint for JCS_USER
  • JCS_REMOTE_USER - database user
  • JCS_REMOTE_PASSWORD - database user password
  • JCS_REMOTE_ENDPOINT - the endpoint for JCS_REMOTE_USER
  • SQLPLUS_USER - the database user
  • SQLPLUS_PASSWORD - the password for the database user
  • SQLPLUS_ENDPOINT - the endpoint for SQLPLUS_USER

Syntax for JCS_USER and JCS_REMOTE_USER:

<user>[/<password>][@<endpoint>]

The <endpoint> can be a database or RemoteHostName process server parameter.

note

When JCS_USER or JCS_REMOTE_USER contain a password or JCS_PASSWORD or JCS_REMOTE_PASSWORD are used, make sure to set the Password property on the parameter or the password will be stored and displayed in clear text. Passwords in the Run As User and Remote Run As User fields will be encrypted the next time(s) you edit the process definition.

note

When you do not specify a password nor endpoint, a credential lookup is performed based on the process server name.

Note that JCS_USER behaves differently on Windows in Redwood Server version 9 than did JCS_NTUSER in version 7 and earlier; JCS_USER defaults to local accounts (when no domain is specified) whereas JCS_NTUSER would default to domain accounts.

Credentials

You use credentials to store username/password details in a central location. The credential system also allows you to have virtual users which ease the migration of process definitions and process servers between development, test, and production systems. In each system you can have different credentials with the sole common denominator being the virtual user.

To use a particular database, username and password combination you need to set up the following:

  1. Create the oracle credential type, if it does not exist already. This protocol is built-in and can be removed by the administrator.
  2. Create a credential set for each required login, with the following attributes for each set:
  3. Endpoint: The SQL*Net connection alias that you will access the database with.
  4. Real User: The Oracle database username.
  5. Password: The Oracle database password.
  6. Virtual User (optional): An alternative name to find the username.
  7. Enter: user@database or {virtual}:virtual-user@database into the field or parameter listed above.
note

You cannot create a virtual name for the endpoint (database). You must use the connection string used to connect to the database. Set up SQL*Net connection aliases on the agent if your database has an inconvenient name.

note

UNIX and Microsoft Windows only.

To use a particular OS user you need to setup the following:

  1. Create a credential set for each required login, with the following attributes for each set:
  2. Protocol: login.
  3. Endpoint: RemoteHostName.
  4. Real User: username.
  5. Password: password.
  6. Virtual User (optional): An alternative name to find the username.
  7. Enter: user@database or {virtual}:virtual-user@database into the field or parameter listed above.

Environment

Depending on your configuration you may want to add Oracle environment variables to the KeepEnvironmentVariables, EnvironmentVariables or EnvironmentFileprocess server parameters if your environment does not use standard settings. The definition type code sets the following if not set by an environment variable:

  • Derives ORACLE_HOME from the location of sqlplus executable.
  • Defaults TNS_ADMIN as $ORACLE_HOME/network/admin.
  • Sets shared library path to include $ORACLE_HOME/lib (UNIX only).

See The Environment of Platform Processes for more information on environment variables with platform agent processes.

SQLPLUS Examples

Out Parameters

The following example shows the built-in parameters being used as symbols and how to set an Out parameter. Note that the process definition needs an Out or In/Out parameter named P_BYTESFREE.

rem
rem Shows built-in variables and
rem computes output parameter.
rem

column bytes new_value P_BYTESFREE

select sum(bytes) "BYTES"
from   dba_free_space;

prompt Returning free space value: &P_BYTESFREE

Controlling Timing

The following example shows how to turn timing on or off based on an In parameter useTiming, you use a simple constraint to allow values on and off.

set timing &useTiming.

Alternate example which uses two parameters, both In or In/Out named optionToSet and optionValue, and allows you to set any option to any value. It is highly recommended to use constraints to limit what operators can set.

set &optiontoset. &optionvalue.

SQLPLUS PL/SQL

← Microsoft Windows Definition TypesUsing the FTP Definition Type →