Oracle 11g New Features for Developers

 Click here

Course Overview

Students learn to practice the language usability enhancements and utilize the performance improvement features such as in lining and flashback, code trigger enhancements to achieve more control over the triggers. Enhancements to the dynamic SQL features are also discussed in this course.

This course allows students to expand their knowledge of the presented concepts through several demonstrations and hands-on exercises based on the latest SQL and PL/SQL enhancements.

Course Goals

• Use the SQL Developer interface
• Write SQL statements that include the new functions added to enhance regular expression support functionality
• Use the enhancements added to native dynamic SQL and to DBMS_SQL which enable more interoperability between the two methodologies
• Write compound triggers and use the enhancements made to triggers
• Practice the performance improvements
• Write SQL and PL/SQL calls to sequences that are simpler
• Use the new CONTINUE statement to control loops

Prerequisites

• Experience and knowledge of SQL and PL/SQL from prior Oracle releases

Who Should Attend?

• PL/SQL Developers
• System Analysts
• Application Developers

Course Materials

• 11g New Features SQL & Pl/SQL Notes
• 11g New Features SQL & Pl/SQL Reference Book

Duration

• Two (2) days

Course Outline

• SQL Enhancements
  • Use the regular expression support functions to find sub patterns and count the occurrences of patterns
  • Use the WAIT option for DDL statements
  • Use the LOCK TABLE new syntax that enables you to specify the maximum number of seconds the statement should wait to obtain a DML lock on the table
  • Create and query virtual columns
  • Use the ALTER TABLE statement to change tables to read-only status
• PL/SQL Enhancements
  • List the compiler changes and how the changes impact native compilation
  • The new SIMPLE_INTEGER , SIMPLE_FLOAT, and SIMPLE_DOUBLE data types
  • Describe the process of in-lining functions and procedures
  • Use flashback to store and track transactional changes to a record
  • Use inline sequence calls to NEXTVAL and CURRVAL without using SQL
  • Use the CONTINUE statement to control iteration
  • Use named and mixed notation calls to functions from a SQL statement
• DBMS_SQL Enhancements
  • Write PL/SQL code that uses dynamic SQL and allows for SQL statements larger than 32kb
  • Use the DBMS_SQL.PARSE() function that is overloaded for CLOBs
  • Convert a REF CURSOR to a DBMS_SQL cursor and vice versa to support interoperability
  • Program using the enhancements to DBMS_SQL that include supporting the full range of data types (including collections and object types)
  • Create user-defined collection types and bulk bind them using DBMS_SQL.
• Trigger Enhancements
  • Create compound triggers
  • Create disabled triggers
  • Use the ENABLE clause with a trigger
  • Control trigger order with the FOLLOWS and PRECEDES clauses
• Data Warehousing Usability Enhancements
  • Identify the benefits of pivoting and un-pivoting operations
  • Write cross-tab queries to pivot (rotate) column values into new columns and to un-pivot (rotate) columns into column values
  • Pivot and un-pivot with multiple columns and multiple aggregates
  • Use wildcards and aliases with pivoting operations