Toutes nos formations sont désormais disponibles en "Live Virtual classes". Contactez-nous pour plus d’informations : formation@oxiane.luToutes nos formations sont désormais disponibles en "Live Virtual classes". Contactez-nous pour plus d’informations : formation@oxiane.lu

Oracle Database 11g: SQL & PL/SQL New Features

Accueil » Formations » Data » Oracle Database 11g: SQL & PL/SQL New Features

After completing this course, students should be able to use all new SQL & PL/SQL features.

1310 € HT 2 jours O11GNFSQLPLSQL

Programme

Implementing the Language Functionality Enhancements

  • Use the new regular expression support functions to find sub patterns and count the occurrences of patterns
  • Track dependencies at the element level
  • Find and fix exception handlers that do not pass the exception upward to the calling program or environment
  • Dispatch an over ride-able object type method using the ANSI SQL 2003 standard for super-types
  • 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

Executing Dynamic SQL in PL/SQL with the 11g 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

Utilizing the Performance Improvement Enhancements

  • List the compiler changes and how the changes impact native compilation
  • Use the new SIMPLE_INTEGER data type
  • Describe the process of in-lining
  • Use flashback to store and track all transactional changes to a record

Practicing the Language Usability Enhancements

  • Implement the sequence calls to NEXTVAL and CURRVAL without using a SQL statement to retrieve the values
  • Use the new CONTINUE statement to control the next loop iteration or to leave a loop
  • Use both named and mixed notation calls to functions from a SQL statement
  • Use the ALTER TABLE statement to change tables to read-only status

Coding the Trigger Enhancements

  • Create compound triggers
  • Create disabled triggers
  • Use the ENABLE clause with a trigger
  • Control trigger order with the FOLLOWS and PRECEDES clauses

Administering Secure-File LOBs

  • Describe Secure-File LOB features
  • Migrate Basic-File LOBs to the Secure-File LOB format
  • Analyze the performance of LOBs
  • Enable Secure-File LOB de-duplication, compression, and encryption

Using the 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

Using the SQL Developer Enhancements

  • Navigate through the object navigator and view the enhancements
  • Compile and debug PL/SQL
  • Browse through the available search engines
  • Change preferences
  • Create reports