ORA driver support for other recent Oracle features

Client result cache

The PowerBuilder ORA driver supports Oracle Client Cache, however this feature depends on your Oracle Server and Client configuration. You can configure the Oracle Client Cache with an init.ora or sqlnet.ora file. Cached queries are annotated with “/*+ result_cache */” hints to indicate that results are stored in the query result cache. To cache the client result set, you must also enable OCI statement caching from PowerBuilder applications with the StatementCache DBPARM parameter.

Application driver name

An OCI application can choose its own name and set it as a diagnostic aid. The AppDriverName DPBARM parameter allows you to set your own client driver name for the PowerBuilder ORA interface. The maximum length of the name is 8 characters. You can display the client driver name with the V$SESSION_CONNECT_INFO or GV$SESSION_CONNECT_INFO dynamic performance view queries.

Client access through a proxy

The PowerBuilder ORA driver supports the proxy authentication feature that was introduced in Oracle 10.2. With proxy authentication, the end user typically authenticates to a middle tier (such as a firewall), that in turn logs into the database as a proxy user. After logging into the database, the proxy user can switch to the end user's identity and perform operations using the authorization accorded to that user.

The ConnectAs DBParm parameter allows you to take advantage of this proxy connection feature. For example, if the user’s Transaction object LogID is “Scott” and you set the ConnectAs DBParm parameter to “John”, the OCI client logs in to database as the proxy user (“Scott”), then switches to the end user identity (“John”).

If you are using connection or session pooling, the proxy user name is the connection or session pooling creator (which you can provide in the PoolCreator and PoolPwd DBParm parameters), and the Transaction object’s LogID is ignored. No proxy session can be created if pooling is set to homogeneous session mode.

Limitation on proxy connection without pooling When using a proxy connection without pooling, you must set the NLS_Charset DBPARM to “Local” or to another non-Unicode character set. If you do not change the “Unicode” default value for this DBPARM, the connection fails because the Oracle Client Interface does not accept a Unicode name string for its proxy client attribute.

Support for XMLType datatype

The PowerBuilder ORA driver supports the Oracle XMLType datatype that was introduced with Oracle 9i. The XMLType datatype is mapped to the PowerBuilder String datatype. However, you cannot use this datatype:

In the Where clause of a PowerBuilder embedded SQL statement or in a DataWindow object
As a parameter of a procedure or function, because PowerBuilder binds XMLType as a String datatype but Oracle does not support this usage

In columns that you select directly in an Oracle cursor statement

For example, if the col1 column has an XML datatype, you cannot select this column directly in an Oracle cursor as the following code attempts to do:

CREATE OR REPLACE Function p_Ora_sp_char_11 return
  types.cursortype 
AS 
l_cursor types.cursorType; 
begin 
open l_cursor for select col1 from t_Ora_sp_char_11; return l_cursor; 
end;

However, you can use the following code in a PowerBuilder application to obtain string values for the XMLType column that you select in an Oracle cursor statement:

CREATE OR REPLACE Function p_Ora_sp_char_11 return
  types.cursortype 
AS 
l_cursor types.cursorType;

begin 
open l_cursor for select x.col1.getstringval() from
    t_Ora_sp_char_11 x; 
return l_cursor; 
end;