Oracle Database 11g delivers economies of scale on easily managed low-cost grids, making it easier to:

  • Reduce cost of downtime with maximum availability architecture
  • Change IT systems faster using Real Application Testing
  • Partition and compress data to run queries faster using less disks
  • Securely protect and audit data, and enable total recall of data
  • Make productive use of standby resources with Active Data Guard

This course addresses new features with Oracle Database 11g SQL and PL/SQL and is focused on:

  • Getting Started with SQL and PL/SQL New Features
  • Using Dynamic SQL and Implementing Performance Improvements
  • Using Trigger, Data Warehousing, and Pivoting Enhancements
  • Using the PL/SQL Debugger, Collections, and Data Warehousing

What You’ll Learn

  • Scope and capabilities of enhancements to SQL and PL/SQL, including tools such as SQL*Plus and SQL Developer, and using them to create reports and scripts, browse and manage database objects, find subpatterns, and track dependencies
  • Steps for improving SQL and PL/SQL flexibility, usability, and performance, using dynamic SQL, language enhancements, and a variety of performance enhancements
  • Steps for improving performance, managing scalabilty, enhancing control and processing speed, and reducing network load using enhanced features such as compound triggers, SecureFile LOBs, and PIVOT operations
  • Ways to utilize the PL/SQL Debugger to analyze your code, uses and benefits of collections, and the steps for employing data warehousing enhancements to improve query execution times

Who Needs to Attend

PL/SQL developers, developers, system analysts, data warehouse administrators, and application developers

Prerequisites

  • Experience and knowledge of SQL and PL/SQL from prior Oracle releases
  • Hands-on experience using PL/SQL

Follow-On Courses

There are no follow-ons for this course.

Course Outline

1. Oracle Database 11g: Getting Started with SQL and PL/SQL New Features

  • Using SQL*Plus commands to display the structure of a table and perform some editing and file management tasks
  • Using SQL Developer and SQL Worksheet to connect to a database and browse and export database objects, and use SQL*Plus to enter and execute SQL and PL/SQL statements
  • Using SQL Developer to create reports and migrate to an Oracle Database 11g database
  • Using Oracle Database 11g’s new SQL and PL/SQL language functionality enhancements
  • Scope and uses of Data Change Notification (DCN) and lock enhancements
  • Use SQL and PL/SQL language functionality enhancements to connect to a database and create a report, examine dependency at the element level, and modify an exception handler

2. Oracle Database 11g: Using Dynamic SQL and Implementing Performance Improvements

  • Using native dynamic SQL and the DBMS_SQL package to specify dynamic SQL statements; using CLOB data types and abstract data types
  • Using language enhancements to improve sequence usability, control loop iterations, employ named and mixed notation in calls to PL/SQL, and place a table in read-only mode
  • Improving the performance of SQL and PL/SQL with a new compiler, a new, faster data type, in-lining for faster performance, caching, and flashback enhancements
  • Examine and improve performance in Oracle Database 11g

3. Oracle Database 11g: Using Trigger, Data Warehousing, and Pivoting Enhancements

  • Creating and enabling a compound trigger and controlling its firing order using new trigger clauses
  • Implementing SecureFile LOBs
  • Performing PIVOT and UNPIVOT operations in various ways on the server side
  • Create reports with the PIVOT operator

4. Oracle Database 11g: Using the PL/SQL Debugger, Collections and Data Warehousing

  • Using the PL/SQL Debugger tool in SQL Developer
  • Debug a procedure using the PL/SQL Debugger
  • Making effective use of collections in PL/SQL and deciding which is the best collection to use in a given scenario
  • Using materialized views and query rewrite enhancem