The SET CONNECTION_ATTRIBUTES command allows you to declare a connection to one Oracle database server and to supply authentication attributes necessary to connect to the server.

You can connect to more than one Oracle database server by issuing multiple SET CONNECTION_ATTRIBUTES commands. The actual connection takes place when the first request referencing that connection is issued. You can issue SET CONNECTION_ATTRIBUTES commands in a FOCEXEC, at the FOCUS command prompt or in a FOCUS-supported profile. The profile can be encrypted.

If you issue multiple SET CONNECTION_ATTRIBUTES commands:

• The first SET CONNECTION_ATTRIBUTES command sets the default Oracle database server to be used.
• If more than one SET CONNECTION_ATTRIBUTES command declares the same Oracle database server, the authentication information is taken from the last SET CONNECTION_ATTRIBUTES command.

The adapter supports connections to:

• Local Oracle database servers.
• Remote Oracle database servers. To connect to a remote Oracle database server, the Oracle tnsnames file on the source machine must contain an entry pointing to the target machine and the listening process must be running on the target machine.

Once you are connected to an Oracle database server, that server may define Oracle DATABASE LINKs that can be used to access Oracle tables on other Oracle database servers.

If needed, the DBA or some other authorized person at your site will supply you with a valid Oracle user ID and password.

It may be desirable to prompt users for their Oracle password instead of coding it in a procedure. In this case, use a Dialogue Manager variable in its place, and retrieve the value using -CRTFORM or -PROMPT facilities. If you use -CRTFORM, you can make the password field non-displayable for additional security.

A valid Oracle user ID and password must be supplied before issuing commands that access the Oracle RDBMS. If a valid Oracle login ID and password have not been supplied, an error message is returned. You should respond by correcting and re-issuing the SET CONNECTION_ATTRIBUTES command.

The SET CONNECTION_ATTRIBUTES command stores both the Oracle login ID and password in user virtual storage. It does not immediately initiate a login to Oracle. The actual login is deferred until a subsequent command is issued that requires the services of the Oracle RDBMS.

The syntax is:

SQL [SQLORA] SET CONNECTION_ATTRIBUTES [connection_name]/userid,password

where:

SQLORA

Indicates the Adapter for Oracle. You can omit this value if you previously issued the SET SQLENGINE command.

connection_name

Specifies a remote instance using an Oracle TNSNAME (the net service name used as a connect descriptor to an Oracle database server across the network). If omitted, the local database server will be set as the default.

userid

Is the primary authorization ID by which you are known to Oracle, up to 30 characters in length.

password

Is the password associated with the user ID, up to 30 characters in length.

Note: SET USER is a synonym for SET CONNECTION_ATTRIBUTES, supported for compatibility with earlier releases of the adapter. However, note that the symbol used for separating the connection attribute from the authentication information and the symbol used for separating the user ID from the password changed in FOCUS 7.2.

Issue the following query command to list status information for all declared connections:

SQL SQLORA ? SERVERS

When an SQL SELECT statement refers to CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP, you can choose a source for obtaining the system date and time. By default, the date or time value will be obtained from the system running FOCUS. Using the SET DATETIME_PROCESS command, you can override this default so that the date or time is obtained from the Oracle DBMS for reports using Automatic Passthru.

SQL SET DATETIME_PROCESS=SERVER|DBMS
END

Once all Oracle database servers to be accessed have been declared using the SET CONNECTION_ATTRIBUTES command, you can select a default server using the SET DEFAULT_CONNECTION command. If you do not issue this command, the tnsname value specified in the first SET CONNECTION_ATTRIBUTES command is used.

SQL [SQLORA] SET DEFAULT_CONNECTION [connection_name]

where:

SQLORA

Indicates the Adapter for Oracle. You can omit this value if you previously issued the SET SQLENGINE command.

connection_name

Is the net service name used as a connect descriptor to an Oracle database server across the network. If omitted, then the local database server will be set as the default. If this name has not been previously declared in a SET CONNECTION_ATTRIBUTES command, message FOC1671 is issued.

Note:

• If you use the SQL SQLORA SET DEFAULT_CONNECTION command more than once, the connection name specified in the last command will be the active connection name.
• The SQL SQLORA SET DEFAULT_CONNECTION command cannot be issued while an uncommitted transaction (LUW) is pending. In that case message FOC1671 is issued.

With regard to CHAR and VARCHAR2 data types, special attention must be paid to which of the two will be used. When you compare a column of CHAR data type to a column of VARCHAR2 data type, where the only difference in data is the additional space in the column of the CHAR data type, Oracle recognizes that the data types are not the same.

The ORACHAR setting lets you specify which of the two data types will be used for inserting, updating, and retrieving data.

If you create the tables outside of FOCUS, we recommend that you use either CHAR or VARCHAR2 data types, but not both. If you create a table with both data types, you might not be able to retrieve the data you inserted due to Oracle's comparison mechanism.

If you use FOCUS to generate Oracle tables and retrieve data, you will not encounter this problem, since the data type being used will be either CHAR or VARCHAR2, depending upon the ORACHAR setting.

SQL [SQLORA] SET ORACHAR {FIX|VAR}

where:

FIX

Uses the CHAR data type.

VAR

Uses the VARCHAR2 data type. This is the default.

Note: Omit the SQLORA target RDBMS qualifier if you issued the SET SQLENGINE command.

Prior to Oracle V7, the Oracle RDBMS treated all character strings transmitted by the adapter through SQL variables as variable length because all Oracle RDBMS character data types were essentially variable length. In Oracle V7, the CHAR data type was enhanced to possess fixed length characteristics similar to other relational database CHAR data types.

Important: The FIX setting may cause compatibility problems in applications developed in earlier releases of FOCUS or Oracle or under the default setting of VAR. Use of the FIX setting with Oracle V7 CHAR columns in MODIFY MATCH, INCLUDE, UPDATE and DELETE commands, as variables in parameterized Direct SQL Passthru statements, and as cross-referenced fields in non-optimized join requests should be tested extensively to verify that their behavior is as expected.

The ORANUMBER setting determines how the Oracle NUMBER data type will be described in the Master File generated for the answer set returned by a Direct SQL Passthru SELECT request.

By default a NUMBER data type whose precision is:

• Between 32 and 37 is mapped to the format D20.2.
• 38 is mapped to the format I11.

You can use the ORANUMBER setting to override the default mapping of a NUMBER data type with precision between 32 and 38.

SQL [SQLORA] SET ORANUMBER {COMPAT|DECIMAL}

An Adapter for Oracle parameter can be used to set the maximum number of input parameters for stored procedures.

SQL [SQLORA] SET SPMAXPRM value

where:

value

Is a numeric value indicating the maximum number of input parameters that may be entered for stored procedures. The default value is 256. This value is displayed by the SQL SQLORA ? query.

Note: Omit the SQLORA target RDBMS qualifier if you issued the SET SQLENGINE command.