Description

This course aims to present the contributions of this new version and their implementation. It will allow each participant to take full advantage of Oracle 12c/11g.

Who is this training for ?

For whom ?

Project managers, design engineers, developers, consultants, having to develop in optimal conditions with Oracle 12c/11g.

Prerequisites

Training objectives

Training program

• What's new in SQL 11g
  • Regular expressions for extraction and verification.
  • Using virtual columns for indexing.
  • Managing external tables: compression and preprocessing.
  • Subpartitioning.
  • Functions for XML.
  • Transposition functions (PIVOT and UNPIVOT).
  • Expression of common tables with the clause WITH, application to recursion.
  • Exercise: Queries with regular expressions and graphs.
  • Setting up virtual column indexes.
  • Creating a compressed external table.
  • Manipulating XML content.
• What's new in SQL 12c
  • Cascading deletions with TRUNCATE.
  • Row limiting with FETCH.
  • Using DEFAULT for sequences and NULLs.
  • Creation of identity columns.
  • Session sequences.
  • Constructing views with LATERAL.
  • Creating joins with CROSS APPLY and OUTER APPLY.
  • Pattern recognition with MATCH_RECOGNIZE.
  • SQL functions for JSON content.
  • Temporal validity with PERIOD.
  • Exercise: Using auto -increment.
  • Template searches.
  • Manipulating JSON content.
• What's new in PL/SQL 11g and 12c
  • Sequences, loops and duplicates.
  • Compound triggers.
  • Result Cache functions.
  • Object versions with Edition-Based.
  • Assigning rights with ACCESSIBLE BY.
  • Role management with Code Based Access Control.
  • Binding variables for dynamic SQL.
  • Implicit results.
  • Exercise: Caching function results.
  • Defining structured variables to construct SQL statements.
  • Assigning roles to a program unit.
• New features in 11g and 12c packages
  • BMS_SQL for dynamic SQL and for returning cursors.
  • UTL_CALL_STACK for obtaining detailed information about subroutines.
  • DBMS_FLASHBACK for auditing and recovering transactions ( transaction backout).
  • DBMS_STATS for new histograms and extended statistics.
  • (CREATE_EXTENDED_STATS, SEED_COL_USAGE and REPORT_COL_USAGE).
  • DBMS_SQLTUNE for automating SQL profiles.
  • DBMS_SPM to manage execution plans with SQL Plan Management.
  • Exercise: Subroutine call traces.
  • Collecting extended statistics.
  • Analysis of query tuning.