Course Content
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
