Oracle PL/SQL Advanced

3 Day Course
Hands On
Code OAP

Book Now - 2 Delivery Methods Available:

Scheduled Online Onsite

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.

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
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. If you are unsure please ask a training advisor

  • Oracle Database 10g: Program with PL/SQL

    The Oracle Database 10g: Program with PL/SQL training course introduces PL/SQL and provides an understanding of the benefits of this programming language

    course code OC10GPLSQL 4 Classroom dates 2 locations
  • PL/SQL

    A 2-day Training Course on PL/SQL; Designed for those who need to write program scripts using Oracle's Procedure Language; corresponds to exam 1Z0-001

    course code OPI 34 Classroom dates 7 locations
CodeLocationDurationPrice Oct Nov Dec Jan Feb Mar
OAP 3 Days $1,825
Ask a Question

or call:0207 620 0033

Course PDF

Print

Share this Course

+1
Share

Recommend this Course