Oracle Connection Attributes

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 declare connections 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.

In this section: Connecting to an Oracle Database Server Authenticating a User on an Oracle Database Server Selecting an Oracle Connection to Access Oracle Support for DATABASE LINKs How to: Declare Connection Attributes for Oracle Query the Declared Oracle Connections Select an Oracle Connection to Access Example: Declaring Connection Attributes for Oracle Selecting an Oracle Connection to Access

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.

Connecting to an Oracle Database Server

The data 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.

Authenticating a User on an Oracle Database Server

Users can issue multiple SET CONNECTION_ATTRIBUTES commands to supply valid user IDs and passwords.

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.

Syntax: How to Declare Connection Attributes for Oracle

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

where:

SQLORA

Indicates the Oracle Data Adapter. 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 data 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 have changed in FOCUS 7.2.

Syntax: How to Query the Declared Oracle Connections

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

SQL SQLORA ? SERVERS

Example: Declaring Connection Attributes for Oracle

Refer to the following diagram in conjunction with these examples:

The following SET CONNECTION_ATTRIBUTES command connects to the Oracle database server named TNSNAMEA with an explicit user ID and password:

SQL SQLORA SET CONNECTION_ATTRIBUTES TNSNAMEA/USERA,PWDA

The following SET CONNECTION_ATTRIBUTES command connects to a local Oracle database server with an explicit user ID and password:

SQL SQLORA SET CONNECTION_ATTRIBUTES /USERA,PWDA

Selecting an Oracle Connection to Access

Once all Oracle database servers to be accessed have been declared using the SET CONNECTION_ATTRIBUTES command, there are two ways to select a specific Oracle connection from the list of declared connections:

• You can select a connection using the SET DEFAULT_CONNECTION command. If you do not issue this command, the connection name specified in the first SET CONNECTION_ATTRIBUTES command is used.
• You can include the CONNECTION= attribute in the Access File of the table specified in the current query. This attribute supersedes the default connection.

Syntax: How to Select an Oracle Connection to Access

SQL [SQLORA] SET DEFAULT_CONNECTION [connection_name]

where:

SQLORA

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

connection_name

Is the connection name specified in a previously issued SET CONNECTION_ATTRIBUTES command. If omitted, the local database server is set as the default. If this connection name has not been previously declared, a FOC1671 message 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 SET DEFAULT_CONNECTION command cannot be issued while an uncommitted transaction (LUW) is pending. In that case, a FOC1671 message is issued.
• SET SERVER is a synonym for SET DEFAULT_CONNECTION, supported for compatibility with earlier releases of the data adapter.

Example: Selecting an Oracle Connection to Access

The following SET DEFAULT_CONNECTION command selects the Oracle database server named TNSNAMEB as the default Oracle database server:

SQL SQLORA SET DEFAULT_CONNECTION TNSNAMEB

Note: You must have previously issued a SET CONNECTION_ATTRIBUTES command for TNSNAMEB.

Oracle Support for DATABASE LINKs

You can be connected to one Oracle database server and access a table on another Oracle database server (without actually connecting to the second server) if the first server has a DATABASE LINK defined for the table.

To access a remote Oracle table using DATABASE LINKs, the following conditions must exist:

• The Oracle database server to which you are connected must have a valid DATABASE LINK defined.
• The TABLENAME= attribute in the Access File for the Oracle table to be queried must have the following format:

TABLENAME=[owner.]tablename@databaselink

where:

owner

Is the user ID by default. It can consist of a maximum of 30 characters. Oracle prefers that the value be uppercase.

tablename

Is the name of the table or view. It can consist of a maximum of 30 characters.

databaselink

Is the valid DATABASE LINK name defined in the currently connected Oracle database server.

Once you have met the above conditions, all requests for the table will be processed on the remote Oracle database server specified using the DATABASE LINK name. Using this method is another way to access multiple remote servers in one SQL request.