Table Of ContentIntroduction
Copyright ©2008, Oracle. All rights reserved.
Course Objectives
After completing this course, you should be able to do the
following:
• Design PL/SQL packages and program units that execute
efficiently
• Write code to interface with external applications and the
operating system
• Create PL/SQL applications that use collections
• Write and tune PL/SQL code effectively to maximize
performance
• Implement a virtual private database with fine-grained
access control
• Write code to interface with large objects and use SecureFile
LOBs
• Perform code analysis to find program ambiguities, test,
trace, and profile PL/SQL code
1 -2 Copyright ©2008, Oracle. All rights reserved.
Course Objectives
In this course, you learn how to use the advanced features of PL/SQL in order to design and tune
PL/SQL to interface with the database and other applications in the most efficient manner. Using the
advanced features of program design, packages, cursors, extendedinterface methods, and collections,
you learn how to write powerful PL/SQL programs. Programming efficiency, use of external C and
Java routines, and fine-grained access are covered in this course.
Oracle Database 11g: Advanced PL/SQL 1 - 2
Oracle Complete Solution
Clients
Any Any Any
browser mail client FTP client
t Internet applications D
n e
e v SQL
m e
e lo
g Business logic Presentation and p
a m
n and data business logic PL/SQL
a e
m n
t
m to
e Databases Application o Other
t l
s servers s
y
S
Java
Network services
1 -3 Copyright ©2008, Oracle. All rights reserved.
Oracle Complete Solution
The Oracle Internet Platform is built on three core components:
• Browser-based clients to process presentation
• Application servers to execute business logic and serve presentation logic to browser-based
clients
• Databases to execute database-intensive business logic and serve data
Oracle offers a wide variety of the most advanced graphical user interface (GUI)–driven
development tools to build business applications, as well as a large suite of software applications for
many areas of business and industry. Stored procedures, functions, and packages can be written by
using SQL, PL/SQL, Java, C, and Net languages. This course concentrates on the advanced features
of PL/SQL.
Oracle Database 11g: Advanced PL/SQL 1 - 3
Lesson Agenda
• Previewing the course agenda
• Describing the development environments
• Using SQL Developer
• Using SQL*Plus
• Identifying the tables, data, and tools used in this course
1 -4 Copyright ©2008, Oracle. All rights reserved.
Oracle Database 11g: Advanced PL/SQL 1 - 4
Course Agenda
• Day 1
– Lesson 1: Introduction
– Lesson 2: PL/SQL Review
– Lesson 3: Designing PL/SQL Code
– Lesson 4: Working with Collections
• Day 2
– Lesson 4: Working with Collections
– Lesson 5: Using Advanced Interface Methods
– Lesson 6: Implementing Fine-Grained Access Control for VPD
– Lesson 7: Manipulating Large Objects
– Lesson 8: Administering SecureFile LOBs
– Lesson 9: Performance and Tuning
1 -5 Copyright ©2008, Oracle. All rights reserved.
Agenda
In this three-day course, you start with a review of PL/SQL concepts before progressing into the new
and advanced topics. By the end of day one, you should have covered design considerations for your
program units, and how to use collections effectively.
On day two, you learn how to use advanced interface methods to call C and Java code from your
PL/SQL programs, how to implement and test fine-grained access control for virtual private
databases, how to manipulate large objects programmatically through PL/SQL, how to administer the
features of the new SecureFile LOB format of Database 11g, andhow to tune PL/SQL code and deal
with memory issues.
Oracle Database 11g: Advanced PL/SQL 1 - 5
Course Agenda
• Day 3
– Lesson 10: Improving Performance with Caching
– Lesson 11: Analyzing PL/SQL Code
– Lesson 12: Profiling and Tracing PL/SQL Code
– Lesson 13: Safeguarding Your Code Against SQL Injection
Attacks
1 -6 Copyright ©2008, Oracle. All rights reserved.
Agenda (continued)
On day three, you learn how to improve performance by using Oracle database 11gcaching
techniques, how to write PL/SQL routines that analyze PL/SQL applications, how to profile and trace
PL/SQL code, and how to protect your code from SQL injection security attacks.
Oracle Database 11g: Advanced PL/SQL 1 - 6
Lesson Agenda
• Previewing the course agenda
• Describing the development environments
• Using SQL Developer
• Using SQL*Plus
• Identifying the tables, data, and tools used in this course
1 -7 Copyright ©2008, Oracle. All rights reserved.
Oracle Database 11g: Advanced PL/SQL 1 - 7
Development Environments: Overview
• Introduction to SQL Developer
• SQL*Plus
SQL Developer
1 -8 Copyright ©2008, Oracle. All rights reserved.
PL/SQL Development Environments
Oracle provides several tools that can be used to write PL/SQL code. Some of the development tools
that are available for use in this course are:
• Oracle SQL Developer: A graphical tool
• Oracle SQL*Plus:A command-line application
Note: The code and screen examples presented in the course notes were generated from the output in
the SQL Developer environment.
Oracle Database 11g: Advanced PL/SQL 1 - 8
Lesson Agenda
• Previewing the course agenda
• Describing the development environments
• Using SQL Developer
• Using SQL*Plus
• Identifying the tables, data, and tools used in this course
1 -9 Copyright ©2008, Oracle. All rights reserved.
Oracle Database 11g: Advanced PL/SQL 1 - 9
What Is Oracle SQL Developer?
• Oracle SQL Developer is a free graphical tool that enhances
productivity and simplifies database development tasks.
• You can connect to any target Oracle database schema by
using the standard Oracle database authentication.
• You can use either SQL Developer or SQL*Plus in this
course.
SQL Developer
1 -10 Copyright ©2008, Oracle. All rights reserved.
What Is Oracle SQL Developer?
Oracle SQL Developer is a free graphical tool designed to improve your productivity and simplify
the development of everyday database tasks. With just a few clicks, you can easily create and debug
stored procedures, test SQL statements, and view optimizer plans.
SQL Developer, the visual tool for database development, simplifies the following tasks:
• Browsing and managing database objects
• Executing SQL statements and scripts
• Editing and debugging PL/SQL statements
• Creating reports
You can connect to any target Oracle database schema by using the standard Oracle database
authentication. When connected, you can perform operations on the objects in the database.
Oracle Database 11g: Advanced PL/SQL 1 - 10
