Course Overview


Oracle Pro-C

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

Customer Reviews


Thanks to Xpertised and the tutor who walked me through all the topics with Practical exposure which is helping me in my current project.
-Waseem

Course was quite helpful in terms of understanding of concepts and practicality. Its really a very friendly environment to learn. The timing were mutually chosen, as we both are working professional. I am quite satisfied with the course.
-Tanmoy

...more
Share:

For Batch Details
Call us at: +91 7259222234

Not sure? Consult Our Experts

Looking for a Training for

Myself

My Team/Organization

I agree to be contacted over mail or phone

or
Call us at: +91 7259222234