By using our website you agree to our use of cookies.

Oracle PL/SQL Advanced

3 Day Course
Hands On
Code OAP

Book Now - 2 Delivery Methods Available:

Classroom Virtual Classroom Private Group - Virtual Self-Paced Online

Overview

This course, designed for Oracle database administrators and software development personnel who need to gain practical experience of the advanced features of PL/SQL such as program design, packages, cursors, large objects and collections.

It also introduces some of the Oracle-supplied packages. This course is suitable for users of Oracle Database 10g and Oracle Database 11g.

By the end of this course delegates will be able to effectively tune PL/SQL code; create subtypes; create and use collections; execute external C programs and Java programs from PL/SQL; use fine-grained access control; use LOB data types and the DBMS_LOB package; use SecureFile LOBs; effectively design cursors; improve memory usage with PL/SQL result caching and SQL result sets and apply coding standards to avoid SQL injection attacks.

View detailed Course Modules

Training Partners

We work with the following best of breed training partners using our bulk buying power to bring you a wider range of dates, locations and prices.

Modules

Collapse all

Introduction (4 topics)

  • Course objectives
  • The Oracle complete solution
  • Course agenda
  • Tables and data used for this course

PL/SQL Programming Concepts Review (8 topics)

  • Identify PL/SQL block structure
  • Create procedures
  • Create functions
  • Create packages
  • Use cursors
  • Handle exceptions
  • Understand dependencies
  • Identify the Oracle supplied packages

Design Considerations (6 topics)

  • List the different guidelines for cursor design
  • Describe cursor variables
  • Pass cursor variables as program parameters
  • Compare cursor variables to static cursors
  • Describe the predefined data types
  • Create subtypes based on existing types for an application

Collections (8 topics)

  • Describe and use nested tables
  • Describe and use varrays
  • Describe and use associative arrays
  • Describe and use string indexed collections
  • Describe and use nested collections
  • Write PL/SQL programs that use collections
  • Describe the common collection exceptions and how to code for them
  • Compare associative arrays to collections

Advanced Interface Methods (7 topics)

  • Execute external C routines from PL/SQL
  • Understand the benefits of external routines
  • Publish the external C routine in the PL/SQL code
  • Execute a PL/SQL routine that calls the external C routine
  • Execute Java routines from PL/SQL
  • Publish the Java class method by creating the PL/SQL subprogram unit specification that references the Java class method
  • Execute the PL/SQL subprogram that invokes the Java class method

PL/SQL Server Pages (6 topics)

  • Define embedding PL/SQL code in Web pages(PL/SQL Server Pages)
  • Describe the format of a PL/SQL Server Page
  • Write the code and content for the PL/SQL Server Page
  • Load the PL/SQL Server Page into the database as a stored procedure
  • Run a PL/SQL Server Page via a URL
  • Debug PL/SQL Server Page problems

Fine Grained Access Control (6 topics)

  • Understand how fine-grained access control works overall
  • Describe the features of fine-grained access control
  • Describe an application context
  • Set up a logon trigger
  • View the results
  • Query the dictionary views holding information on fine-grained access

Performance and Tuning (7 topics)

  • Tune PL/SQL code
  • Write smaller executable sections of code
  • Compare SQL to PL/SQL on performance
  • Understand how bulk binds can improve performance
  • Handle exceptions with the FORALL syntax
  • Identify data type and constraint issues
  • Recognize network issues

Analyzing PL/SQL Code (7 topics)

  • Use the supplied packages and dictionary views to find coding information
  • dbms_describe supplied package
  • Use supplied packages to find error information
  • Trace PL/SQL programs using the dbms_trace supplied packageq
  • Read and interpret the trace information
  • Profile PL/SQL programs using the dbms_profiler supplied package
  • Read and interpret the profiler information

Prerequisites

Practical experience of Oracle SQL and the PL/SQL programming language is required. This can be gained by attendance on the pre-requisite courses. Familiarity with the Oracle Database is also required.

Additional Learning

The courses below may help you meet the knowledge level required to take this course.

Scheduled Dates

Please select from the dates below to make an enquiry or booking.

Pricing

Different pricing structures are available including special offers. These include early bird, late availability, multi-place, corporate volume and self-funding rates. Please arrange a discussion with a training advisor to discover your most cost effective option.

Code Location Duration Price Jun Jul Aug Sep Oct Nov
OAP
Virtual Classroom (London)
3 Days $1,620
Next 6 Months
Oracle Development Training Courses

Course PDF

Print

Share this Course

Share

Recommend this Course

Sections