Using Oracle

Supported versions for Oracle

PowerBuilder provides the following Oracle database interfaces. These interfaces use different DLLs and access different versions of Oracle.

Oracle interface	DLL
O10 Oracle 10g	PBO10.dll
ORA Oracle (for 11g, 12c, 18c, and 19c)	PBORA.dll

The ORA database interface allows you to connect to Oracle 11g/12c/18c/19c servers using Oracle 11g/12c/18c/19c Database Client or Oracle 11g/12c/18c/19c Instant Client. It includes partial support for the XMLType datatype that it maps to the PowerBuilder String datatype. It also supports session and connection pooling, load balancing, the Oracle Client Cache, setting of an application driver name, and access through a proxy. Oracle 11g clients can also connect to Oracle 10g servers.

The O10 database interface allows you to connect to Oracle 10g servers using Oracle 10g Database Client or Oracle 10g Instant Client. It supports BINARY_FLOAT and BINARY_DOUBLE datatypes and increased size limits for CLOB and NCLOB datatypes. Oracle 10g clients can connect to Oracle 10g servers.

Supported Oracle datatypes

Datatype conversion

The Oracle database interfaces support the Oracle datatypes listed in the following table in DataWindow objects and embedded SQL.

Binary_Float (Oracle 10g and later only)	LongRaw
Binary_Double (Oracle 10g and later only)	NChar
Bfile	Number
Blob	NVarChar2
Char	Raw
Clob	TimeStamp
Date	VarChar
Float	VarChar2
Long	XMLType (partial support, ORA driver only)

The ORA driver adds support for the XMLType datatype that was introduced with Oracle 9i. However, you cannot use this datatype with embedded SQL statements or in a DataWindow object.

Accessing Unicode data

PowerBuilder can connect, save, and retrieve data in both ANSI/DBCS and Unicode databases, but it does not convert data between Unicode and ANSI/DBCS. When character data or command text is sent to the database, PowerBuilder sends a Unicode string. The driver must guarantee that the data is saved as Unicode data correctly. When PowerBuilder retrieves character data, it assumes the data is Unicode.

A Unicode database is a database whose character set is set to a Unicode format, such as UTF-8, UTF-16, UCS-2, or UCS-4. All data must be in Unicode format, and any data saved to the database must be converted to Unicode data implicitly or explicitly.

A database that uses ANSI (or DBCS) as its character set might use special datatypes to store Unicode data. These datatypes are NCHAR and NVARCHAR2. Columns with this datatype can store only Unicode data. Any data saved into such a column must be converted to Unicode explicitly. This conversion must be handled by the database server or client.

A constant string is regarded as a char type by Oracle and its character set is NLS_CHARACTERSET. However, if the datatype in the database is NCHAR and its character set is NLS_NCHAR_CHARACTERSET, Oracle performs a conversion from NLS_CHARACTERSET to NLS_NCHAR_CHARACTERSET. This can cause loss of data. For example, if NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is UTF8, when the Unicode data is mapped to WE8ISO8859P1, the Unicode data is corrupted.

If you want to access Unicode data using NCHAR and NVARCHAR2 columns or stored procedure parameters, use PowerBuilder variables to store the Unicode data in a script using embedded SQL to avoid using a constant string, and force PowerBuilder to bind the variables.

By default, the Oracle database interfaces bind all string data to internal variables as the Oracle CHAR datatype to avoid downgrading performance. To ensure that NCHAR and NVARCHAR2 columns are handled as such on the server, set the NCharBind database parameter to 1 to have the drivers bind string data as the Oracle NCHAR datatype.

For example, suppose table1 has a column c1 with the datatype NVARCHAR2. To insert Unicode data into the table, set DisableBind to 0, set NCharBind to 1, and use this syntax:

string var1
insert into table1 (c1) values(:var1);

If an Oracle stored procedure has an NCHAR or NVARCHAR2 input parameter and the input data is a Unicode string, set the BindSPInput database parameter to 1 to force the Oracle database to bind the input data. The Oracle database interfaces are able to describe the procedure to determine its parameters, therefore you do not need to set the NCharBind database parameter.

For a DataWindow object to access NCHAR and NVARCHAR2 columns and retrieve data correctly, set both DisableBind and StaticBind to 0. Setting StaticBind to 0 ensures that PowerBuilder gets an accurate datatype before retrieving.

TimeStamp datatype

The TimeStamp datatype in Oracle9i and later is an extension of the Date datatype. It stores the year, month, and day of the Date value plus hours, minutes, and seconds:

Timestamp[fractional_seconds_precision]

The fractional_seconds_precision value is optional and provides the number of digits for indicating seconds. The range of valid values for use with PowerBuilder is 0-6.

Datatype conversion

When you retrieve or update columns, in general PowerBuilder converts data appropriately between the Oracle datatype and the PowerScript datatype. Keep in mind, however, that similarly or identically named Oracle and PowerScript datatypes do not necessarily have the same definitions.

For information about the definitions of PowerScript datatypes, see the section called “Datatypes” in PowerScript Reference.

Number datatype converted to decimal

When a DataWindow object is defined in PowerBuilder, the Oracle datatype number(size,d) is mapped to a decimal datatype. In PowerBuilder, the precision of a decimal is 18 digits. If a column's datatype has a higher precision, for example number(32,30), inserting a number with a precision greater than 18 digits produces an incorrect result when the number is retrieved in a DataWindow. For example, 1.8E-17 displays as 0.000000000000000018, whereas 1.5E-25 displays as 0.

You might be able to avoid this problem by using a different datatype, such as float, for high precision number columns in the Oracle DBMS. The float datatype is mapped to the number datatype within the DataWindow's source.

Basic software components for Oracle

You must install the software components in the following figure to access an Oracle database in PowerBuilder.

Figure: Components of an Oracle connection

Preparing to use the Oracle database

Before you define the database interface and connect to an Oracle database in PowerBuilder, follow these steps to prepare the database for use:

Install and configure the required database server, network, and client software.
Install the native Oracle database interface for the version of Oracle you want to access.
Verify that you can connect to the Oracle server and database outside PowerBuilder.
(ORA driver only) Determine whether you want to use connection pooling or session pooling.

Preparing an Oracle database for use with PowerBuilder involves these basic tasks.

Step 1: Install and configure the database server

You must install and configure the database server, network, and client software for Oracle.

To install and configure the database server, network, and client software:

Make sure the Oracle database software is installed on your computer or on the server specified in your database profile.

For example, with the Oracle O90 interface you can access an Oracle9i or Oracle 10g database server.

You must obtain the database server software from Oracle Corporation.

For installation instructions, see your Oracle documentation.
Make sure the supported network software (such as TCP/IP) is installed and running on your computer and is properly configured so that you can connect to the Oracle database server at your site.

The Hosts and Services files must be present on your computer and properly configured for your environment.

You must obtain the network software from your network vendor or database vendor.

For installation and configuration instructions, see your network or database administrator.
Install the required Oracle client software on each client computer on which PowerBuilder is installed.

You must obtain the client software from Oracle Corporation. Make sure the client software version you install supports all of the following:

The operating system running on the client computer

The version of the database that you want to access

The version of PowerBuilder that you are running

Oracle 10g Instant Client is free client software that lets you run applications without installing the standard Oracle client software. It has a small footprint and can be freely redistributed.
Make sure the Oracle client software is properly configured so that you can connect to the Oracle database server at your site.

For information about setting up Oracle configuration files, see your Oracle Net documentation.
If required by your operating system, make sure the directory containing the Oracle client software is in your system path.

Step 2: Install the database interface

If you install PowerBuilder using the PowerBuilder Installer (an online setup program), the Oracle database interface is automatically installed. If you install PowerBuilder using the downloaded installation package (an offline setup program), select Native Database Interface | Oracle interfaces from the list of components.

For a list of the Oracle database interfaces available, see Supported versions for Oracle.

Step 3: Verify the connection

Make sure you can connect to the Oracle database server and log in to the database you want to access from outside PowerBuilder.

Some possible ways to verify the connection are by running the following Oracle tools:

Accessing the database server

Tools such as Oracle TNSPING (or any other ping utility) check whether you can reach the database server from your computer.
Accessing the database

Tools such as Oracle SQL*Plus check whether you can log in to the Oracle database you want to access and perform database operations. It is a good idea to specify the same connection parameters you plan to use in your PowerBuilder database profile to access the database.

Step 4: Determine whether to use connection or session pooling

Oracle client interface (OCI) pooling for PowerBuilder applications is created when you connect to an Oracle server for the first time. The pooling is identified by the server name and character set which are passed in the DBPARM parameters SQLCA.ServerName and NLS_Charset, respectively. If two Oracle connections are connected to the same Oracle server but use different character sets, the connections must reside in different connection or session pools. All pooling-related DBPARM parameters must be set before the initial database connection from PowerBuilder.

Session pooling means that the application creates and maintains a group of stateless sessions to the database. These sessions are passed to thin clients as requested. If no session is available, a new one is created. When the client is done with the session, the client releases it to the pool. With session pooling, the number of sessions in the pool can increase dynamically.

Session pooling does not support external authentication using an OS account. If a Login ID is not specified in a database connection using an existing session pool, the Login ID of the session pooling creator is used for the connection.

CNNPool parameter maintained for backward compatibility

The O90 and O10 database drivers that you can use in PowerBuilder to connect to the 9.x and 10.x versions of the Oracle DBMS support connection pooling with the DBPARM parameter CNNPool. For backward compatibility purposes, this parameter is also supported by the ORA driver that you use with Oracle 11g. However, if the Pooling parameter is used with this driver, the CNNPool parameter is ignored.

Deciding on pooling type

The following table describes the circumstances under which you should make your pooling selection.

Choose	When database sessions are
Session pooling	Stateless (reusable by middle tier threads) and the number of back-end server processes can cause database scaling problems.
Connection pooling	Stateful (not reusable by middle tier threads) and the number of back-end server processes can cause database scaling problems. The number of physical connections and back-end server processes is reduced by using connection pooling. Therefore many more database sessions can be utilized for the same back-end server configuration.
No pooling	Stateful (not reusable by middle tier threads) and the number of back-end server processes will never be large enough to cause scaling issues for the database. MTS components do not support either type of pooling for Oracle databases.

Choose

When database sessions are

Session pooling

Stateless (reusable by middle tier threads) and the number of back-end server processes can cause database scaling problems.

Connection pooling

Stateful (not reusable by middle tier threads) and the number of back-end server processes can cause database scaling problems. The number of physical connections and back-end server processes is reduced by using connection pooling. Therefore many more database sessions can be utilized for the same back-end server configuration.

No pooling

Stateful (not reusable by middle tier threads) and the number of back-end server processes will never be large enough to cause scaling issues for the database.

MTS components do not support either type of pooling for Oracle databases.

Setting pooling parameters

The database profile dialog box for an Oracle 11g connection includes a Pooling tab that lets you select the pooling parameters listed in the following table.

Pooling parameter	Description
Pooling Type	You can select Session Pooling, Connection Pooling, or None (default). Sets the Pooling DBPARM.
Runtime Connection Load Balancing	This check box selected by default. It is ignored when you select Connection Pooling or None for the Pooling Type. Sets the RTConnBalancing DBPARM.
Homogeneous Session	This check box is not selected by default and is valid for session pooling only. When selected, all sessions in the pool are authenticated with the user name and password in effect when the session pool was created. The user name and password in later connection requests are ignored. Proxy sessions cannot be created in homogeneous session mode. Sets the SessionHomogeneous DBPARM.
Minimum Number of Sessions	Integer for the minimum number of database connection sessions; value is 1 by default. Sets the CSMin DBPARM. This value is ignored when the SessionHomogeneous DBPARM is set to false.
Maximum Number of Sessions	Integer for the maximum number of database connection sessions; value is 100 by default. Sets the CSMax DBPARM.
Increment	Integer for database connection increments per session; value is 1 by default. Sets the CSIncr DBPARM. This value is ignored when the SessionHomogeneous DBPARM is set to false.
Pool Creator	User name used to create the connection or session pool when the pool is not already created. Sets the PoolCreator DBParm to a string for the user name prior to the database connection. If you do not provide a value for the PoolCreator DBParm, the Transaction object's LogID and LogPass properties are used to create the pooling.
Password	Password used to create the connection or session pool when the pool is not already created. Sets the PoolPwd DBParm to a string for the password for the pool creator.

Defining the Oracle database interface

Specifying the Oracle server connect descriptor

To define a connection through an Oracle database interface, you must create a database profile by supplying values for at least the basic connection parameters in the Database Profile Setup dialog box for your Oracle interface. You can then select this profile at any time to connect to your database in the development environment.

For information on how to define a database profile, see Using database profiles.

Specifying the Oracle server connect descriptor

To connect to an Oracle database server that resides on a network, you must specify the proper connect descriptor in the Server box on the Connection tab of the Database Profile Setup dialog box for your Oracle interface. The connect descriptor specifies the connection parameters that Oracle uses to access the database.

For help determining the proper connect descriptor for your environment, see your Oracle documentation or system administrator.

Specifying a connect descriptor

The syntax of the connect descriptor depends on the Oracle client software you are using.

If you are using Net9 or later, the syntax is:

OracleServiceName

If you are using SQL*Net version 2.x, the syntax is:

@ TNS: OracleServiceName

Parameter	Description
@	The at ( @ ) sign is required
TNS	The identifier for the Oracle Transparent Network Substrate (TNS) technology
:	The colon ( : ) is required
OracleServiceName	The service name assigned to your server in the Oracle configuration file for your platform

Net9 example

To use Net9 client software to connect to the service named ORA9, type the following connect descriptor in the Server box on the Connection tab of the Database Profile Setup dialog box for Oracle9i and later: ORA9.

Using Oracle stored procedures as a data source

What is an Oracle stored procedure?
What you can do with Oracle stored procedures
Using Oracle stored procedures with result sets
Using a large-object output parameter
RPC calls to stored procedures with array parameters

This section describes how you can use Oracle stored procedures.

What is an Oracle stored procedure?

Oracle defines a stored procedure (or function) as a named PL/SQL program unit that logically groups a set of SQL and other PL/SQL programming language statements together to perform a specific task.

Stored procedures can take parameters and return one or more result sets (also called cursor variables). You create stored procedures in your schema and store them in the data dictionary for use by multiple users.

What you can do with Oracle stored procedures

Ways to use Oracle stored procedures

You can use an Oracle stored procedure in the following ways in your PowerBuilder application:

As a data source for DataWindow objects
Called by an embedded SQL DECLARE PROCEDURE statement in a PowerBuilder application (includes support for fetching against stored procedures with result sets)
Called as an external function or subroutine in a PowerBuilder application by using the RPCFUNC keyword when you declare the procedure

For information about the syntax for using the DECLARE PROCEDURE statement with the RPCFUNC keyword, see the PowerScript Reference.

Procedures with a single result set

You can use stored procedures that return a single result set in DataWindow objects and embedded SQL, but not when using the RPCFUNC keyword to declare the stored procedure as an external function or subroutine.

Procedures with multiple result sets

You can use procedures that return multiple result sets only in embedded SQL. Multiple result sets are not supported in DataWindows, reports, or with the RPCFUNC keyword.

Using Oracle stored procedures with result sets

Overview of basic steps

The following procedure assumes you are creating the stored procedure in the ISQL view of the Database painter in PowerBuilder.

To use an Oracle stored procedure with a result set:

Set up the ISQL view of the Database painter to create the stored procedure.
Create the stored procedure with a result set as an IN OUT (reference) parameter.
Create DataWindow objects that use the stored procedure as a data source.

Setting up the Database painter

When you create a stored procedure in the ISQL view of the Database painter, you must change the default SQL statement terminator character to one that you do not plan to use in your stored procedure syntax.

The default SQL terminator character for the Database painter is a semicolon (;). If you plan to use a semicolon in your Oracle stored procedure syntax, you must change the painter's terminator character to something other than a semicolon to avoid conflicts. A good choice is the backquote ( ` ) character.

To change the default SQL terminator character in the Database painter:

Connect to your Oracle database in PowerBuilder as the System user.

For instructions, see Defining the Oracle database interface.
Open the Database painter.
Select Design>Options from the menu bar.

The Database Preferences dialog box displays. If necessary, click the General tab to display the General property page.
Type the character you want (for example, a backquote) in the SQL Terminator Character box.
Click Apply or OK.

The SQL Terminator Character setting is applied to the current connection and all future connections (until you change it).

Creating the stored procedure

After setting up the Database painter, you can create an Oracle stored procedure that has a result set as an IN OUT (reference) parameter. PowerBuilder retrieves the result set to populate a DataWindow object.

There are many ways to create stored procedures with result sets. The following procedure describes one possible method that you can use.

For information about when you can use stored procedures with single and multiple result sets, see What you can do with Oracle stored procedures.

To create Oracle stored procedures with result sets:

Make sure your Oracle user account has the necessary database access and privileges to access Oracle objects (such as tables and procedures).

Without the appropriate access and privileges, you will be unable to create Oracle stored procedures.

Assume the following table named tt exists in your Oracle database:

a	b	c
1	Newman	sysdate
2	Everett	sysdate

Create an Oracle package that holds the result set type and stored procedure. The result type must match your table definition.

For example, the following statement creates an Oracle package named spm that holds a result set type named rctl and a stored procedure named proc1. The tt%ROWTYPE attribute defines rctl to contain all of the columns in table tt. The procedure proc1 takes one parameter, a cursor variable named rc1 that is an IN OUT parameter of type rctl.
```
CREATE OR REPLACE PACKAGE spm
   IS TYPE rctl IS REF CURSOR
   RETURN tt%ROWTYPE;
   PROCEDURE proc1(rc1 IN OUT rctl);END;`
```
Create the Oracle stored procedure separately from the package you defined.

The following examples show how to create two stored procedures: spm_proc 1 (returns a single result set) and spm_proc2 (returns multiple result sets).

The IN OUT specification means that PowerBuilder passes the cursor variable (rc1 or rc2) by reference to the Oracle procedure and expects the procedure to open the cursor. After the procedure call, PowerBuilder fetches the result set from the cursor and then closes the cursor.

spm_proc1 example for DataWindow objects

The following statements create spm_proc1 which returns one result set. You can use this procedure as the data source for a DataWindow object in PowerBuilder.
```
CREATE OR REPLACE PROCEDURE spm_proc1(rc1 IN OUT spm.rctl)
AS
BEGIN
   OPEN rc1 FOR SELECT * FROM tt;
END;`
```
spm_proc2 example for embedded SQL

The following statements create spm_proc2 which returns two result sets. You can use this procedure only in embedded SQL.
```
CREATE OR REPLACE PROCEDURE spm_proc2 (rc1 IN OUT spm.rctl, rc2 IN OUT spm.rctl)
AS
BEGIN
   OPEN rc1 FOR SELECT * FROM tt ORDER BY 1;
   OPEN rc2 FOR SELECT * FROM tt ORDER BY 2;END;`
```

Error checking

If necessary, check the Oracle system table public.user_errors for a list of errors.

Creating the DataWindow object

After you create the stored procedure, you can define the DataWindow objects that uses the stored procedure as a data source.

You can use Oracle stored procedures that return a single result set in a DataWindow object. If your stored procedure returns multiple result sets, you must use embedded SQL commands to access it.

The following procedure assumes that your Oracle stored procedure returns only a single result set.

To create a DataWindow object using an Oracle stored procedure with a result set:

Select a presentation style on the DataWindow page of the New dialog box and click OK.
Select the Stored Procedure icon and click OK.

The Select Stored Procedure wizard page displays, listing the stored procedures available in your database.
Select the stored procedure you want to use as a data source, and click Next.
Complete the wizard to define the DataWindow object.

When you preview the DataWindow object or call Retrieve, PowerBuilder fetches the result set from the cursor in order to populate the DataWindow object. If you selected Retrieve on Preview on the Choose Data Source page in the wizard, the result set displays in the Preview view when the DataWindow opens.

Using a large-object output parameter

You can define a large object (LOB) as an output parameter for an Oracle stored procedure or function to retrieve large-object data. There is no limit on the number of LOB output arguments that can be defined for each stored procedure or function.

In Oracle 10g, the maximum size of LOB datatypes has been increased from 4 gigabytes minus 1 to 4 gigabytes minus 1 multiplied by the block size of the database. For a database with a block size of 32K, the maximum size is 128 terabytes.

RPC calls to stored procedures with array parameters

If your application performs a remote procedure call (RPC) that passes an array parameter to an Oracle stored procedure, the array size in the stored procedure must not be zero. If the array size is uninitialized (has no size), the PBVM returns an error.

For Oracle 18c and 19c, the string array parameter can only be the VarChar type and VarChar2 type, and cannot be the NVarChar2, Char, or NChar type.

For Oracle 18c and 19c, the date array parameter can only be the Date type (cannot be the DateTime type).

Using Oracle user-defined types

PowerBuilder supports SQL CREATE TYPE and CREATE TABLE statements for Oracle user-defined types (objects) in the ISQL view of the Database painter. It correctly handles SQL SELECT, INSERT, UPDATE, and DELETE statements for user-defined types in the Database and DataWindow painters.

This means that using the Oracle native database interfaces in PowerBuilder, you can:

Do this	In
Use Oracle syntax to create user-defined types	Database painter
Use Oracle syntax to create tables with columns that reference user-defined types	Database painter
View columns in Oracle tables that reference user-defined types	Database painter
Manipulate data in Oracle tables that have user-defined types	Database painter DataWindow painter DataWindow objects
Export Oracle table syntax containing user-defined types to a log file	Database painter
Invoke methods	DataWindow object painter (Compute tab in SQL Toolbox)

Example

Here is a simple example that shows how you might create and use Oracle user-defined types in PowerBuilder.

For more information about Oracle user-defined types, see your Oracle documentation.

To create and use Oracle user-defined types:

In the ISQL view of the Database painter, create two Oracle user-defined types: ball_stats_type and player_type.

Here is the Oracle syntax to create ball_stats_type. Notice that the ball_stats object of type ball_stats_type has a method associated with it called get_avg.
```
CREATE OR REPLACE TYPE ball_stats_type AS OBJECT (bat_avg NUMBER(4,3),rbi NUMBER(3),MEMBER FUNCTION get_avg RETURN NUMBER,PRAGMA RESTRICT_REFERENCES (get_avg,WNDS,RNPS,WNPS));
CREATE OR REPLACE TYPE BODY ball_stats_type ASMEMBER FUNCTION get_avg RETURN NUMBER ISBEGINRETURN SELF.bat_avg;
END;
END;
```
Here is the Oracle SQL syntax to create player_type. Player_type references the user-defined type ball_stats_type. PowerBuilder supports such nesting graphically in the Database, DataWindow, and Table painters (see step 3).
```
CREATE TYPE player_type AS OBJECT (player_no NUMBER(2),player_name VARCHAR2(30),ball_stats ball_stats_type);
```
In the Database painter, create a table named lineup that references these user-defined types.

Here is the Oracle SQL syntax to create the lineup table and insert a row. Lineup references the player_type user-defined type.
```
CREATE TABLE lineup (position NUMBER(2) NOT NULL, player player_type);
INSERT INTO lineup VALUES (1,player_type (15, 'Dustin Pedroia', ball_stats_type (0.317, 50)));
```
Display the lineup table in the Database or DataWindow painter.

PowerBuilder uses the following structure->member notation to display the table:
```
lineup
======
position
player->player_no
player->player_name
player->ball_stats->bat_avg
player->ball_stats->rbi
```
To access the get_avg method of the object ball_stats contained in the object column player, use the following structure->member notation when defining a computed column for the DataWindow object. For example, when working in the DataWindow painter, you could use this notation on the Compute tab in the SQL Toolbox:
```
player->ball_stats->get_avg()
```

Support for HA event notification

Oracle Real Application Clusters (RAC) is a cluster database that uses a shared cache architecture. In Oracle 10g Release 2, a High Availability (HA) client connected to an RAC database can register a callback to indicate that it wants the server to notify it in case of a database failure event that affects a connection made by the client.

To take advantage of this feature, PowerBuilder users can script the DBNotification event of the Transaction object. For more information, see the description of the DBNotification event and the HANotification database parameter in the section called “DBNotification” in PowerScript Reference and the section called “HANotification” in Connection Reference.

ORA driver support for Oracle 11g features

In addition to support for Oracle 11g session pooling and connection pooling, the ORA driver adds support for other 11g 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. You 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 (Oracle 10.2 feature)

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 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 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 HomogeneousSession 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.

Load balancing

The Oracle Real Application Clusters (RAC) database option allows a single database to be hosted in multiple instances on multiple nodes of the database server. This adds high availability and failover capacity to the database. Availability is improved since, if one node fails, another node can assume its workload. All instances have access to the whole database. The shared disk method of clustering databases used by the RAC option increases scalability because nodes can be added or freed as required.

In RAC environments, session pools can use service metrics received from the RAC load balancing advisory to balance application session requests. The work requests coming into the session pool can then be distributed across the instances of RAC based on current service performance.

Connect time load balancing

Balancing of work requests occur at two different times: connect time and runtime. Connect time load balancing occurs when a session is first created by the application. This ensures that sessions that are part of the pool are well distributed across RAC instances, and that sessions on each of the instances get a chance to execute work.

For session pools that support services at one instance only, the first available session in the pool is adequate. When the pool supports services that span multiple instances, there is a need to distribute the work requests across instances so that the instances that are providing better service or have greater capacity get more requests.

Runtime connection load balancing

Runtime connection load balancing basically routs work requests to the sessions in a session pool that best serve the work. Runtime connection load balancing is enabled by default when an Oracle 11.1 or higher client is connected to a 10.2 or higher Oracle server using OCI session pooling.

The DBPARM parameter, RTConnBalancing, supports the runtime connection load balancing feature. It is available only when the Pooling parameter is set to Session Pooling, and it can be set before connection only. By default, when you select Session Pooling for the pooling type, the RTConnBalancing value is true.

Prev	Up	Next
Using Microsoft SQL Server	Home	Using DirectConnect

Connecting to Your DatabaseWorking with Native Database Interfaces