This section describes how to use the native Oracle database interfaces in InfoMaker.
InfoMaker provides two Oracle database interfaces. These interfaces use different DLLs and access different versions of Oracle.
The ORA database interface allows you to connect to Oracle 11g/12c servers using Oracle 11g/12c Database Client or Oracle 11g/12c 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.
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.
The Oracle database interfaces support the Oracle datatypes listed in the following table in reports:
|
Bfile |
NChar (Oracle9i and later only) |
|
Blob |
Number |
|
Char |
NVarChar2 (Oracle9i and later only) |
|
Clob |
Raw |
|
Date |
TimeStamp (Oracle9i and later only) |
|
Float |
VarChar |
|
Long |
VarChar2 |
|
LongRaw |
|
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 Oracle9i TimeStamp datatype 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.
You must install the software components in the following figure to access an Oracle database in InfoMaker.
Figure: Components of an Oracle connection
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.
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.
For instructions on defining the Oracle database interface in InfoMaker, see Defining the Oracle database interface.
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.
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.
This section describes how you can use Oracle stored procedures.
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.
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.
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:
-
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.
What InfoMaker supports
When you use the O84 and O90 database interfaces, 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.
What you can do
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()
For instructions on connecting to the database, see Connecting to a database.
