Oracle 12c New Features for Developers

Introduction to Oracle 12c

• Overview of Oracle 12c Database changes
  • In-memory Database
  • Multi-tenant architecture (container/pluggable databases)
  • Improved optimization
  • Adaptive Execution Plans
  • Expanded maximum size for VARCHAR2, NVARCHAR2, and RAW columns
    • How to use 32K fields
    • Setup requirements
    • What is the Oracle Database Cloud?

• SQL Developer 4.0 support for Oracle 12c

Exercise: Explore Available Sample Tables

SELECT improvements

• Top-N queries (fetch by row or by percentage)
• Pagination (offset)
• Performance implications of Top-N and Pagination

Exercise: Using Top-N and Pagination

Pattern Matching using Regular Expressions

• MATCH_RECOGNIZE
• Matching syntax: PARTITION, ORDER BY, MEASURES, AFTER, PATTERN, DEFINE

Exercise: Exploring the Power of Patterns

Outer Join Improvements

• Expansion of traditional Oracle (+) operator
• New capabilities provided by CROSS_APPLY and OUTER_APPLY
• LATERAL inline views

Exercise: Improving Join Capabilities

Table Definition Improvements

• Identity Columns: GENERATED AS IDENTITY
• Column Default using Sequence

Exercise: Identity Columns and Default Sequences

Enhanced Default Columns

Computer Exercise: Expanding Column Default Capabilities

Invisible Columns

• Why would you use Invisible Columns?
• Maintenance implications of Invisible Columns
• Multiple Index capability

Exercise: Using Invisible Columns

Session-Level Sequences

• Uses of Session-Level Sequences

Exercise: Sequences at the Session Level

Temporal Validity

• PERIOD clause in table definition
• AS OF PERIOD in SELECT
• Temporal Validity and Flashback Queries

Exercise: Working wth Data Periods

PL/SQL in SELECT WITH Clause

• Understanding how PL/SQL and SQL work together
• PRAGMA UDF
• Performance Implications of PL/SQL in WITH and PRAGMA UDF

Exercise: Improving PL/SQL

Review of Oracle 11g PL/SQL Result Cache

• Invoker’s Rights Improvement in Oracle 12c PL/SQL Result Cache
• Understanding how to use PL/SQL Result Cache

Exercise: Working with PL/SQL Result Cache

JDBC support for PL/SQL types and Implicit Result Sets

• PL/SQL “white list”
• UTL_CALL_STACK

DML Enhancements

• Online DML locking improved
• Truncate CASCADE
• DDL Logging
• View Expansion

Exercise: How DML Improvements Work

EBR Improvements

• Public Synonyms
• Materialized Views and Types
• Virtual Columns

Demonstration: Working with EBR