ADO.NET for Oracle

This section describes PowerBuilder changes supporting Oracle Data Provider for .NET (ODP.NET) connections.

Drivers for these ODP.NET versions are updated:

For Oracle 10g, the Oracle.DataAccess.dll driver was upgraded from Version 10.1.0.301 to 2.102.2.20.
For Oracle 11, the Oracle.DataAccess.dll Version 2.111.6.20 driver was added.

Both drivers are ADO.NET 2.0 compatible.

The PowerBuilder ADO.NET interface no longer includes a driver for Oracle ODP.NET 9i. Users of that provider should migrate to Oracle ODP.NET 10g.

The following new features are supported:

Client Identifier: The client identifier is a predefined attribute for the Oracle application context namespace, USERENV. Like proxy authentication, the client identifier enables tracking user identities. However, unlike proxy authentication, the client identifier does not require separate sessions for the proxy user and end user. Also, the client identifier does not need to be a database user, and can be set to any string. Most important, the client identifier enables ODP.NET developers to use application context and Oracle Label Security, and to configure an Oracle Virtual Private Database (VPD) more easily.

Configure the client identifier for Oracle ADO.NET data providers in the Driver Specific tab of the Database Profile Setup dialog.

Connection Pool Optimizations for RAC Databases: An Oracle Data Provider for ADO.NET optimizes connection pooling for Real Application Cluster (RAC) databases by balancing work requests across Oracle RAC instances, based on load balancing advisory and service requirements. In addition, the ODP.NET connection pool can be enabled to proactively free resources associated with connections that have been severed when an Oracle RAC service, instance, or node goes down.

Specify ODP.NET connection pool optimizations as arguments to the ProviderString DBParm parameter. You can enter driver-specific parameters at the bottom of the Connection tab of the Database Profile Setup dialog.

Large Object Retrieval: You can retrieve entire columns of large object (LOB) data even if the select list does not contain a primary key, row id, or unique key. To use this enhancement, set the InitialLOBFetchSize property value to -1 for CLOB and BLOB objects.

LONG Retrieval: You can retrieve entire columns of LONG and LONGRAW data even if the select list does not contain a primary key, row id, or unique key. To use this enhancement, set the InitialLONGFetchSize property value to -1.

XMLType: The Oracle XMLType datatype is mapped to the PowerBuilder string type, with these limitations:
- XMLType cannot be used in Where clauses within PowerBuilder Embedded SQL statements or in a DataWindow® object.
- XMLType columns cannot be selected directly by an Oracle cursor.
  
  For example:
```
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;
```
  To use the preceding statement in PowerBuilder, modify it to:
```
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;
```
- XMLType cannot be a parameter of a procedure or function, because PowerBuilder binds XMLType as a string type, but Oracle does not support that usage. The following SQLPLUS statement illustrates:
```
declare
```
```
ss varchar2(1000);
```
```
BEGIN
```
```
ss := '<Date>1994-08-07</Date>';
```
```
mysp2(ss);
```
```
END;
```

Client Access Through a Proxy: With proxy authentication, the end user typically authenticates to a middle tier (such as a firewall), that in turn logs into the database on the user's behalf, 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 Connection tab of the Database Profile Setup dialog provides a Connect As dropdown control. To create a proxy connection, enter a different value that is not one of the predefined control items (Default, SYSOPER, and SYSDBA).

Transparent Application Failover Notification: Transparent Application Failover (TAF) notification enables an application connection to automatically reconnect to another database instance if the connection is severed. When a failover occurs, applications may wish to be notified.

A new DBParm, SvrFailover, supports TAF notification. By default, SvrFailover is set to 0. If SvrFailover is set to 1 (true or yes), the transaction object invokes the DBNotification event when a failover occurs.

The following new features are supported:

ODP.NET Configuration: Developers can now configure ODP.NET using configuration files, including the .NET application configuration file, web.config, and machine.config. Settings in the machine.config file override the registry settings. The settings in the application configuration file or the web.config file overrides the values in the machine.config file.

Additional Connection Pool Optimizations for RAC and Data Guard: ODP.NET now cleans up the connection pool when the database down event is received from Real Application Clusters (RAC) or Oracle Data Guard. This is in addition to the events for which ODP.NET previously cleaned up the connection pool: node down, service member down, and service down.

Windows-Authenticated User Connection Pooling: You can now manage operating system-authenticated connections as part of ODP.NET connection pools, through Windows account management.

Connection Pool Performance Counters: ODP.NET publishes performance counters for connection pooling, which can be viewed using the Windows Performance Monitor.

For PowerBuilder, the counters can be set in the Windows registry or in the application configuration file.

The following ADO.NET 1.1 features are not supported:

Bulk Copy Operations: ADO.NET 1.1 enables applications to efficiently load large amounts of data from a table in one database to another table in the same or a different database.

PowerBuilder does not support bulk copies; instead it uses pipelines for table copy operations.