Course Overview

PL/SQL is Oracle's extension language for standard SQL. In this PL/SQL training class, students who already know SQL, learn to use PL/SQL to write sophisticated queries against an Oracle database.

Course Content


  • Describing PL/SQL.
  • Describing the use of PL/SQL for the Developer and the Database Administrator.
  • Explaining the benefits of PL/SQL.
  • PL/SQL program constructs.
  • PL/SQL ananymous block structure.

Subprogram block structure.

  • Declaring variables
  • Recognizing the basic PL/SQL block and its sections.
  • Describing the significance of variables in PL/SQL.
  • Distinguishing between PL/SQL and Non-PL/SQL variables.
  • Declaring variables and constants.
  • Executing a PL/SQL block.

Writing executable statements

  • Recognizing the significance of the executable section.
  • Writing statements within the executable section.
  • Describing the rules of nested blocks.
  • Executing and testing a PL/SQL block.
  • Using coding conventions.

Interacting with the Oracle server

  • Writing a successful SELECT statement in PL/SQL.
  • Declaring the data type and size of a PL/SQL variable dynamically.
  • Writing Data Manipulation Language (DML) statements in PL/SQL.
  • Controlling transactions in PL/SQL.
  • Determining the outcome of SQL DML
  • Determining the outcome of SQL DML statements.

Writing control structures

  • Identifying the uses and types of control structures.
  • Constructing an IF statement.
  • Constructing and identifying different loop statements.
  • Controlling block flow using nested loops and labels.
  • Using logic tables.

Working with composite data types

  • Creating user-defined PL/SQL records.
  • Creating a PL/SQL table.
  • Creating a PL/SQL table of records.
  • Differentiating among records, tables and tables of records.

Writing explicit cursors

  • Using a PL/SQL record variable.
  • Distinguishing between implicit and explicit cursor.
  • Writing a cursor FOR loop.

Advanced explicit cursor concepts

  • Writing a cursor that uses parameters.
  • Determining when a FOR UPDATE clause in a cursor is required.
  • Using a PL/SQL table variable.
  • Using a PL/SQL table of records.

Handling Exceptions

  • Defining PL/SQL exceptions.
  • Recognizing unhandled exceptions.
  • Listing and using different types of PL/SQL exception handlers.
  • Trapping unanticipated errors.
  • Describing the effect of exception propagation in nested blocks.
  • Customizing PL/SQL exception messages.

Generating Procedures

  • Describe the uses of procedures.
  • Create procedures.
  • Create procedures with arguments.
  • Invoke a procedure.
  • Remove a procedure.

Creating Functions

  • Describe the uses of functions.
  • Create a function.
  • Invoke a function.
  • Remove a function.
  • Differentiate between a procedure and a function.

Managing Subprograms

  • Describe system and object privilege requirements.
  • Query the relevant data dictionary views.
  • Debug subprograms.

Creating Packages

  • Describe packages and list their possible components.
  • Create packages that include public and private subprograms, as global and local variables.
  • Invoke objects in a package.
  • Remove packages.

More Package concepts

  • Write packages that use the overloading feature of PL/SQL.
  • Avoid errors with mutually referential subprograms.
  • Initialize variables with a one-time-only procedure.
  • Specify the purity level of packaged functions.
  • Describe the persistent state of packaged variables, cursors, tables, and records.
  • Query the relevant data dictionary views.

Using Oracle-Supplied Packages

  • Overview of Oracle-supplied packages.
  • View examples of some supplied packages.
  • Writing dynamic SQL.

Creating database triggers

  • Describe different types of triggers.
  • Describe database triggers and their use.
  • Create database triggers.
  • Describe database trigger firing rules.
  • Drop database triggers.

More trigger concepts

  • Create triggers that fire when certain database actions occur.
  • List some of the limitations of database triggers.
  • Determine when to use database triggers or Oracle Server features.
  • Create triggers by using alternative events (not INSERT/UPDATE/DELETE).
  • Create triggers by using alternative levels (not STATEMENT/ROW).
  • Query the relevant data dictionary views.

Managing Dependencies

  • Overview of object dependencies
  • Manage PL/SQL objects for recompilation

Manipulating Large Objects

  • Compare and contrast LONG/RAW/LONG RAW with large objects (LOBs)
  • Understand LOBs
  • Manage binary large file objects (BFILEs)
  • Use PL/SQL with an LOB
  • Create a table with LOB columns
  • Manipulate LOBs
  • Use DBMS_LOB Oracle-supplied packages
  • Create a temporary LOB
  • Suggested Next Courses

Customer Reviews

Thanks to Xpertised and the tutor who walked me through all the topics with Practical exposure which is helping me in my current project.

Course was quite helpful in terms of understanding of concepts and practicality. Its really a very friendly environment to learn. The timing were mutually chosen, as we both are working professional. I am quite satisfied with the course.


For Batch Details
Call us at: +91 7259222234

Not sure? Consult Our Experts

Looking for a Training for


My Team/Organization

I agree to be contacted over mail or phone

Call us at: +91 7259222234