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
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
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
Suggested Prerequisites:
Hands on experience using PL/SQL
Oracle Database 10g: Advanced PL/SQL
Suggested Next Courses:
Hands on experience using PL/SQL
Oracle Database 10g: Advanced PL/SQL
Code:
11g-SQL-PLSQL
Length:
2 days
Type:
Instructor-Led
Certified By:
Oracle
Tuition:
$1,200
This course is taught by Certified Oracle instructors. There is a difference. Learn More
This course is in the following categories. Click the categories to find similar courses and topics of interest.
There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links below. We can also contact you when the course is scheduled in your area.
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
When you take a certified course with ExitCertified, you are learning from
the creators of the products you use. Our commitment to your IT
community, along with our authorization to deliver certified courses,
ensures you receive a premium training experience.
