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.