Oracle PL SQL Programming

Duration: 
5 days
Codes: 
493,PL,SQL

Overview

Increase the productivity, performance, scalability, portability, and security of your Oracle database with this comprehensive PL/SQL programming course. With this training, you learn to write PL/SQL programs, build stored procedures, design and execute modular applications, and increase the efficiency of data movement — essential skills for any Oracle developer.

Skills Gained

You Will Learn How To You Will Learn How To

  • Develop efficient PL/SQL programs to access Oracle databases
  • Create stored procedures and functions for reuse and maintenance
  • Design modular applications using packages
  • Manage data retrieval for front-end applications
  • Invoke native dynamic SQL to develop high-level abstract code

Prerequisites

Important Course Information Important Course Information

  • Requirements
    • Knowledge at the level of:
      • Course 2226, Oracle Database 12c Introduction, or
      • Course 925, SQL Programming Language Introduction
  • Software
    • This course is currently running on Oracle 12c

Requirements Requirements

  • Knowledge at the level of:
    • Course 2226, Oracle Database 12c Introduction, or
    • Course 925, SQL Programming Language Introduction

Knowledge at the level of:

  • Course 2226, Oracle Database 12c Introduction, or
  • Course 925, SQL Programming Language Introduction

Course 2226 Course 2226 , Oracle Database 12c Introduction, or

Course 925 Course 925 , SQL Programming Language Introduction

Software Software

  • This course is currently running on Oracle 12c

Course Outline

  • Introduction and Overview

PL/SQL fundamentals

  • Flow control constructs

Oracle 11 g and 12 c PL/SQL features

  • PL/Scope in Oracle 11 g
  • Returning implicit cursor results from stored procedures in Oracle 12 c
Oracle 11 g and 12 c PL/SQL features

Oracle 11

g

g and 12

c

c PL/SQL features

PL/Scope in Oracle 11

Returning implicit cursor results from stored procedures in Oracle 12

  • 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
  • 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

  • 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
  • Building and applying a test bed
  • Leveraging the debugging facilities in SQL Developer
  • Achieving Maximum Reusability

Writing stored procedures and functions

  • Calling and invoking server-side logic
  • Passing input and output parameters
  • Implementing an autonomous transaction

Coding user-written functions

  • Calling PL/SQL functions from SQL
  • Building table-valued functions

Developing safe triggers

  • Employing :OLD and :NEW variables
  • Avoiding unreliable trigger constructs
  • Exploiting schema and database triggers
  • Exploiting Complex Datatypes

Collection types

  • PL/SQL tables, nested tables, VARRAYs
  • Stepping through dense and nonconsecutive collections

Bulk binding for high performance

  • Moving data into and out of PL/SQL blocks
  • BULK COLLECT INTO and 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
  • Autogenerating standard code
  • Package Tips and Techniques
  • Package structure: SPEC and BODY
  • Eliminating dependency problems
  • Overloading for polymorphic effects
  • Evaluating application frameworks

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

Upcoming Dates

  • GREEN This class is Guaranteed To Run.
  • SPVC - Self-Paced Virtual Class.
  • Click a Date to Enroll.
Course Location Days Cost Date
London London5 2445 £2445 2019-10-14
Somerset Bath3 2445 £2445 2019-11-11