Contents

Title and Copyright Information

Preface

• Audience
• Related documents
• Conventions
• Documentation Accessibility

What's New

• New features in Release 11.2.2.0.0

1 Introduction to PL/SQL in the TimesTen Database

• Features of PL/SQL in TimesTen
• TimesTen PL/SQL components and operations
  • Application interaction with TimesTen and PL/SQL
  • PL/SQL in TimesTen versus PL/SQL in Oracle Database
    • SQL statements in PL/SQL blocks
    • Execution of PL/SQL from SQL
• Audiences for this document
  • Developers experienced with Oracle Database and Oracle Database 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
• SQL function calls from PL/SQL
• 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 IMDB Cache
  • Large objects (LOBs)
    • About LOBs
    • LOB locators
    • Temporary LOBs
    • Differences between TimesTen LOBs and Oracle Database LOBs
    • Using LOBs
    • PL/SQL package support for LOBs
    • Passthrough LOBs
• Use of cursors in PL/SQL programs
• PL/SQL procedures and functions
  • Creating and using procedures and functions
  • Using synonyms for procedures and functions
• PL/SQL packages
  • Package concepts
  • Creating and using packages
  • Using synonyms for 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
    • LOB data types
  • PL/SQL composite data types
    • Using collections in PL/SQL
    • Using records in PL/SQL
    • Using associative arrays from applications
  • 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
  • Zero-length strings in TimesTen type mode

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
• Showing errors in ttIsql
• 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 Database SQL parser)
  • Use of TimesTen expressions at runtime

5 Examples Using TimesTen SQL in PL/SQL

• Examples using the SELECT...INTO statement in PL/SQL
  • Using SELECT... INTO to return sum of salaries
  • Using SELECT...INTO to query another user's table
• Example using the INSERT statement
• Examples using input and output parameters and bind variables
  • Using IN and OUT parameters
  • Using IN OUT parameters
  • Using associative arrays
• 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
  • Using SAVE EXCEPTIONS with BULK COLLECT
• Examples using EXECUTE IMMEDIATE
  • Using EXECUTE IMMEDIATE to create a table
  • Using EXECUTE IMMEDIATE with a single row query
  • Using EXECUTE IMMEDIATE to alter a connection attribute
  • Using EXECUTE IMMEDIATE to call a TimesTen built-in procedure
  • Using EXECUTE IMMEDIATE with TimesTen-specific syntax
• 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
  • Script for AUTHID examples
  • Using AUTHID CURRENT_USER
  • Using AUTHID DEFINER
• Example querying a system view

6 PL/SQL Installation and Environment

• Confirming that PL/SQL is installed and enabled in TimesTen
  • PL/SQL installation and the ttmodinstall utility
  • Checking that PL/SQL is enabled in a TimesTen database
• PL/SQL connection attributes
• The ttSrcScan utility

7 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

8 TimesTen Supplied PL/SQL Packages

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

9 TimesTen PL/SQL Support: Reference Summary

Index