Table Of Content

Introduction 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