Oracle Pro-C

Introduction

• What is an Oracle Precompiler?
• Why Use the Oracle Pro*C/C++ Precompiler
• Why Use SQL
• Why Use PL/SQL
• Pro*C/C++ Precompiler Benefits

Precompiler Concepts

• Key Concepts of Embedded SQL Programming
  • Embedded SQL Statements
  • Embedded SQL Syntax
  • Static Versus Dynamic SQL Statements
  • Embedded PL/SQL Blocks
  • Host and Indicator Variables
  • Oracle Datatypes
  • Arrays
  • Datatype Equivalencing
  • Private SQL Areas, Cursors, and Active Sets
  • Transactions
  • Errors and Warnings
• Steps in Developing an Embedded SQL Application
• Guidelines for Programming
  • Comments
  • Constants
  • Declare Section
  • Delimiters
  • File Length
  • Function Prototyping
  • Host Variable Names
  • Line Continuation
  • Line Length
  • MAXLITERAL Default Value
  • Operators
  • Statement Terminator
• Conditional Precompilation
  • Symbol Definition
  • Example SELECT Statement
• Precompile Separately
• Guidelines
• Compile and Link
• Example Tables
• Example Data
• Example Program: A Simple Query

Database Concepts

• Connect to the Database
  • Using the ALTER AUTHORIZATION Clause to Change Passwords
  • Connecting Using Oracle Net
  • Automatic Connects
• Advanced Connection Options
  • Some Preliminaries
  • Concurrent Logons
  • Default Databases and Connections
  • Explicit Connections
  • Implicit Connections
• Definitions of Transactions Terms
• How Transactions Guard Your Database
• How to Begin and End Transactions
• Using the COMMIT Statement
• Using the SAVEPOINT Statement
• The ROLLBACK Statement
  • Statement-Level Rollbacks
• The RELEASE Option
• The SET TRANSACTION Statement
• Override Default Locking
  • Using FOR UPDATE OF
  • Using LOCK TABLE
• Fetch Across COMMITs
• Distributed Transactions Handling
• Guidelines
  • Designing Applications
  • Obtaining Locks
  • Using PL/SQL
• Datatypes and Host Variables

Oracle Datatypes

• Internal Datatypes
• External Datatypes
• Additional External Datatypes
• Host Variables
  • Host Variable Declaration
  • Host Variable Referencing
• Indicator Variables
  • The INDICATOR Keyword
  • Example of INDICATOR Variable Usage
  • INDICATOR Variable Guidelines
  • Oracle Restrictions
• VARCHAR Variables
  • VARCHAR Variable Declaration
  • VARCHAR Variable Referencing
  • Return NULLs to a VARCHAR Variable
  • Insert NULLs Using VARCHAR Variables
  • Pass VARCHAR Variables to a Function
  • Find the Length of the VARCHAR Array Component
  • Example Program: Using sqlvcp()
• Lab
• Cursor Variables
  • Declare a Cursor Variable
  • Allocate a Cursor Variable
  • Open a Cursor Variable
  • Closing and Freeing a Cursor Variable
  • Cursor Variables with the OCI (Release 7 Only)
  • Restrictions
  • Example: cv_demo.sql and sample11.pc
• CONTEXT Variables
• Universal ROWIDs
  • SQLRowidGet()
• Host Structures
  • Host Structures and Arrays
  • PL/SQL Records
  • Nested Structures and Unions
  • Host Indicator Structures
  • Example Program: Cursor and a Host Structure
• Pointer Variables
  • Pointer Variable Declaration
  • Pointer Variable Referencing
  • Structure Pointers

Advanced topics

• Character Data
  • Precompiler Option CHAR_MAP
  • Inline Usage of the CHAR_MAP Option
  • Effect of the DBMS and CHAR_MAP Options
  • VARCHAR Variables and Pointers
  • Unicode Variables
• Datatype Conversion
• Datatype Equivalencing
  • Host Variable Equivalencing
  • User-Defined Type Equivalencing
  • CHARF External Datatype
  • The EXEC SQL VAR and TYPE Directives
  • Example: Datatype Equivalencing (sample4.pc):
• The C Preprocessor
  • How the Pro*C/C++ Preprocessor Works
  • Preprocessor Directives
  • ORA_PROC Macro
  • Location of Header File Specification
  • Some Preprocessor Examples
  • SQL Statements Not Allowed in #include
  • Include the SQLCA, ORACA, and SQLDA
  • EXEC SQL INCLUDE and #include Summary
  • Defined Macros
  • Include Files
• Precompiled Header Files
  • Precompiled Header File Creation
  • Use of the Precompiled Header Files
  • Examples
  • Effects of Options
  • Usage Notes
• The Oracle Preprocessor
  • Symbol Definition
  • An Oracle Preprocessor Example
• Evaluation of Numeric Constants
  • Numeric Constants in Pro*C/C++
  • Numeric Constant Rules and Examples 
• SQLLIB Extensions for OCI Release 8 Interoperability
  • Runtime Context in the OCI Release 8 Environment
  • Parameters in the OCI Release 8 Environment Handle
• Interface to OCI Release 8
  • SQLEnvGet()
  • SQLSvcCtxGet()
  • Embedded OCI Release 8 Calls
• Embedded OCI Release 7 Calls
  • Set Up the LDA
  • Remote and Multiple Connections
• New Names for SQLLIB Public Functions
• X/Open Application Development
  • Oracle-Specific Issues

Embedded SQL

• Host Variables
  • Output versus Input Host Variables
• Indicator Variables
  • Insert NULLs
  • Returned NULLs
  • Fetch NULLs
  • Test for NULLs
  • Truncated Values
• The Basic SQL Statements
  • The SELECT Statement
  • The INSERT Statement
  • The UPDATE Statement
  • The DELETE Statement
  • The WHERE Clause
  • The DML Returning Clause
  • Cursors
• The DECLARE CURSOR Statement
  • The OPEN Statement
  • The FETCH Statement
  • The CLOSE Statement
• Scrollable Cursors
  • Using Scrollable Cursors
  • The CLOSE_ON_COMMIT Precompiler Option
  • The PREFETCH Precompiler Option
• Optimizer Hints
  • Issuing Hints
• The CURRENT OF Clause
  • Restrictions
• The Cursor Statements
• A Complete Example Using Non-Scrollable Cursor
• A Complete Example Using Scrollable Cursor
• Positioned Update

Embedded PL/SQL

• Advantages of PL/SQL
  • Better Performance
  • Integration with Oracle
  • Cursor FOR Loops
  • Procedures and Functions
  • Packages
  • PL/SQL Tables
  • User-Defined Records
• Embedded PL/SQL Blocks
• Host Variables
  • Example: Using Host Variables with PL/SQL
  • Complex Example
  • VARCHAR Pseudotype
  • Restriction
• Indicator Variables
  • NULLs Handling
  • Truncated Values
• Host Arrays
  • ARRAYLEN Statement
  • Optional Keyword EXECUTE
• Cursor Usage in Embedded PL/SQL
• Stored PL/SQL and Java Subprograms
  • Creating Stored Subprograms
  • Calling a Stored PL/SQL or Java Subprogram
  • Getting Information about Stored Subprograms
• External Procedures
  • Restrictions on External Procedures
  • Creating the External Procedure
  • SQLExtProcError()
• Using Dynamic SQL

Host Arrays

• Why Use Arrays?
• Declaring Host Arrays
  • Restrictions
  • Maximum Size of Arrays
• Using Arrays in SQL Statements
  • Referencing Host Arrays
  • Using Indicator Arrays
  • Oracle Restrictions
  • ANSI Restriction and Requirements
• Selecting into Arrays
  • Cursor Fetches
  • Using sqlca.sqlerrd[2]
  • Number of Rows Fetched
  • Scrollable Cursor Fetches
  • Sample Program 3: Host Arrays
  • Sample Program: Host Arrays Using Scrollable Cursor
  • Host Array Restrictions
  • Fetching NULLs
  • Fetching Truncated Values

Lab

• Inserting with Arrays
  • Inserting with Arrays Restrictions
  • Updating with Arrays
  • Updating with Arrays Restrictions
• Deleting with Arrays
  • Deleting with Arrays Restrictions
• Using the FOR Clause
  • FOR Clause Restrictions.
• Using the WHERE Clause
• Arrays of Structs
  • Arrays of Structs Usage
  • Restrictions on Arrays of Structs
  • Declaring an Array of Structs
  • Variables Guidelines
  • Declaring a Pointer to an Array of Structs
  • Examples

Handling Runtime Errors

• The Need for Error Handling
• Error Handling Alternatives
  • Status Variables
  • The SQL Communications Area
• The SQLSTATE Status Variable
  • Declaring SQLSTATE
  • SQLSTATE Values
  • Using SQLSTATE
• Declaring SQLCODE
• Key Components of Error Reporting Using the SQLCA
  • Status Codes
  • Warning Flags
  • Rows-Processed Count
  • Parse Error Offsets
  • Error Message Text
• Using the SQL Communications Area (SQLCA)
  • Declaring the SQLCA
  • SQLCA Contents
  • SQLCA Structure
  • PL/SQL Considerations
• Getting the Full Text of Error Messages
• Using the WHENEVER Directive
  • WHENEVER Conditions
  • WHENEVER Actions
  • WHENEVER Examples
  • Use of DO BREAK and DO CONTINUE
  • Scope of WHENEVER
  • Guidelines for WHENEVER
• Obtaining the Text of SQL Statements
  • Restrictions
  • Example Program
• Using the Oracle Communications Area (ORACA)
  • Declaring the ORACA
  • Enabling the ORACA
  • ORACA Contents
  • Choosing Runtime Options
  • Structure of the ORACA
  • ORACA Example

Precompiler Options

• The Precompiler Command
  • Case Sensitivity
• Precompiler Options
  • Configuration Files
  • Precedence of Option Values
  • Macro and Micro Options
  • What Occurs During Precompilation?
  • Scope of Options
• Quick Reference
• Entering Options
  • On the Command Line
  • Inline
• Using the Precompiler Options
  • AUTO_CONNECT, CHAR_MAP, CLOSE_ON_COMMIT, CODE, COMP_CHARSET, CONFIG, CPP_SUFFIX,
  • DBMS, DEF_SQLCODE, DEFINE, DURATION,
  • DYNAMIC, ERRORS, ERRTYPE, FIPS, HEADER, HOLD_CURSOR, INAME, INCLUDE, INTYPE, LINES, LNAME, LTYPE,
  • MAXLITERAL, MAXOPENCURSORS, MODE,
  • NLS_CHAR, NLS_LOCAL, OBJECTS, ONAME,
  • ORACA, PAGELEN, PARSE, PREFETCH,
  • RELEASE_CURSOR, SELECT_ERROR,
  • SQLCHECK, SYS_INCLUDE, THREADS,
  • TYPE_CODE, UNSAFE_NULL, USERID,
  • UTF16_CHARSET, VARCHAR, VERSION

C++ Applications

• Understanding C++ Support
  • No Special Macro Processing
• Precompiling for C++
  • Code Generation
  • Parsing Code
  • Output Filename Extension
  • System Header Files
• Example Programs
  • cppdemo1.pc
  • cppdemo2.pc
  • cppdemo3.pc

Oracle Dynamic SQL

• What is Dynamic SQL?
• Advantages and Disadvantages of Dynamic SQL
• When to Use Dynamic SQL
• Requirements for Dynamic SQL Statements
• How Dynamic SQL Statements are Processed
• Methods for Using Dynamic SQL
  • Method 1
  • Method 2
  • Method 3
  • Method 4
  • Guidelines
• Using Method 1
  • Example Program: Dynamic SQL Method 1
• Using Method 2
  • The USING Clause
  • Example Program: Dynamic SQL Method 2
• Using Method 3
  • PREPARE
  • DECLARE
  • OPEN
  • FETCH
  • CLOSE
  • Example Program: Dynamic SQL Method 3
• Using Method 4
  • Need for the SQLDA
  • The DESCRIBE Statement
  • What is a SQLDA?
  • Implementing Oracle Method 4
  • Restriction
• Using the DECLARE STATEMENT Statement
  • Using Host Arrays
• Using PL/SQL
  • With Method 1
  • With Method 2
  • With Method 3
  • With Oracle Method 4

18 Collections

• Collections
  • Nested Tables
  • Varrays
  • C and Collections
• Descriptors for Collections
  • Declarations for Host and Indicator Variables
  • Manipulating Collections
  • Rules for Access
  • Indicator Variables
• OBJECT GET and SET
• Collection Statements
  • COLLECTION GET
  • COLLECTION SET
  • COLLECTION RESET
  • COLLECTION APPEND
  • COLLECTION TRIM
  • COLLECTION DESCRIBE
  • Rules for the Use of Collections
• Collection Example Code
  • Type and Table Creation
  • GET and SET Example
  • DESCRIBE Example
  • RESET Example
  • Example Program:coldemo1.pc