• +27 (0)21 418 4874

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 computer courses from beginner level to advanced, and is also a leading supplier of of online IT training courses.

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.

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.

Course 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 cursor attributes

Invoking Native Dynamic SQL
Finessing the compiler

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