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