Using the SQLPLUS Definition Type

SQL Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with Oracle. To use it, a Process Server must have the SQLPLUS Definition Type. This is not set by default on Platform Agent Process Servers.

The Platform Agent host must also have Oracle Software installed with a sqlplus binary, and the Oracle environment must be set.

Variables and Parameters

You can manipulate parameter values in a SQL Plus script as substitution variables, using the standard &PARAMETER. syntax. Note that variable names are case-insensitive.

SQL Plus substitution variables are string-based. Defines are limited to 240 characters.

To define an Out parameter, define a variable with the name of the parameter using the regular method. To set an Out parameter, use define PARAMETER = 'VALUE'. If you are using date parameters, they must be in the standard Oracle date format SYYYMMDDHH24MISS.

Note: If 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, the exception is ignored unless you add whenever sqlerror exit failure to your script.

Security

The SQLPLUS Definition Type is integrated with the credential system. Processes run on a Platform Agent (directed by the 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 starts off with.

If the Run As User field, the Remote Run As User field, the JCS_USER parameter, and the JCS_REMOTE_USER parameter are all 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: This is an OS user if Remote Run As User is also set. Otherwise, this is the database user.
  • Remote Run As User: This is the database user.

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

  • JCS_USER: The OS user if JCS_REMOTE_USER is also set. Otherwise, the database user.
  • JCS_PASSWORD: The password for the OS user if JCS_REMOTE_USER is also set. Otherwise, this is the database user.
  • JCS_ENDPOINT: The endpoint for JCS_USER.
  • JCS_REMOTE_USER: The database user.
  • JCS_REMOTE_PASSWORD: The 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.

The syntax for JCS_USER and JCS_REMOTE_USER is as follows.

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

The <endpoint> can be a database or RemoteHostName Process Server parameter.

Note: If JCS_USER or JCS_REMOTE_USER contain a password, or you use JCS_PASSWORD or JCS_REMOTE_PASSWORD, 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 you edit the Process Definition.

Note: If you specify neither a password nor an 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 defaults to domain accounts.

Credentials

You can use credentials to store username/password details in a central location. The credential system also lets you use 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, follow this procedure.

  1. Create the oracle credential type, if it does not exist. 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.
    • Endpoint: The SQL*Net connection alias that you will access the database with.
    • Real User: The Oracle database username.
    • Password: The Oracle database password.
    • Virtual User (optional): An alternative name to be used in finding the username.
  3. Enter: user@database or {virtual}:virtual-user@database into the field or parameter listed above.

Note: UNIX and Microsoft Windows only: 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 Platform Agent if your database has an inconvenient name.

To use a particular OS user, follow this procedure.

  1. Create a credential set for each required login, with the following attributes for each set:
    • Protocol: Login.
    • Endpoint: The RemoteHostName.
    • Real User: The username.
    • Password: The password.
    • Virtual User (optional): An alternative name to be used in finding the username.
  2. 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.

SQL Plus Examples

Out Parameters

This example shows built-in parameters being used as symbols. It also shows how to set an Out parameter. Note that the Process Definition must have an Out or In/Out parameter named P_BYTESFREE.

Copy 
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

This example shows how to turn timing on or off based on an In parameter named useTiming. You can use a simple constraint to allow values on and off.

Copy 
set timing &useTiming.

This is an alternative example that uses two In or In/Out parameters named optionToSet and optionValue It shows how to set any option to any value. Redwood recommends using constraints to limit what operators can set.

Copy 
set &optiontoset. &optionvalue.