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.