Use Database Commands

Use the connection wizard to specify database commands that run when an Oracle connection or session is opened or closed, or when a DB2 or Microsoft SQL Server session is opened.

For example, you can use a database command to set the language when you open a connection to an Oracle database, or to set up proxy connections or virtual private databases .

For DB2 and Microsoft SQL, you can use commands to run native SQL commands when you open a session, such as running a stored procedure.

Database commands are included in command blocks which are formatted using XML.

<commandBlock> 
  <commands> 
   <sqlCommand> 
    <sql>ALTER SESSION SET NLS_LANGUAGE = FRENCH</sql> 
   </sqlCommand> 
  </commands> 
</commandBlock>

<commandBlock> 
  <commands> 
   <sqlCommand> 
    <sql>ALTER SESSION SET NLS_LANGUAGE = ENGLISH</sql> 
   </sqlCommand> 
  </commands> 
</commandBlock>

Add Command Blocks While Creating a Data Source

You add command blocks using the connection wizard.

By default, connections acquire properties from the parent data source. You can modify this later.

Steps

1. Complete steps 1 to 6 to create a data source , choosing Oracle, DB2, or Microsoft SQL Server as the data source type.

2. Click Next.

3. In the specify commands page, click Set next to the command string that you want to specify.

4. In the set command page, add an XML command block, and click OK.

   Note: If you are using DB2 or Microsoft SQL Server, you can only add commands block for opening a session.

5. Continue adding command blocks, as required.

6. Click Finish.

Add or Modify Command Blocks for a Connection

Connections acquire properties from their parent data source. If you have added a command block for a data source, then that command block will be available to Oracle, DB2, or Microsoft SQL Server connections in that data source.

You can change a command block for a specific connection, and you can remove the command block if you do not want it used for the connection.

Steps

1. Complete steps 1 to 7 to create a connection or to modify a connection , choosing Oracle, DB2, or Microsoft SQL Server as the data source type.

2. Click Next.

3. Click Set or Edit next to the command block that you want to modify.

   The Acquired column shows Yes or No to indicate whether the connection acquires properties from its parent data source.

4. In the set command page, add or modify the XML code command block, and click OK.

   Tip: You can reset command blocks by selecting the check box next to the connections clicking Reset to parent value or Clear.

   Note: If you are using DB2 or Microsoft SQL Server, you can only add commands block for opening a session.

5. Continue adding or modifying command blocks, as required.

6. Click Finish.

Command Block Examples

The following diagram shows an example of the interaction between the four available command blocks, assuming that a connection to the database does not exist.

Example -- Using Command Blocks for Proxy Connections for Oracle

If you are using Oracle proxy connections, you can use an existing idle connection with signons for proxy connections. The physical connection can be used by more than one user. Because the proxy connections run on top of the existing physical connection, fewer physical connections are required.

To create a proxy connection, you create open session command blocks in XML.

Only one proxy connection session can exist for each physical connection. If the connection fails while a proxy connection is being created, the handle for the application server session is reassociated with the service context handle. All subsequent requests use the Oracle session handle that was created during connection time until a new proxy connection is created for the user.

The following is a simple example of an open session command block that creates a proxy connection for User1. Note that the sessionStartCommand is an Oracle-specific command that triggers the start of an OCI_session.

<commandBlock>
  <commands>
    <sessionStartCommand>
      <arguments>
        <argument>
          <name>OCI_ATTR_USERNAME</name>
          <value>PROXY_USER1</value>
        </argument>
      </arguments>
    </sessionStartCommand>
  </commands>
</commandBlock>

Another example is a macro that can be substituted if authentication userNames are equivalent to the proxy userid.

<commandBlock>
  <commands>
    <sessionStartCommand>
      <arguments> 
        <argument> 
          <name>OCI_ATTR_USERNAME</name>
          <value>#$account.personalInfo.userName#
          </value>
        </argument>
      </arguments>
    </sessionStartCommand>
  </commands>
</commandBlock>

The following is a simple example of a close session command block for a proxy session. The current proxy connection is terminated. Note that sessionEndCommand is an Oracle-specific command that ends an OCI_session.

<commandBlock>
  <commands>
    <sessionEndCommand>
      <arguments/>
    </sessionEndCommand>
  </commands>
</commandBlock>

Example -- Using Command Blocks for Virtual Private Databases for Oracle

Typically, Oracle uses signons to determine the database information that users can access. A virtual private database determines which users can access which information, without further signon information required.

You create a command block for the connection using macros that are substituted at run time for the logged on user. The macros identify the user so that the user need not re-enter signon information.

If all users who access the database are defined as database users and user accounts are used for connections, you can set up the context automatically when the connection is established. For example, the macro can be substituted for the userName.

The XML command block stores a series of commands that are run in the stated sequence. This may include the commands that are described in Schema for Data Source Commands.

The following example shows an XML command block for a virtual private database.

This command block sets up a context (virtual private database) within the connection based on the passed parameter. The passed parameter is retrieved from the environment, which is related to the user's logon at the portal level. These variables can be modified in the configuration tool. Their values are user specific and obtained using the security control mechanism (CAM).

<commandBlock>
  <commands>
    <sqlCommand> 
      <sql>BEGIN PKG_COUNTRY_CONTEXT.SP_SET_COUNTRY1
       (#$account.parameters.var1#);
END;</sql>
    </sqlCommand>
  </commands>
</commandBlock>

This example shows account parameter substitution. You must specify account information as custom properties. For information about session properties, see the Framework Manager User Guide.

Note: Command blocks for Oracle proxy connections and virtual private databases at the data source level apply to all connections to that data source.