Contents

Title and Copyright Information

Send Us Your Comments

Preface

Who Should Read This Manual?

How This Manual Is Organized

Conventions Used in This Manual

Notation

ANSI Compliance

Related Publications

1 Writing a Pro*PL/I Program

Programming Guidelines

Comments

Continuation Lines

Embedded SQL Syntax

Host Variable Names

MAXLITERAL Default Value

Nulls

Operators, Logical

Operators, Relational

PL/I Versions

Preprocessor

Quotation Marks and Apostrophes

Scope of Variables

SQL Statement Terminator

Statement Labels

Required Declarations and SQL Statements

The Declare Section

Using the INCLUDE Statement

Event and Error Handling

Host Variables

Declaring Host Variables

Referencing Host Variables

IndicatorVariables

Declaring Indicator Variables

Referencing Indicator Variables

Host Arrays

Declaring Host Arrays

Referencing Host Arrays

Pointers as Host Variables

CHARACTER VARYING

VARYINGReturning Nulls to a CHARACTER (N)

Handling Character Data

Effects of the MODE Option

The Oracle Datatypes

Internal Datatypes

External Datatypes

Datatype Conversion

Datatype Equivalencing

Host Variable Equivalencing

Embedding PL/SQL

Using Host Variables with PL/SQL

Using Indicator Variables with PL/SQL

SQLCHECK

Connecting to Oracle

Automatic Logins

Concurrent Logins

2 Error Handling and Diagnostics

SQLSTATE, the SQLCA, and SQLCODE

Declaring SQLSTATE

SQLSTATE Values

Using SQLSTATE

Declaring SQLCODE

Using the SQLCA

Declaring the SQLCA

What's in the SQLCA?

Key Components of Error Reporting

Getting the Full Text of Error Messages

Using the WHENEVER Statement

Scope of WHENEVER

Using the ORACA

Declaring the ORACA

Enabling the ORACA

What's in the ORACA?

3 Running the Pro*PL/I Precompiler

Precompiler Command

Precompiler Options

Default Values

Case Sensitivity

Configuration Files

Scope of Options

DBMS

MODE

Entering Options

Special PL/I Options

Doing Conditional Precompilations

Doing Separate Precompilations

Restrictions

Compiling and Linking

4 Sample Programs

Sample Programs

Sample Program 1: Login and Query

Sample Program 2: Using a Cursor

Sample Program 3: Fetching in Batches

Sample Program 4: Datatype Equivalencing

Sample Program 5: A SQL*Forms User Exit

Sample Program 6: Dynamic SQL Method 1

Sample Program 7: Dynamic SQL Method 2

Sample Program 8: Dynamic SQL Method 3

Sample Program 9: Calling a Stored procedure

5 Implementing Dynamic SQL Method 4

Meeting the Special Requirements of Method 4

What Makes Method 4 Special?

What Information Does Oracle Need?

Where Is the Information Stored?

How is the Information Obtained?

The SQLDA

Introducing the PL/I SQLDA

Declaring a SQLDA

Multiple SQLDAs

The SQLDA Variables

Datatypes in the SQLDA

Handling NULL/NOT NULL Datatypes

The Basic Steps

A Closer Look at Each Step

Declare a Host String

Set the Size of the Descriptors

Declare the SQLDAs

Declare the Data Buffers

Initialize the Descriptors

Get the Query Text into the Host String

PREPARE the Query from the Host String

DECLARE a Cursor

DESCRIBE the Bind Variables

VariablesReset Maximum Number of Bind

Get Values for Bind Variables

OPEN the Cursor

DESCRIBE the SelectList

Adjust the Select Descriptor Values

FETCH A Row from the Active Set

Process the Select-List Items

CLOSE the Cursor

Using Host Arrays

Sample 10: Dynamic SQL Method 4 Program

A Differences from Previous Release

Topics

B Operating System Dependencies

Topics

Index