PL/SQL Programming

This PL/SQL Programming Course will solidify your SQL and coding experience so you can become a PL/SQL Database Developer. Cover the International syllabus to prep for your OCA/OCP exam.

R12,995.00

Description

This PL/SQL Programming Course will solidify your SQL and coding experience so you can become a PL/SQL Database Developer. It covers the International syllabus to prep for your OCA/OCP exam.

Prerequisites

Intended Audience

    • This Oracle PL/SQL Programming Course will prepare you for the international Oracle Exam in Pl/SQL Programming
    • You know the basics of SQL Querying, but want to combine it with your Programming Skills to become a professional Oracle PL/SQL Developer

After this course you should

    • Use various features of PL/SQL.
    • Write anonymous blocks of code in PL/SQL.
    • Use PL/SQL conditional constructs and loops in program blocks.
    • Interface your PL/SQL code with the database.
    • Code stored procedures and functions in your program blocks.
    • How to handle Exceptions in PL/SQL code

Alignment

  • Oracle Database: Program with PL/SQL
  • Oracle Database: Advanced PL/SQL | 1Z0-148

Course Contents

(1) PL/SQL Concepts

  • 1.1 PL/SQL in Client/Server Architecture
  • 1.1.1 Use PL/SQL Anonymous Blocks
  • 1.1.2 Understand How PL/SQL Gets Executed
  • 1.2 PL/SQL in SQL*Plus
  • 1.2.1 Use Substitution Variables
  • 1.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement

(2) General Programming Language

  • 2.1 PL/SQL Programming Fundamentals
  • 2.1.1 Make Use of PL/SQL Language Components
  • 2.1.2 Make Use of PL/SQL Variables
  • 2.1.3 Handle PL/SQL Reserved Words
  • 2.1.4 Make Use of Identifiers in PL/SQL
  • 2.1.5 Make Use of Anchored Datatypes
  • 2.1.6 Declare and Initialize Variables
  • 2.1.7 Understand the Scope of a Block, Nested Blocks, and Labels

(3) SQL in PL/SQL

  • 3.1 Making Use of DML in PL/SQL
  • 3.1.1 Use the Select INTO Syntax for Variable Initialization
  • 3.1.2 Use DML in a PL/SQL Block
  • 3.1.3 Make Use of a Sequence in a PL/SQL Block
  • 3.2 Making Use of SAVEPOINT
  • 3.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT
  • in a PL/SQL Block

(4) Conditional Control: IF Statements

  • 4.1 IF Statements
  • 4.1.1 Use the IF-THEN Statement
  • 4.1.2 Use the IF-THEN-ELSE Statement
  • 4.2 ELSIF Statements
  • 4.2.1 Use the ELSIF Statement
  • 4.3 Nested IF Statements
  • 4.3.1 Use Nested IF Statements

(5) Conditional Control: CASE Statements

  • 5.1 CASE Statements
  • 5.1.1 Use the CASE Statement
  • 5.1.2 Use the Searched CASE Statement
  • 5.2 CASE Expressions
  • 5.2.1 Use the CASE Expression
  • 5.3 NULLIF and COALESCE Functions
  • 5.3.1 The NULLIF Function
  • 5.3.2 Use the COALESCE Function

(6) Iterative Control: Part I

  • 6.1 Simple Loops
  • 6.1.1 Use Simple Loops with EXIT Conditions
  • 6.1.2 Use Simple Loops with EXIT WHEN Conditions
  • 6.2 WHILE Loops
  • 6.2.1 Use WHILE Loops
  • 6.3 Numeric FOR Loops
  • 6.3.1 Use Numeric FOR Loops with the IN Option
  • 6.3.2 Use Numeric FOR Loops with the REVERSE Option

(7) Iterative Control: Part II

  • 7.1 The CONTINUE Statement
  • 7.1.1 Use the CONTINUE Statement
  • 7.1.2 Use the CONTINUE WHEN Condition
  • 7.2 Nested Loops
  • 7.2.1 Use Nested Loops

(8) Error Handling and Built-in Exceptions

  • 8.1 Handling Errors
  • 8.1.1 Understand the Importance of Error Handling
  • 8.2 Built-in Exceptions
  • 8.2.1 Use Built-in Exceptions

(9) Exceptions

  • 9.1 Exception Scope
  • 9.1.1 Understand the Scope of an Exception
  • 9.2 User-Defined Exceptions
  • 9.2.1 Use User-Defined Exceptions
  • 9.3 Exception Propagation
  • 9.3.1 Understand How Exceptions Propagate
  • 9.3.2 Reraise Exceptions

(10) Exceptions: Advanced Concepts

  • 10.1 RAISE_APPLICATION_ERROR
  • 10.1.1 Use RAISE_APPLICATION_ERROR
  • 10.2 EXCEPTION_INIT Pragma
  • 10.2.1 USE the EXCEPTION_INIT Pragma
  • 10.3 SQLCODE and SQLERRM
  • 10.3.1 Use SQLCODE and SQLERRM

(11) Introduction to Cursors

  • 11.1 Cursor Manipulation
  • 11.1.1 Make Use of Record Types
  • 11.1.2 Process an Explicit Cursor
  • 11.1.3 Make Use of Cursor Attributes
  • 11.1.4 Put It All Together
  • 11.2 Using Cursor FOR Loops and Nested Cursors
  • 11.2.1 Use a Cursor FOR Loop
  • 11.2.2 Process Nested Cursors

(12) Advancd Cursors

  • 12.1 Using Parameters with Cursors and Complex Nested Cursors
  • 12.1.1 Use Parameters in a Cursor
  • 12.1.2 Use Complex Nested Cursors
  • 12.2 FOR UPDATE and WHERE CURRENT Cursors
  • 12.2.1 For UPDATE and WHERE CURRENT Cursors

(13) Triggers

  • 13.1 What Triggers Are
  • 13.1.1 Understand What a Trigger Is
  • 13.1.2 Use BEFORE and AFTER Triggers
  • 13.2 Types of Triggers
  • 13.2.1 Use Row and Statement Triggers
  • 13.2.2 Use INSTEAD OF Triggers

(14) Compound Triggers

  • 14.1 Mutating Table Issues
  • 14.1.1 Understand Mutating Tables
  • 14.2 Compound Triggers
  • 14.2.1 Understand Compound Triggers

(15) Collections

  • 15.1 PL/SQL Tables
  • 15.1.1 Use Associative Arrays
  • 15.1.2 Use Nested Tables
  • 15.2 Varrays
  • 15.2.1 Use Varrays
  • 15.3 Multilevel Collections
  • 15.3.1 Use Multilevel Collections

(16) Records

  • 16.1 Record Types
  • 16.1.1 Use Table-Based and Cursor-Based Records
  • 16.1.2 Use User-Defined Records
  • 16.2 Nested Records
  • 16.2.1 Use Nested Records
  • 16.3 Collections of Records
  • 16.3.1 Use Collections of Records

(17) Native Dynamic SQL

  • 17.1 EXECUTE IMMEDIATE Statements
  • 17.1.1 Use the EXECUTE IMMEDIATE Statement
  • 17.2 OPEN-FOR, FETCH, and CLOSE Statements
  • 17.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements

(18) Bulk SQL

  • 18.1 The FORALL Statement
  • 18.1.1 Use the FORALL Statement
  • 18.2 The BULK COLLECT Clause
  • 18.2.1 Use the BULK COLLECT Statement

(19) Procedures

  • 19.1 Creating Procedures
  • 19.1.1 Create Procedures
  • 19.1.2 Query the Data Dictionary for Information on Procedures
  • 19.2 Passing Parameters into and out of Procedures
  • 19.2.1 Use IN and OUT Parameters with Procedures

(20) Functions

  • 20.1 Creating and Using Functions
  • 20.1.1 Create Stored Functions
  • 20.1.2 Make Use of Functions
  • 20.1.3 Invoke Functions in SQL Statements
  • 20.1.4 Write Complex Functions

(21) Packages

  • 21.1 The Benefits of Using Packages
  • 21.1.1 Create Package Specifications
  • 21.1.2 Create Package Bodies
  • 21.1.3 Call Stored Packages
  • 21.1.4 Create Private Objects
  • 21.1.5 Create Package Variables and Cursors
  • 21.2 Cursor Variables
  • 21.2.1 Make Use of Cursor Variables
  • 21.3 Extending the Package
  • 21.3.1 Extend the Package

(22) Stored Code

  • 22.1 Gathering Information About Stored Code
  • 22.1.1 Get Stored Code Information from the Data Dictionary
  • 22.1.2 Enforce the Purity Level with the RESTRICT_REFERENCES Pragma 500
  • 22.1.3 Overload Modules

(23) Object Types in Oracle

  • 23.1 Object Types
  • 23.1.1 Use Object Types
  • 23.1.2 Use Object Types with Collections
  • 23.2 Object Type Methods
  • 23.2.1 Use Object Type Methods

(24) Oracle Supplied Packages

  • 24.1 Making Use of Oracle Supplied Packages to Profile PL/SQL, Access Files, and Schedule Jobs
  • 24.1.1 Access Files with UTL_FILE
  • 24.1.2 Schedule Jobs with DBMS_JOB
  • 24.1.3 Submit Jobs
  • 24.2 Making Use of Oracle-Supplied Packages to Generate an Explain
  • Plan and Create HTML Pages
  • 24.2.1 Generate an Explain Plan with DBMS_XPLAN
  • 24.3 Creating Web Pages with the Oracle Web Toolkit
  • 24.3.1 Create an HTML Page with the Oracle Web Toolkit

Duration and pricing

5 Days, R12995

Certificate

Read about our certificates

Questions

Please email us

Schedule for Johannesburg

On the calendar on this page below.
If your browser doesn’t display the calendar below, please click on this link or try using Google Chrome, alternatively please enquire via our ‘Contact Us’ page.