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

start > courses and registration > training feature sheet

code.

11g-SQL-PLSQL

length.

2 days

type.

Instructor-Led

partner.

Oracle

price.

$1,200

In this course students learn new features of SQL and PL/SQL in Oracle Database 11g. This course introduces you to the SQL Developer tool, using which you learn various enhancements in language functionality and triggers.

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. Students also learn about the reengineered LOB data type that offers advanced, next-generation functionality such as intelligent compression and transparent encryption. Additionally, students explore the data warehousing usability enhancements with the new SQL PIVOT and UNPIVOT operators.
Learn To:

	Practice the performance improvements
	Monitor dependency tracking and change notification
	Write compound triggers and use the enhancements made to triggers
	Use Secure File LOBS
	Use the SQL Developer interface
	Write SQL statements that include the new functions added to enhance regular expression support functionality

There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links below.

who can benefit

	Data Warehouse Administrator
	Developer
	System Analysts
	Application Developers
	PL/SQL Developer

prerequisites

Required Prerequisites:

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

skills gained

	Explore the PIVOT and UNPIVOT Data warehousing operators
	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
	Use element level dependency tracking
	Write compound triggers and use the enhancements made to triggers
	Practice the performance improvements
	Use Secure File LOBS
	Write SQL and PL/SQL calls to sequences that are simpler
	Use the new CONTINUE statement to control loops

suggested prerequisites:

	Hands on experience using PL/SQL
	Oracle Database 10g: Advanced PL/SQL

suggested next courses:

Oracle Database 11g: Administration Workshop I

related courses, exams and materials

	before related training and products

Oracle Database 10g: Advanced PL/SQL

course content details

	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