Oracle 7
\The Sandbox \PB History & Future \New in PB 4 \DB Enhancements \Oracle 7
Array Fetches
in Oracle 7
Bind Variables
Cached
Statements
Select & DB
Painters
Remote Procedure
Informix
ODBC
Oracle 7
Peformance
Gains
Cache in
Oracle
SQL Server
System 10
Watcom 4.0

This section describes how to use the Powersoft ORACLE OR6 and OR7 database interfaces in PowerBuilder or InfoMaker.

You may wish to also read the description of Using embedded SQL with ORACLE.

Supported versions

Powersoft supplies two ORACLE database interfaces:

  • OR6
  • OR7

These interfaces use different Powersoft DLLs and access different versions of the ORACLE database software, as summarized in the following table:

Powersoft ORACLE interface

Powersoft interface DLL

ORACLE database software

OR6

PBOR6040.DLL

ORACLE Version 6

OR7

PBOR7040.DLL

ORACLE Version 7

Supported data types

PowerBuilder and InfoMaker support the following ORACLE data types in DataWindows, reports, and embedded SQL:

Char

Number

Date

Raw

Float (ORACLE 7 only)

VarChar (ORACLE 6 only)

Long

VarChar2 (ORACLE 7 only)

LongRaw

Data type conversion
When you retrieve or update columns, PowerBuilder or InfoMaker converts data appropriately between the ORACLE data type and the Powersoft data type.

Preparing to use the database

Before you define the interface and connect to an ORACLE database from PowerBuilder or InfoMaker, follow these steps to prepare the database.

This procedure applies to both Version 6 and Version 7 ORACLE databases.

To prepare an ORACLE database:

  1. Install the ORACLE database server software, following the instructions in your ORACLE documentation.
  2. You must obtain the database server software from Oracle Corporation.

  3. Make sure there is only one copy of ORA7WIN.DLL and/or one copy of ORA6WIN.DLL on your computer.
  4. If you want to access both ORACLE Version 6 and ORACLE Version 7 databases, you can have one copy of each file on your computer.

  5. Install the SQL*Net client software, following the instructions in your SQL*Net documentation.
  6. You must obtain the SQL*Net client software from Oracle Corporation.

    Installing the SQL*Net software places the correct configuration file in the ORACLE directory on your computer. In general, this configuration file is named CONFIG.ORA if you are using SQL*Net for DOS, and ORACLE.INI if you are using SQL*Net for Windows.

  7. Install the appropriate Powersoft ORACLE database interface (OR6 or OR7) on your computer.
  8. Make sure the files listed in the following table are installed on your computer, and that the directories containing these files appear in your program search path.

ORACLE database

Program search path entries

ORACLE Version 6

PBOR6040.DLL
ORA6WIN.DLL
SQL*Net client software

ORACLE Version 7

PBOR7040.DLL
ORA7WIN.DLL
COREWIN.DLL
SQL*Net client software

  1. Make sure the following line appears in your AUTOEXEC.BAT file to define the SET CONFIG environment variable:
  2. SET CONFIG = ORACLE_configuration_file_pathname

    For example:

    SET CONFIG = C:\ORACLE\ORACLE.INI

  3. Determine the network protocol you are using and the SQL*Net driver that it requires.
  4. You need this information to make sure the appropriate SQL*Net driver is loaded on your computer, as described in step 8. You will also need this information when you specify the ORACLE server connect string in the database profile.

    The following table lists frequently used network protocols and their corresponding SQL*Net for DOS and SQL*Net for Windows drivers:

Network protocol

SQL*Net for DOS driver

SQL*Net for Windows driver

DECnet

SQLDNT.EXE

SQLDNT.DLL

Local

SQLPME.EXE

SQLPME.DLL

Named Pipes

SQLNMP.EXE

SQLNMP.DLL

NetBios

SQLNTB.EXE

SQLNTB.DLL

Novell

SQLSPX.EXE

SQLSPX.DLL

TCP/IP

SQLTCP.EXE

SQLTCP.DLL

Vines

SQLVIN.EXE

SQLVIN.DLL

  1. Make sure the SQL*Net driver required by your network protocol is loaded on your computer.
  2. Make sure you can connect to the ORACLE database server and to the ORACLE database you want to access from outside PowerBuilder or InfoMaker.
  3. Start Windows on your computer.

Defining the database interface

The following table lists the values you should supply for each field in the Database Profile Setup dialog box when defining the Powersoft OR6 or OR7 database interface.

Field

Value

Profile Name

The name of your database profile.

DBMS

OR6 - ORACLE v6.x
or
OR7 - ORACLE v7.x

User ID

Not applicable for use with PowerBuilder or InfoMaker.

Password

Not applicable for use with PowerBuilder or InfoMaker.

Database Name

Not applicable for use with PowerBuilder or InfoMaker.

Prompt for Database information during Connect

Select this checkbox if you want to be prompted for connection information when creating or selecting a profile to connect to the database.

Server Name

The server connect string.

This field is required only when using a networked version of the ORACLE database server. If you are using a local version of the ORACLE database server, leave the Server Name field blank.

Login ID

The login ID of your database server.

In order to properly create the Powersoft repository tables, make sure the first person to connect to the database has sufficient authority to create tables and grant permissions to public.

Login Password

The login password of your database server. The actual password does not display in this field. Small Xs appear in place of the characters you type.

DBParm

Specify DBMS-specific connection parameters in this field.

Specifying the server connect string

In order to connect to a networked version of ORACLE 6 or ORACLE 7, you must specify the proper connect string or connect descriptor in the Server Name field of the Database Profile Setup window. The connect string or connect descriptor specifies the connection parameters that the ORACLE Windows API uses to access the database.

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

Using a connect string or connect descriptor

The SQL*Net client software you are using determines whether you should specify an ORACLE connect string or connect descriptor in the Server Name field, as summarized in the following table:

If you are using

Specify this in Server Name field

SQL*Net for DOS

ORACLE connect string

SQL*Net for Windows Version 1.0 (SQL*Net V1)

ORACLE connect string

SQL*Net for Windows Version 2.0 (SQL*Net V2)

ORACLE connect descriptor

Specifying a connect string

The syntax of the connect string depends on the ORACLE client software you are using: SQL*Net for DOS or SQL*Net for Windows Version 1.0.

    If you are using SQL*Net for DOS, the syntax is:

    @identifier : LogicalServerName

If you are using SQL*Net for Windows Version 1.0, the syntax is:

    @identifier : LogicalServerName : ORACLEInstanceName

Parameter

Description

@

The at ( @ ) sign is required.

identifier

The appropriate SQL*Net communications identifier for your network protocol and driver, as follows:

B       NetBios, SQLNTB driver
D       DECnet, SQLDNT driver
P       Named Pipes, SQLNMP driver
T       TCP/IP, SQLTCP driver
V       Vines, SQLVIN driver
X       Novell, SQLSPX driver

:

The colon ( : ) is required.

LogicalServerName

The name assigned to the database server.

ORACLEInstanceName

The name assigned to the ORACLE instance you are logging on to.

Example 1--To use TCP/IP with SQL*Net for DOS client software to connect to an ORACLE server named LION, enter the following connect string in the Server Name field of the Database Profile Setup dialog box:

    @T:LION

Example 2--To use NetBios with SQL*Net for Windows Version 1.0 client software to connect to an ORACLE server named TIGER under the default ORACLE instance named ORACLE, enter the following connect string in the Server Name field of the Database Profile Setup dialog box:

    @B:TIGER:ORACLE

Specifying a connect descriptor

If you are using SQL*Net for Windows Version 2.0, you must specify an ORACLE connect descriptor in the Server Name field. The syntax is:

    @TNS : ORACLEServiceName

Parameter

Description

@

The at ( @ ) sign is required.

TNS

The identifier for the Oracle Transparent Network Substrate (TNS) technology, on which SQL*Net Version 2.0 is based.

:

The colon ( : ) is required.

ORACLEServiceName

The service name assigned to your server in the TNSNAMES.ORA configuration file. TNSNAMES.ORA is one of the required configuration files for SQL*Net Version 2.0.

For instructions on configuring SQL*Net Version 2.0, see your ORACLE documentation.

Example--To use SQL*Net for Windows Version 2.0 client software to connect to the service named ORA7UNIX, enter the following connect descriptor in the Server Name field of the Database Profile Setup dialog box:

    @TNS:ORA7UNIX

Using ORACLE 7 stored procedures as a data source

You can define DataWindow objects and reports that use an ORACLE 7 PBDBMS stored procedure as their data source. A stored procedure is a group of pre compiled and pre optimized SQL statements that performs some database operation. Stored procedures reside on the database server where they can be accessed as needed.

Using an ORACLE 7 stored procedure as a data source involves two general steps:

  1. Setting up your ORACLE 7 database server
  2. Creating DataWindow objects and reports that use the PBDBMS stored procedure

Setting up the ORACLE 7 database server

The first step in using ORACLE 7 stored procedures is to install special software on the database server and create stored procedures using PBDBMS.Put_Line function calls.

To set up your ORACLE 7 database server:

  1. From PowerBuilder or InfoMaker, connect to your ORACLE 7 database as the System user.
  2. Change the TerminatorCharacter value in Database preferences to ` (back quote).
  3. If you are using PowerBuilder, you can use the Preferences painter to do this or you can edit the [Database] section of the PB.INI file. If you are using InfoMaker, you must edit the [Database] section IM.INI file to set the TerminatorCharacter value.

  4. In the Database Administration painter, open and execute the PBOR7CAT.SQL script.
  5. The PBOR7CAT.SQL script installs the PBDBMS package on the ORACLE 7 database server. The PBDBMS package enables you to use ORACLE 7 PBDBMS stored procedures as data sources for DataWindow objects and reports.

    If you are using PowerBuilder, the PBOR7CAT.SQL script is on Disk 5 of the Deployment Kit. If you are using InfoMaker, it is on Disk 7.

  6. Create the ORACLE 7 stored procedure.
  7. You create the ORACLE 7 stored procedure as you normally do. The only difference is that you must code PBDBMS.Put_Line function calls to build the SQL SELECT statement.

    The PBDBMS.Put_Line function takes one parameter, a string. The SELECT statement is a concatenation of the parameters passed to the Put_Line function. You can call Put_Line repeatedly in the procedure (up to 100 times) until you have built the entire SELECT statement.

    Here is a sample stored procedure

    CREATE PROCEDURE spmdw2 (dbname varchar2, dboth varchar2)
    is mystr varchar2(255);
    BEGIN
    your code here...
    PBDBMS.Put_Line('SELECT Col1,Col2');
    PBDBMS.Put_Line('FROM test');
    END;

    The generated SELECT statement will be:

    SELECT Col1, Col2 FROM test

    A DataWindow object or report using this procedure will have two retrieval arguments: dbname and dboth.

Creating DataWindow objects and reports using the stored procedure

After you set up the ORACLE 7 database server and create the stored procedure, you create the DataWindow object or report using the stored procedure.

To create a DataWindow object or report using an ORACLE 7 PBDBMS stored procedure as its data source:

  1. Set the PBDBMS DBParm parameter to 1 as follows to enable use of an ORACLE 7 PBDBMS stored procedure as a data source:
  2. DBParm = PBDBMS = 1

  3. In the DataWindow or Report painter, click the New button in the Select dialog box.
  4. The New DataWindow or New Report dialog box appears. The Stored Procedure icon displays as a data source.

  5. Select the Stored Procedure data source and define your DataWindow object or report.
  6. You call a DataWindow Retrieve function as you normally do to get the data. The appropriate SELECT statement is generated by the specified stored procedure and accessed internally by the DataWindow through the PBDBMS package on the database server.

Limitations

Using an ORACLE 7 PBDBMS stored procedure as a data source for DataWindow objects and reports has the following limitations:

  • The stored procedure must have no output parameters.
  • The SELECT statement is limited to 255 characters * 100, or 25,500 characters. 
PBL Peeper PB Help PB History
& Future About Us Feedback Site Map

Google
 
Web www.techno-kitten.com
www.sybase.com