Contents

Title and Copyright Information

Preface

• Audience
• Related documents
• Conventions
• Documentation Accessibility
• Technical support

1 Introduction to PL/SQL in the TimesTen Database

• Features of PL/SQL in TimesTen
• Sources of documentation for PL/SQL
• Audiences for this document
  • Developers experienced with Oracle Database and Oracle PL/SQL
  • Developers experienced with TimesTen
• About the TimesTen PL/SQL demos

2 Programming Features in PL/SQL in TimesTen

• PL/SQL blocks
• PL/SQL variables and constants
• PL/SQL built-in functions and SQL functions
• PL/SQL control structures
  • Conditional control
  • Iterative control
  • CONTINUE statement
• How to execute PL/SQL procedures and functions
• How to pass data between an application and PL/SQL
  • Using bind variables from an application
  • IN, OUT, and IN OUT parameter modes
• Use of SQL in PL/SQL programs
  • Static SQL in PL/SQL for queries and DML statements
  • Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)
  • FORALL and BULK COLLECT operations
  • RETURNING INTO clause
  • TimesTen PL/SQL with In-Memory Database Cache
• Use of cursors in PL/SQL programs
• PL/SQL procedures and functions
• PL/SQL packages
  • Package concepts
  • Creating and using packages
• Wrapping PL/SQL source code
• Differences in TimesTen: transaction behavior

3 Data Types in PL/SQL in TimesTen

• Understanding the data type environments
• Understanding and Using PL/SQL data types
  • PL/SQL data type categories
  • Predefined PL/SQL scalar data types
    • PLS_INTEGER and BINARY_INTEGER data types
    • SIMPLE_INTEGER data type
    • ROWID data type
  • PL/SQL composite data types
    • Using collections
    • Using records
  • PL/SQL REF CURSORs
• Data type conversion
  • Conversion between PL/SQL data types
  • Conversion between application data types and PL/SQL or SQL data types
• Differences in TimesTen: data type considerations
  • Conversion between PL/SQL and TimesTen SQL data types
  • Date and timestamp formats: NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT
  • Non-supported data types

4 Errors and Exception Handling

• Understanding exceptions
  • About exceptions
  • Exception types
• Trapping exceptions
  • Trapping predefined TimesTen errors
  • Trapping user-defined exceptions
    • Using the RAISE statement
    • Using the RAISE_APPLICATION_ERROR procedure
• Differences in TimesTen: exception handing and error behavior
  • TimesTen PL/SQL transaction and rollback behavior for unhandled exceptions
  • TimesTen error messages and SQL codes
  • Warnings not visible in PL/SQL
  • Unsupported predefined errors
  • Possibility of runtime errors after clean compile (use of Oracle SQL parser)

5 Examples Using TimesTen SQL in PL/SQL

• Example using the SELECT statement in PL/SQL
• Example using the INSERT statement
• Examples using cursors
  • Fetching values
  • Using the %ROWCOUNT and %NOTFOUND attributes
  • Using cursor FOR loops
• Examples using FORALL and BULK COLLECT
  • Using FORALL with SQL%BULK_ROWCOUNT
  • Using BULK COLLECT INTO with queries
  • Using BULK COLLECT INTO with cursors
• Examples using EXECUTE IMMEDIATE
• Examples using RETURNING INTO
  • Using the RETURNING INTO clause with a record
  • Using BULK COLLECT INTO with the RETURNING INTO clause
• Examples using the AUTHID clause

6 Examples Using Standalone Subprograms

• Examples using input and output parameters and bind variables
• Example querying a system view
• Example creating and invoking a standalone function

7 PL/SQL Installation and Environment

• Confirming that PL/SQL is Installed and Enabled in TimesTen
  • PL/SQL installation and the ttmodinstall utility
  • Understanding the PLSQL connection attribute
  • Checking that PL/SQL is enabled in a TimesTen database
• PL/SQL connection attributes
• The ttSrcScan utility
• Displaying supplied packages

8 Access Control for PL/SQL Programs

• Access control for PL/SQL operations
  • Required privileges for PL/SQL statements and operations
  • Granting and revoking privileges
  • Invalidated objects
• Access control for SQL operations
• Definer's rights and invoker's rights
• Additional access control considerations
  • Access control for connections and connection attributes
  • Access control for system views and supplied packages
  • Access control for built-in procedures relating to PL/SQL

9 TimesTen Supplied PL/SQL Packages

• DBMS_LOCK
• DBMS_OUTPUT
• DBMS_PREPROCESSOR
• DBMS_RANDOM
• DBMS_SQL
• DBMS_UTILITY
• TT_DB_VERSION
• UTL_FILE
• UTL_IDENT
• UTL_RAW
• UTL_RECOMP

10 TimesTen PL/SQL Support: Reference Summary

• TimesTen PL/SQL language element support
• Additional support considerations and differences

Index