Course Content

Introduction – PL/SQL Architecture

* Declaring Variables

PLSQL block

* Writing Executable Statements

* Interacting with Oracle Server

* Writing Control Structures

* Comments in PLSQL

* Variable Scope

* Using GOTO

* Using the CASE Statement

* Using the If-Then-Else

* Using the If-Then-Else

* Working with LOOPS

* Continue Statement

* Working with Composite Data Types

Cursor

* Cursors Implicit

* Understanding the Concept and Purpose of Explicit Cursors

* Learn about Cursor Attributes

* Creating and Using Cursor Parameters

* Cursors with Records

* Looping with Cursors

Exceptions

* Difference between Global vs Local Objects and Exceptions

* Handling Exceptions

* What Are the Types of Exceptions

* Creating User-Defined Exceptions

* Using SQLCODE and SQLERRM Objects

Procedures & Functions

* Creating Procedures

* Input and Output of procedures

* Understanding the Parameter Specification

* Standalone procedures

* Creating Functions

* Managing Subprograms

* Overloading the Subprograms

* What are Functions & Procedures and Why We Use

Creating Packages

* Creating & Using & Modifying & Removing the Packages

* Visibility of Package Objects

* Illegal Object Reference & Forward Declaration

* Package Initialization

* How Can I Find the Packages?

Triggers

* Creating Database Triggers

* Types of triggers

* Statement & Row Level Triggers

* Using New & Old Qualifiers in Triggers

* Using RAISE_APPLICATION_ERROR Procedure in Triggers

* Using WHEN Clause in Triggers

* INSTEAD OF Triggers

* Exploring & Managing the Triggers

* Compound Triggers

* Handling Mutating Table Errors

Trainer Profile

Interview Questions & Answer

Blog

An error condition is called an exception in PL/SQL.  Exceptions can be internally defined (by the runtime system) or can be user-defined. For example, internally defined exceptions include division by zero and out of memory.

When an error occurs, an exception is raised. That is normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised automatically by run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.
in order to handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, then control returns to the host environment.

Example: Runtime Error Handling

DECLARE

stock_price NUMBER := 9.73;

net_earnings NUMBER := 0;

pe_ratio NUMBER;

BEGIN

-- Calculation might cause division-by-zero error.

pe_ratio := stock_price / net_earnings;

DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);

EXCEPTION -- exception handlers begin

-- Only one of the WHEN blocks is executed.

WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error

DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.');

pe_ratio := NULL;

WHEN OTHERS THEN -- handles all other errors

DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.');

pe_ratio := NULL;

END; -- exception handlers and block end here

/

Rules for Avoiding and Handling PL/SQL Errors and Exceptions        

• Add exception handlers whenever there is any possibility of the error occurring.
• Add error-checking code whenever you can predict that an error might occur if your code gets a bad input data.
• Make your programs robust sufficient to work even if the database is not in the state you expect.
• Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers.
• Test your code with different combinations of the bad data to see what potential errors arise.
• Write out debugging information in your exception handlers.