Oracle

Supported versions for Oracle

InfoMaker provides two 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 12c, 18c, 19c, 21c, 23c)	PBORA.dll

The ORA database interface allows you to connect to Oracle 12c/18c/19c/21c/23c servers using Oracle Database Client or Oracle 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.

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

For more information

Updated information about supported versions of databases might be available electronically on the the Appeon Support Web site at https://support.appeon.com/ or in the InfoMaker Release Bulletin.

Supported Oracle datatypes

Datatype conversion

The Oracle database interfaces support the Oracle datatypes listed in the following table in reports:

Binary_Float	LongRaw
Binary_Double	NChar
Boolean (Oracle 23c only)	NCLOB
Bfile	Number
Blob	NVarChar2
Char	Raw
Clob	TimeStamp
Date	VarChar
Float	VarChar2
Long	XMLType (partial support, ORA driver 12c or earlier only)

Accessing Unicode data

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

Using the O84 database interface, InfoMaker detects whether the Oracle client variable NS_LANG is set. If the variable is set to a value that requires UTF-8 or DBCS characters, InfoMaker converts command text (such as SELECT * FROM emp) to the appropriate character set before sending the command to the database. However, if DisableBind is set to 0 (the default), InfoMaker always binds string data as Unicode data.

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, NVARCHAR, 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.

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 InfoMaker is 0-6.

Datatype conversion

When you retrieve or update columns, in general InfoMaker 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.

Number datatype converted to decimal

When a DataWindow object is defined in InfoMaker, the Oracle datatype number(size,d) is mapped to a decimal datatype. In InfoMaker, 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 InfoMaker.

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 InfoMaker, 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 InfoMaker.
(ORA driver only) Determine whether you want to use connection pooling or session pooling.

Preparing an Oracle database for use with InfoMaker involves these three 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 O84 interface you can access an Oracle 8.0.x or Oracle8i 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 InfoMaker 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 InfoMaker that you are running

Required client software versions

To use the Oracle 8.0.x and Oracle8i (O84) interface or the Oracle9i (O90) interface, you must install Oracle Net client software version 8.0.4 or later.
Make sure the Oracle SQL*Net or Net client software is properly configured so that you can connect to the Oracle database server at your site.

Installing SQL*Net software places the correct configuration file in the Oracle directory on your computer. For example, if you are using SQL*Net version 2.x, the required configuration file is called TNSNAMES.ORA.

The configuration file provides information that Oracle needs to find and connect to the database server at your site. To modify and view the information in TNSNAMES.ORA, use an Oracle tool designed to edit the configuration file (such as Oracle Network Manager or the SQL*Net Easy Configuration utility).

For information about setting up Oracle configuration files, see your SQL*Net or 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

In the InfoMaker Setup program, select the Typical install or select the Custom install and select the Oracle database interfaces you require.

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

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 InfoMaker database profile to access the database.

Step 4: Determine whether to use connection or session pooling

Oracle client interface (OCI) pooling for InfoMaker 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 InfoMaker.

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 O10 database driver used in InfoMaker to connect to the 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 or later. 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 Net version 8.x 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

Net version 8.x example

To use Net version 8.x or later client software to connect to the service named ORA8, type the following connect descriptor in the Server box on the Connection tab of the Database Profile Setup dialog box for Oracle 8.x and later:ORA8.

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

In your InfoMaker application, you can use an Oracle stored procedure as a data source for reports.

Procedures with a single result set

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

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

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 reports 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 InfoMaker 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 property sheet 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. InfoMaker retrieves the result set to populate a report.

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 amed 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 example shows how to create a stored procedure named spm_proc 1 that returns a single result set.

The IN OUT specification means that InfoMaker 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, InfoMaker fetches the result set from the cursor and then closes the cursor.

spm_proc1 example for reports

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

Error checking

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

Creating the report

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

You can use Oracle stored procedures that return a single result set in a report.

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

To create a report 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 report.

When you preview the report, InfoMaker fetches the result set from the cursor in order to populate the report. 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.

For more instructions on defining reports, see the User's Guide.

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 might be defined for each stored procedure or function.

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

InfoMaker 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 Report painters.

This means that using these database interfaces in InfoMaker, 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 Report painter Reports
Export Oracle table syntax containing use-defined types to a log file	Database painter
Invoke methods of objects columns	Report painter (Compute tab in SQL Toolbox)

Example

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

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

To create and use Oracle 8 and later 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. InfoMaker supports such nesting graphically in the Database, Report, 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 an Oracle 8 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 (5, 'Manny Ramirez', ball_stats_type (0.342, 46)));
```
Display the lineup table in the Database or Report painter.

InfoMaker 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 report. For example, when working in the Report 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, InfoMake 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.

Support for identity column

The ORA driver supports the Identity columns for the Oracle database:

When a DataWindow object is created, the Identity column is automatically set (read from the database). You can modify the Identity column in the Specify Update Properties.
When inserting rows with an Identity column, the Identity column is excluded from the INSERT statement. Its value is generated based on the GENERATED BY DEFAULT.
After a new row is inserted, the value of the Identity column in the row will show after the Update.

Note: The DataWindow automatically gets the biggest value for the Identity column in the newly-added row after the Update. Therefore if the Identity column auto-increments by a negative number (which is supported by Ora Oracle and ADO.NET, but not by ODBC), the value shown for the newly-added row may be incorrect. You can execute a Retrieve on the DataWindow to get the correct value.
You can directly add auto incremental columns for Oracle tables in the IDE database painter, by setting the data type of the columns to either NUMBER IDENTITY or FLOAT IDENTITY. The clause would be:
```
GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 START WITH 1
```

Prev	Up	Next
Microsoft SQL Server	Home	Adaptive Server Enterprise

Connecting to Your DatabaseUsing Native Database Interfaces