Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

1 Writing a Pro*FORTRAN Program

• Programming Guidelines
  • Case-sensitivity
  • Comments
  • Continuation Lines
  • Delimiters
  • Embedded SQL Syntax
  • File Length
  • File Naming Restrictions
  • FORTRAN Versions
• Required Declarations and SQL Statements
  • The Declare Section
  • Using the INCLUDE Statement
  • Filename Extensions
  • Search Paths
  • Caution
  • Event and Error Handling
• Host Variable Names
  • Logical and Relational Operators
  • MAXLITERAL Default
  • Nulls
  • Program Units
• Scope of Host Variables
  • Statement Labels
  • Statement Terminator
• Host Variables
  • Declaring Host Variables
  • Example Declarations
  • Repeating Definitions
  • Initialization
  • Constants
  • COMMON Blocks
  • EQUIVALENCE Statement
  • Special Requirements for Subroutines
  • Restrictions
• Referencing Host Variables
  • Restrictions
• Indicator Variables
  • Declaring Indicator Variables
  • Referencing Indicator Variables
  • Restriction
  • Oracle Restrictions
  • ANSI Requirements
• Host Arrays
  • Declaring Host Arrays
  • Restrictions
  • Referencing Host Arrays
  • Using Indicator Arrays
• VARCHAR Host Variables
  • Declaring VARCHAR Variables
  • Referencing VARCHAR Variables
  • Overcoming the Length Limit
• Handling Character Data
  • Effects of the MODE Option
  • CHARACTER*n
  • On Input
  • On Output
  • VARCHAR Variables
  • On Input
  • On Output
• The Oracle Datatypes
  • Internal Datatypes
  • External Datatypes
• Datatype Conversion
• Datatype Equivalencing
  • Host Variable Equivalencing
• Embedding PL/SQL
  • Host Variables
  • VARCHAR Variables
  • Indicator Variables
  • Handling Nulls
  • Handling Truncated Values
  • SQLCHECK
  • Cursor Variables
• Declaring a Cursor Variable
  • Allocating a Cursor Variable
  • Opening a Cursor Variable
  • Opening Indirectly through a Stored PL/SQL Procedure
  • Opening Directly from Your Pro*FORTRAN Application
  • Return Types
  • Fetching from a Cursor Variable
  • Closing a Cursor Variable
  • Restrictions
  • Error Conditions
  • Sample Programs
  • SAMPLE11.SQL
  • SAMPLE11.PFO
• Connecting to Oracle
  • Automatic Logons
  • Concurrent Logons

2 Error Handling and Diagnostics

• Error Handling Alternatives
  • SQLCOD and SQLSTA
  • SQLCA
  • ORACA
• Using Status Variables when MODE={ANSI|ANSI14}
  • Some Historical Information
  • Release 1.5
  • Release 1.6
  • Release 1.7
  • Declaring Status Variables
  • Declaring SQLCOD
  • Declaring SQLSTA
  • Status Variable Combinations
• Using the SQL Communications Area
  • What's in the SQLCA?
  • Declaring the SQLCA
  • Key Components of Error Reporting
  • Status Codes
  • Warning Flags
  • Rows-Processed Count
  • Parse Error Offset
  • Error Message Text
  • Getting the Full Text of Error Messages
  • Using the WHENEVER Statement
  • Scope
  • Careless Usage: Examples
• Using the Oracle Communications Area
  • What's in the ORACA?
  • Declaring the ORACA
  • Enabling the ORACA

3 Sample Programs

• Sample Program 1: Simple Query
• Sample Program 2: Cursor Operations
• Sample Program 3: Fetching in Batches
• Sample Program 4: Datatype Equivalencing
• Sample Program 5: Oracle 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

4 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?
• Understanding the SQL Descriptor Area (SQLDA)
  • Purpose of the SQLDA
  • Multiple SQLDAs
  • Naming Conventions
  • Declaring a SQLDA
• Using the SQLDA Variables and Arrays
  • The N Variable
  • The F Variable
  • The S Array
  • The M Array
  • The C Array
  • The L Array
  • Select Descriptors
  • Bind Descriptors
  • The T Array
  • Select Descriptors
  • Bind Descriptors
  • The V Array
  • Select Descriptors
  • Bind Descriptors
  • The I Array
  • Select Descriptors
  • Bind Descriptors
  • The X Array
  • The Y Array
  • The Z Array
• Some Preliminaries
  • Using SQLADR
  • Restriction
  • Converting Data
  • Internal Datatypes
  • External Datatypes
  • PL/SQL Datatypes
  • Coercing Datatypes
  • Exceptions
  • Extracting Precision and Scale
  • Handling Null/Not Null Datatypes
• The Basic Steps
• A Closer Look at Each Step
  • Declare a Host String
  • Declare the SQLDAs
  • Set the Maximum Number to DESCRIBE
  • Initialize the Descriptors
  • Store the Query Text in the Host String
  • PREPARE the Query from the Host String
  • DECLARE a Cursor
  • DESCRIBE the Bind Variables
  • Reset Number of Placeholders
  • Get Values for Bind Variables
  • OPEN the Cursor
  • DESCRIBE the Select List
  • Reset Number of Select-List Items
  • Reset Length/Datatype of Each Select-List Item
  • FETCH Rows from the Active Set
  • Get and Process Select-List Values
  • CLOSE the Cursor
• Using Host Arrays with Method 4
• Sample Program 10: Dynamic SQL Method 4

A Operating System Dependencies

• System-Specific References for Chapter 1
  • Case-sensitivity
  • Coding Area
  • Continuation Lines
  • FORTRAN Versions
  • Declaring
  • Naming
  • INCLUDE Statements
  • MAXLITERAL Default
• System-Specific Reference for Chapter 3
  • Sample Programs
• System-Specific Reference for Chapter 4
  • SQLADR