Oracle PL/SQL Programming Class

2KO presents Oracle courses in Cape Town by means of full time instructor-led training; as well as internet-accessible online training, which are aimed at International IT Certification exams. 2KO International delivers consulting services, in the IT and Business space, as well as being a training provider for IT certification and user level courses. Training can be any blend of bespoke, classroom and online learning.

 

Overview:

2KO International proudly offers Oracle PL/SQL training in Cape Town, South Africa. This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. Students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Students learn to create anonymous PL/SQL blocks as well as stored procedures and functions. Students learn to develop, execute, and manage PL\SQL stored program units such as procedures, functions, packages, and database triggers. Students also learn to manage PL/SQL subprograms, triggers, declaring identifiers and trapping exceptions. Students are introduced to the utilization of some of the Oracle-supplied packages.

 

Target Audience:

Programmers and others working with PL/SQL.

 

Pre-requisites:

Database administrators, developers with data management responsibilities and others involved in the management or deployment of Oracle 11g databases. Oracle Database 11g basics, or equivalent experience will be of advantage.

 

Outline:
Introduction and Overview
 

PL/SQL fundamentals

Declaring variables

Anchoring variables to database definitions

Flow control constructs

 

Oracle10g and 11g PL/SQL features

PL/Scope in Oracle 11g

CASE statement process flow

Referencing PL/SQL records in DML

Improving performance with native compilation

Handling regular expressions with Oracle 10g functions

Compound triggers in Oracle 11g

Multiset operators for collections

 

Data Manipulation Techniques
Maintaining data with DML statements

Employing the RETURNING INTO clause

Solving the fetch-across-commit problem

 

Managing data retrieval with cursors

Implications of explicit and implicit cursors

Cursor attributes

Simplifying cursor processing with cursor FOR LOOPs

Embedding cursor expressions in SELECT statements

 

Cursor variables

Strong vs. weak cursor variables

Passing cursor variables to other programs

Defining REF CURSORS in packages

 

Developing Well-Structured and Error-Free Code
Error handling using EXCEPTIONS

Predefined and user EXCEPTIONs

Propagation and scope

"Retrying" problem transactions with EXCEPTION processing

 

Debugging PL/SQL blocks

Simplifying testing and debugging with conditional compilation

Interpreting compiler messages

Applying structured testing techniques

 

Achieving Maximum Reusability
Writing stored procedures and functions

Calling and invoking server-side logic

Passing input and output parameters

Implementing an autonomous transaction

Definer rights vs. invoker rights

 

Coding user-written functions

Calling PL/SQL functions from SQL

Building table-valued functions

 

Developing safe triggers

Employing :OLD and :NEW bind variables in row-level triggers

Implementing complex business rules

Avoiding unreliable trigger constructs

Exploiting schema and database triggers

 

Exploiting Complex Datatypes
Collection types

PL/SQL tables, nested tables, VARRAYs

Stepping through dense and non-consecutive collections

 

Bulk binding for high performance

Moving data into and out of PL/SQL blocks

BULK COLLECT INTO

FORALL

BULK cursor attributes

BULK EXCEPTION handling

 

Invoking Native Dynamic SQL
Finessing the compiler

The EXECUTE IMMEDIATE statement

The RETURNING INTO clause

 

Types of dynamic SQL

Building SQL statements during runtime

Auto-generating standard code

 

Package Tips and Techniques

Package structure: SPEC and BODY

Eliminating dependency problems

Overloading for polymorphic effects

Evaluating application frameworks

Bodiless packages for all application definitions

Declaring and using persistent global variables