12c: Advanced PL/SQL

Course Fees: $3219.00 excl. GST
Printed Manual: $95.00 excl. GST
Course Duration: 3 days
Course Manual

Sorry, no course dates found

This Oracle Database 12c: Advanced PL/SQL training teaches you how to use the advanced features of PL/SQL to design and tune PL/SQL to interface with the database and other applications. Expert Oracle University instructors will help you explore advanced features of program design, packages, cursors, extended interface methods and collections.

Learn To:

Write powerful PL/SQL programs.
Explore programming efficiency.
Use external C and Java routines.
Apply PL/SQL designing best practices.
Create PL/SQL applications that use collections.
Implement a virtual private database with fine-grained access control. Write code to interface with external C and Java applications.

Write code to interface with large objects and use SecureFile LOBs. Write and tune PL/SQL code effectively to maximize performance.

Benefits to You

Discover how to write PL/SQL routines that analyze the PL/SQL applications and caching techniques that can improve performance. By investing in this course, you'll be introduced to the Virtual Private Database(VPD) to implement security policies and explore techniques and tools to strengthen your applications against SQL injection attacks. Expand programming resources by creating PL/SQL programs that interface with C and Java code. 

Application Developers
PL/SQL Developer
Database Administrators
Experience with SQL and PL/SQL required. Develop PL/SQL Program Unit; Program with PL/SQL
  • 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 


Course Objectives
Course Agenda
Describe the development environments
Identify the tables, data, and tools used in this course

PL/SQL Programming Concepts: Review

Identify PL/SQL block structure Packages, procedures and functions Cursors
Handle exceptions


Designing PL/SQL Code

Describe the predefined data types
Create subtypes based on existing types for an application List the different guidelines for cursor design
Describe cursor variables
White List

Overview of Collections

Overview of collections
Use Associative arrays
Navigate using associative methods Use Nested tables
Use Varrays
Compare nested tables and varrays

Using Collections

Write PL/SQL programs that use collections Use Collections effectively

Enhancements to PL/SQL Type Binds

Manipulating Large Objects

Working with LOBs
Overview of SecureFile LOBs

Using Advanced Interface Methods

Calling External Procedures from PL/SQL
Benefits of External Procedures
Understand how an external routine is called from PL/SQL C advanced interface methods
Java advanced interface methods

Performance and Tuning

Understand and influence the compiler Tune PL/SQL code
Enable intra unit inlining
Identify and tune memory issues Recognize network issues

Improving Performance with Caching

Describe result caching
Use SQL query result cache
Use PL/SQL function cache
Review PL/SQL function cache considerations

Analyzing PL/SQL Code

Finding Coding Information PL/Scope Concepts DBMS_METADATA Package PL/SQL Enhancements

Profiling and Tracing PL/SQL Code

Tracing PL/SQL Execution Tracing PL/SQL: Steps

12:Implementing VPD with Fine-Grained Access Control

Understand how fine-grained access control works overall Describe the features of fine-grained access control Describe an application context
Create an application context

Set an application context
List the DBMS_RLS procedures
Implement a policy
Query the dictionary views holding information on fine-grained access

Safeguarding Your Code Against SQL Injection Attacks

SQL Injection Overview
Reducing the Attack Surface Filtering Input with DBMS_ASSERT