Course Contents

Duration : 40 Hours

Introduction

• Oracle Database : Focus Areas 
• Oracle Database   
• PL/SQL Development Environments  
• Oracle SQL Developer  
• Specifications of SQL Developer   
• SQL Developer Interface   
• Coding PL/SQL in SQL*Plus   
• SQL Developer Web   

2 Introduction to PL/SQL

• Limitations of SQL  
• Why PL/SQL?  
• Why PL/SQL 
• About PL/SQL  
• Benefits of PL/SQL 
• PL/SQL Runtime Architecture 
• PL/SQL Block Structure  
• Block Types 
• Examining an Anonymous Block
• Executing an Anonymous Block 
• Enabling Output of a PL/SQL Block 
• Viewing the Output of a PL/SQL Block 

3 Declaring PL/SQL Variables

• Variables 
• Variables in PL/SQL 
• Requirements for Variable Names 
• Using Variables in PL/SQL
• Declaring and Initializing PL/SQL Variables 
• Declaring and Initializing PL/SQL Variables  
• Initializing Variables Through a SELECT Statement 
• Types of Variables  
• Declaring Variables  
• Guidelines for Declaring and Initializing PL/SQL Variables  
• Guidelines for Declaring PL/SQL Variables 
• Data Types for Strings  
• Delimiters in String Literals
• Data Types for Numeric values  
• Data Types for Date and Time values  
• Data Type Conversion  
• The %TYPE Attribute   
• Declaring Variables with the %TYPE Attribute   
• Declaring Boolean Variables   
• LOB Data Type Variables  
• Composite Data Types: Records and Collections  
• Bind Variables   
• Bind Variables: Examples  
• Using AUTOPRINT with Bind Variables  

4 Writing Executable Statements

• Lexical Units in a PL/SQL Block  
• PL/SQL Block Syntax and Guidelines  
• Commenting Code  
• SQL Functions in PL/SQL  
• SQL Functions in PL/SQL: Examples  
• Using Sequences in PL/SQL blocks   
• Using Sequences in PL/SQL Blocks   
• Nested blocks   
• Nested Blocks: Example  
• Variable Scope and Visibility  
• Using a Qualifier with Nested Blocks  
• Challenge: Determining the Variable Scope 
• Operators in PL/SQL 
• Operators in PL/SQL: Examples  
• Programming Guidelines   
• Indenting Code  

5 Using SQL Statements Within a PL/SQL Block

• SQL Statements in PL/SQL  
• SELECT Statements in PL/SQL  
• Retrieving Data in PL/SQL: Example  
• Retrieving Data in PL/SQL  
• Naming Ambiguities   
• Avoiding Naming Ambiguities  
• Using PL/SQL to Manipulate Data 
• Insert Data: Example   
• Update Data: Example  
• Delete Data: Example  
• Merging Rows  
• SQL Cursor  
• SQL Cursor Attributes for Implicit Cursors  

6 Writing Control Structures

• PL/SQL Control Structures 
• IF Statement  
• IF-ELSIF Statements  
• Simple IF Statement   
• IF THEN ELSE Statement  
• IF ELSIF ELSE Clause   
• NULL Value an in IF Statement  
• CASE Expressions   
• Searched CASE Expressions   
• CASE Statement   
• Handling Nulls  
• Logic Tables   
• Boolean Expression or Logical Expression?  
• Iterative Control: LOOP Statements  
• Basic Loops   
• Basic Loop: Example   
• WHILE Loops   
• WHILE Loops: Example   
• FOR Loops   
• FOR Loops: Example  
• FOR Loop Rules   
• Suggested Use of Loops   
• Nested Loops and Labels  
• Nested Loops and Labels: Example  
• PL/SQL CONTINUE Statement   
• PL/SQL CONTINUE Statement: Example 1   
• PL/SQL CONTINUE Statement: Example 2  

7 Working with Composite Data Types

• Composite Data Types  
• PL/SQL Records Versus Collections  
• PL/SQL Records   
• Creating a PL/SQL Record  
• Creating a PL/SQL Record: Example  
• PL/SQL Record Structure   
• %ROWTYPE Attribute   
• Creating a PL/SQL Record: Example   
• Advantages of Using the %ROWTYPE Attribute   
• Another %ROWTYPE Attribute: Example  
• Inserting a Record by Using %ROWTYPE  
• Updating a Row in a Table by Using a Record  
• Associative Arrays (INDEX BY Tables)  
• Associative Array Structure   
• Steps to Create an Associative Array  
• Creating and Accessing Associative Arrays   
• Associative Arrays with Record values  
• Using Collection Methods   
• Using Collection Methods with Associative Arrays   
• Nested Tables   
• Nested Tables: Syntax and Usage  
• Variable-Sized Arrays (Varrays)   
• VARRAYs: Syntax and Usage   

8 Using Explicit Cursors

• Cursors  
• Implicit Cursors  
• Explicit Cursor  
• Controlling Explicit Cursors   
• Declaring the Cursor   
• Opening the Cursor  
• Fetching Data from the Cursor  
• Closing the Cursor   
• Cursors and Records   
• Cursor FOR Loops  
• Explicit Cursor Attributes   
• %ISOPEN Attribute   
• %ROWCOUNT and %NOTFOUND: Example  
• Cursor FOR Loops Using Subqueries   
• Cursors with Parameters   
• FOR UPDATE Clause   
• WHERE CURRENT OF Clause   
• WHERE CURRENT OF Clause: Example   

9 Handling Exceptions

• What Is an Exception?  
• Handling an Exception: Example   
• Understanding Exceptions with PL/SQL  
• Handling Exceptions   
• Exception Types   
• Syntax to Trap Exceptions   
• Guidelines for Trapping Exceptions   
• Trapping Internally Predefined Exceptions   
• Internally Defined Exception Trapping: Example  
• Trapping Predefined Exceptions  
• Functions for Trapping Exceptions  
• Trapping User-Defined Exceptions  
• RAISE Statement   
• Trapping User-Defined Exceptions   
• Propagating Exceptions in a Sub-Block   
• The RAISE_APPLICATION_ERROR Procedure   

10 Introducing Stored Procedures and Functions

• What Are PL/SQL Subprograms?   
• Differences Between Anonymous Blocks and Subprograms   
• Procedure: Syntax   
• Creating a Procedure   
• Invoking a Procedure   
• Function: Syntax   
• Creating a Function   
• Invoking a Function   
• Passing a Parameter to the Function   
• Invoking the Function with a Parameter   

11 Creating Procedures

• Modularized Program Design   
• Modularizing Code with PL/SQL  
• Benefits of Modularization   
• What Are PL/SQL Subprograms?  
• Procedures   
• What Are Procedures?   
• Creating Procedures: Overview   
• Creating Procedures   
• Creating Procedures Using SQL Developer  
• Compiling Procedures   
• Calling Procedures   
• Calling Procedures Using SQL Developer   
• Procedures   
• What Are Parameters and Parameter Modes?   
• Formal and Actual Parameters   
• Procedural Parameter Modes   
• Comparing the Parameter Modes   
• Using the IN Parameter Mode: Example   
• Using the OUT Parameter Mode: Example   
• Using the IN OUT Parameter Mode: Example   
• Passing Parameters to Procedures   
• Passing Actual Parameters: Creating the raise_sal Procedure   
• Passing Actual Parameters: Examples   
• Using the DEFAULT Option for the Parameters   
• Handled Exceptions   
• Handled Exceptions: Example   
• Exceptions Not Handled   
• Exceptions Not Handled: Example   
• Removing Procedures: Using the DROP SQL Statement or SQL Developer  
• Viewing Procedure Information Using the Data Dictionary Views   
• Viewing Procedures Information Using SQL Developer   

12 Creating Functions

• Functions   
• Creating Functions syntax   
• Tax Calculation   
• The Difference Between Procedures and Functions  
• Creating Functions: Overview   
• Invoking a Stored Function: Example   
• Using Different Methods for Executing Functions   
• Creating and Compiling Functions Using SQL Developer   
• Using a Function in a SQL Expression: Example   
• Calling User-Defined Functions in SQL Statements   
• Restrictions When Calling Functions from SQL Expressions   
• Side Effects of Function Execution   
• Controlling Side Effects   
• Guidelines to Control Side Effects   
• Passing Parameters to Functions   
• Named and Mixed Notation from SQL: Example   
• Viewing Functions Using Data Dictionary Views   
• Viewing Functions Information Using SQL Developer   
• Removing Functions: Using the DROP SQL Statement or SQL Developer  

13 Debugging Subprograms

• Before Debugging PL/SQL Subprograms 
• Debugging a Subprogram: Overview  
• The Debugging – Log Tab Toolbar   
• Tracking Data and Execution   
• Debugging a Procedure Example: Creating a New emp_list Procedure   
• Debugging a Procedure Example: Creating a New get_location Function   
• Setting Breakpoints and Compiling emp_list for Debug Mode   
• Compiling the get_location Function for Debug Mode   
• Debugging emp_list and Entering Values for the PMAXROWS Parameter   
• Debugging emp_list: Step Into (F7) the Code   
• Viewing the Data   13-23
• Modifying the Variables While Debugging the Code   
• Debugging emp_list: Step Over Versus Step Into   
• Debugging emp_list: Step Out of the Code (Shift + F7)   
• Debugging emp_list: Step to End of Method   
• Debugging a Subprogram Remotely: Overview   

14 Creating Packages

• DBMS_OUTPUT.PUT_LINE  
• What Is a Package?  
• Advantages of Packages  
• How Do You Create PL/SQL Packages?  
• Components of a PL/SQL Package  
• Application Program Interface   
• Creating the Package Specification: Using the CREATE PACKAGE Statement  
• Creating Package Specification: Using SQL Developer  
• Creating the Package Body: Using SQL Developer  
• Example of a Package Specification: comm_pkg   
• Creating the Package Body   
• Example of a Package Body: comm_pkg   
• Invoking the Package Subprograms: Examples   
• Invoking Package Subprograms: Using SQL Developer   
• Creating and Using Bodiless Packages   
• Viewing Packages by Using the Data Dictionary  
• Viewing Packages by Using SQL Developer   
• Removing Packages   
• Removing Package Bodies   
• Guidelines for Writing Packages   

15 Working with Packages

• Why Overload Subprograms?   
• Overloading Subprograms in PL/SQL   
• Overloading Procedures Example: Creating the Package Specification  
• Overloading Procedures Example: Creating the Package Body  
• Restrictions on Overloading   
• STANDARD package   
• Overloading and the STANDARD Package   
• Package Instantiation and Initialization   
• Initializing Packages in Package Body   
• Using User-Defined Package Functions in SQL   
• User-Defined Package Function in SQL: Example   
• Serially Reusable Packages   
• Memory Architecture  
• Serially Reusable Packages   
• Persistent State of Packages   
• Persistent State of Package Variables: Example   
• Persistent State of a Package Cursor: Example   
• Executing the CURS_PKG Package   

16 Using Oracle-Supplied Packages in Application Development

• Using Oracle-Supplied Packages  
• Examples of Some Oracle-Supplied Packages  
• How the DBMS_OUTPUT Package Works  
• Using the UTL_FILE Package  
• Some of the UTL_FILE Procedures and Functions   
• File Processing Using the UTL_FILE Package: Overview   
• Using the Available Declared Exceptions in the UTL_FILE Package  
• FOPEN and IS_OPEN Functions: Example   
• Using UTL_FILE: Example   
• What Is the UTL_MAIL Package?  
• Setting Up and Using the UTL_MAIL: Overview   
• Summary of UTL_MAIL Subprograms  
• Installing and Using UTL_MAIL   
• The SEND Procedure Syntax   
• The SEND_ATTACH_RAW Procedure  
• Sending Email with a Binary Attachment: Example   
• The SEND_ATTACH_VARCHAR2 Procedure   
• Sending Email with a Text Attachment: Example  

17 Using Dynamic SQL

• What is Dynamic SQL?  
• When do you use Dynamic SQL?   
• Using Dynamic SQL   
• Execution Flow of SQL Statements  
• Dynamic SQL implementation  
• Native Dynamic SQL (NDS)   
• Using the EXECUTE IMMEDIATE Statement   
• Dynamic SQL with a DDL Statement: Examples   
• Dynamic SQL with DML Statements   
• Dynamic SQL with a Single-Row Query: Example   
• Executing a PL/SQL Anonymous Block Dynamically   
• BULK COLLECT INTO clause   
• OPEN FOR clause   
• Using BULK COLLECT and OPEN FOR clause   
• Summarizing Methods for Using Dynamic SQL   
• Using the DBMS_SQL Package   
• Using the DBMS_SQL Package Subprograms   
• Using DBMS_SQL with a DML Statement: Deleting Rows  
• Using DBMS_SQL with a Parameterized DML Statement   

18 Creating Triggers

• What are Triggers?   
• Defining Triggers   
• Why do you use Triggers?   
• Trigger Event Types   
• Available Trigger Types   
• Trigger Event Types and Body   
• Creating DML Triggers by Using the CREATE TRIGGER Statement   
• Creating DML Triggers by Using SQL Developer   
• Specifying the Trigger Execution Time   
• Creating a DML Statement Trigger Example: SECURE_EMP   
• Testing Trigger SECURE_EMP   
• Using Conditional Predicates   
• Multiple Triggers of the Same Type   
• CALL Statements in Triggers   
• Statement-Level Triggers Versus Row-Level Triggers  
• Creating a DML Row Trigger   
• Correlation names and Pseudorecords   
• Correlation Names and Pseudorecords   
• Using OLD and NEW Qualifiers   
• Using OLD and NEW Qualifiers: Example   
• Using the WHEN Clause to Fire a Row Trigger Based on a Condition  
• Trigger-Firing Sequence: Single-Row Manipulation   
•       Trigger-Firing Sequence: Multirow Manipulation   
• Summary of the Trigger Execution Model   
• INSTEAD OF Triggers   
• Creating an INSTEAD OF Trigger: Example   
• Creating an INSTEAD OF Trigger to Perform DML on Complex Views   
• The Status of a Trigger   
• System Privileges Required to Manage Triggers   
• Managing Triggers by Using the ALTER and DROP SQL Statements   
• Managing Triggers by Using SQL Developer   
• Viewing Trigger Information   
• Using USER_TRIGGERS   
• Testing Triggers   

19 Creating Compound, DDL, and Event Database Triggers

• What is a Compound Trigger?  
• Working with Compound Triggers  
• Why Compound Triggers?  
• Compound Trigger Structure   
• Compound Trigger Structure for Views   
• Compound Trigger Restrictions   
• Mutating Tables   
• Mutating Table: Example   
• Using a Compound Trigger to Resolve the Mutating Table Error  
• Creating Triggers on DDL Statements   
• Creating Triggers on DDL Statements -Example  
• Creating Database Triggers   
• Creating Triggers on System Events   
• LOGON and LOGOFF Triggers: Example   
• Guidelines for Designing Triggers   

20 Design Considerations for the PL/SQL Code

• Standardizing Constants and Exceptions  
• Standardizing Exceptions   
• Standardizing Exception Handling  
• Standardizing Constants   
• Local Subprograms   
• Definer’s and Invoker’s Rights   
• Specifying Invoker’s Rights: Setting AUTHID to CURRENT_USER   
• Granting Privileges to Invoker’s Rights Unit   
• Autonomous Transactions   
• Features of Autonomous Transactions   
• Using Autonomous Transactions: Example   
• Using the NOCOPY Hint   
• Effects of the NOCOPY Hint  
• When Does the PL/SQL Compiler Ignore the NOCOPY Hint?  
• Using the PARALLEL_ENABLE Hint   
• Using the Cross-Session PL/SQL Function Result Cache   
• Declaring and Defining a Result-Cached Function: Example    
• Using the DETERMINISTIC Clause with Functions   
• Using the RETURNING Clause  
• Using Bulk Binding   
• Bulk Binding: Syntax and Keywords   
• Bulk Binding FORALL: Example  
• Using BULK COLLECT INTO with Queries  
• Using BULK COLLECT INTO with Cursors   
• Using BULK COLLECT INTO with a RETURNING Clause  

21 Tuning the PL/SQL Compiler

• Optimizing PL/SQL Compiler Performance  
• Initialization Parameters for PL/SQL Compilation  
• Using the Initialization Parameters for PL/SQL Compilation  
• Displaying the PL/SQL Initialization Parameters  
• Displaying and Setting PL/SQL Initialization Parameters   
• Changing PL/SQL Initialization Parameters: Example   
• PL/SQL Compile-Time Warnings   
• Benefits of Compiler Warnings    
• Categories of PL/SQL Compile-Time Warning Messages   
• Enabling Warning Messages   
• Setting Compiler Warning Levels: Using PLSQL_WARNINGS, Examples   
• Enabling Compiler Warnings: Using PLSQL_WARNINGS in SQL Developer   
• Viewing the Current Setting of PLSQL_WARNINGS   
• Viewing Compiler Warnings   
• SQL*Plus Warning Messages: Example   
• Defining PLSQL_WARNINGS for Program Units   
• Using the DBMS_WARNINGS Package   
• Using the DBMS_WARNING Package Subprograms   
• The DBMS_WARNING Procedures: Syntax, Parameters, and Allowed Values   
• The DBMS_WARNING Procedures: Example   
• The DBMS_WARNING Functions: Syntax, Parameters, and Allowed Values   
• The DBMS_WARNING Functions: Example   
• Using DBMS_WARNING: Example   

22 Managing Dependencies

• What are Dependencies in a Schema?  
• How Dependencies Work?   
• Dependent and Referenced Objects  
• Querying Object Dependencies: Using the USER_DEPENDENCIES View   
• Querying an Object’s Status   
• Categorizing Dependencies  
• Direct Dependencies   
• Indirect Dependencies   
• Displaying Direct and Indirect Dependencies   
• Fine-Grained Dependency Management   
• Fine-Grained Dependency Management: Example 1  
• Fine-Grained Dependency Management: Example 2  
• Guidelines for Reducing Invalidation  
• Object Revalidation  
•       Remote Dependencies  
• Managing Remote Procedure Dependencies  
• Setting the REMOTE_DEPENDENCIES_MODE Parameter  
• Timestamp Checking   
• Signature Checking   
• Revalidating PL/SQL Program Units   
• Unsuccessful Recompilation   
• Successful Recompilation   
• Recompiling Procedures   
• Packages and Dependencies: Subprogram References the Package   
• Packages and Dependencies: Package Subprogram References Procedure