Oracle Database: Develop PL/SQL Program Units Ed 2

Suggested Prerequisite

• Familiarity with programming languages
• Familiarity with data processing concepts and technique

Required Prerequisite

• Oracle Database 12c: Introduction for Experienced SQL Users

Audience

• Database Administrators
• System Analysts
• Forms Developer
• Technical Consultant
• Portal Developer
• PL/SQL Developer

Course Topics

1. Introduction

• PL/SQL development environments available in this course
• Using the SQL Worksheet
• Executing SQL Statements
• Creating and Executing Anonymous Blocks
• Course Objectives, Course Agenda and Appendixes Used in this Course
• Full Human Resources (HR) Schema
• Online Oracle Database 12c SQL and PL/SQL documentation
• Working With Script Files

2. Working with Oracle Database Exadata Express Cloud Service

• Connecting to Exadata Express Database using Database Clients
• Using SQL Developer to work with Exadata Express Database
• Using SQLcl to work with Exadata Express Database
• Using SQL*Plus to work with Exadata Express Database
• Overview of Oracle Database Exadata Express Cloud Service
• Accessing Cloud Database using SQL Workshop

3.Creating Stored Procedures

• Working with procedures
• Using formal and actual parameters
• Handling exceptions in procedures
• Passing parameters using the positional, named, or combination techniques
• Uses and benefits of procedures
• Identify the available parameter-passing modes
• Viewing the procedure information
• PL/SQL blocks and subprograms

4. Creating Functions and Debugging Subprograms

• Working with Functions
• Creating Stored Functions
• Using User-Defined Functions in SQL Statements
• Defining and executing PL/SQL functions in SQL statements
• Identifying the Advantages of Using Stored Functions in SQL Statements
• The Difference Between Procedures and Functions
• Using a PL/SQL Function in the SQL WITH Clause
• Restrictions When Calling Functions from SQL statements

5. Creating Packages

• Developing a PL/SQL Package
• Invoking the Package Constructs
• Removing a Package
• Visibility of a Package’s Components
• Components of a PL/SQL Package
• Creating and Using Bodiless Packages
• Using PL/SQL Packages
• Creating the Package Specification and Package Body

6. Working With Packages

• Controlling Side Effects of PL/SQL Subprograms
• Initializing Packages
• Using Forward Declarations to Solve Illegal Procedure Reference
• Using Package Functions in SQL and Restrictions
• Persistent State of Package Variables and Cursors
• Using PL/SQL Tables of Records in Packages
• Overloading Subprograms
• Persistent State of Packages

7. Using Oracle-Supplied Packages in Application Development

• Using the UTL_MAIL Package
• Using Oracle-Supplied Packages
• Examples of Some of the Oracle-Supplied Packages
• Using the UTL_FILE Package to Interact With Operating System Files
• Working of DBMS_OUTPUT Package

8. Using Dynamic SQL

• Introduction to Dynamic SQL
• Dynamic SQL using DBMS_SQL package
• Working With Dynamic SQL
• Dynamic SQL with mock up application
• The Execution Flow of SQL
• Using Native Dynamic SQL (NDS)
• When Do You Need Dynamic SQL?
• Using BULK COLLECT and FORALL

9. Creating Triggers

• Different types of triggers
• Creating database triggers
• Database triggers and their use
• Removing database triggers
• Database trigger firing rules

10. Creating Compound, DDL, and Event Database Triggers

• Creating triggers on DDL statements
• Displaying information about triggers
• Creating triggers on system events
• Compound triggers
• Mutating tables

11. Design Considerations for PL/SQL Code

• Grant Roles to PL/SQL Packages and Standalone Stored Subprograms
• Writing PL/SQL code that uses local subprograms
• Standardizing exceptions with an exception package
• Using the PARALLEL ENABLE hint for optimization
• Using the NOCOPY compiler hint to pass parameters by reference
• Using the AUTONOMOUS TRANSACTION pragma
• Standardizing constants with a constant package
• Describing the differences between invoker rights and definer rights

12. Using PL/SQL compiler

• Viewing the Compiler Warnings
• Viewing the Current Setting of PLSQL_WARNINGS
• Guidelines for using PLSQL_WARNINGS
• Conditional Compilation
• Using the PL/SQL Compile Time Warnings
• Using the PL/SQL Compiler with initialisation parameters

13. Managing Dependencies

• Managing local and remote procedural dependencies
• Predicting the effect of changing a database object
• Tracking procedural dependencies with dictionary views
• Dependent and referenced objects