Oracle 19c PL/SQL Fundamentals is a three-day, hands-on course that introduces Oracle database programming using the PL/SQL programming language. Throughout the course students will explore the core syntax, structure and features of the language. This course will also lay the foundation for the entire Oracle PL/SQL programming series, allowing one to progress from introductory topics to advanced application design and programming and finally onto writing complex high-performance applications. The course also explores applying the newly learned skills to the development of database applications. Participants will learn how to use database-resident stored program units such as procedures, functions, packages and database triggers. Students will also learn about the latest features in Oracle

starstarstarstarstar

* Actual course outline may vary depending on offering center. Contact your sales representative for more information.

Learning Objectives

Working within in a hands-on learning environment, guided by our expert team, attendees will develop a practical approach to Oracle Database Technology. Throughout the course participants will explore:
Using PL/SQL programming language for database applications and development
incorporating PL/SQL modules within the application architecture from the initial design and planning phase
The essentials of building executable PL/SQL program units
Each of the major segments of a working program and how these interact with each other during program execution
Important error or exception handling capabilities of the language.
How database-resident program units can be used as part of the overall database application architecture
Applying these new skills to the development of PL/SQL packages.
Advanced database programming capabilities and benefits
How database triggers can be used as part of an advanced database application design

1

  • Selection & Setup of the Database Interface

  • Considering Available Tools
    Selecting the Appropriate Tool
    Oracle Net Database Connections
    Oracle PAAS Database Connections
    Setup SQL Developer
    Setup SQL *Plus
    Setup JDeveloper
    About BIND and Substitution Variables
    Using SQL Developer
    Using SQL *Plus
2

  • Choosing a Database Programming Language

  • What is Database Programming
    PL/SQL Programming
    PL/SQL Performance Advantages
    Integration with Other Languages
3

  • PL/SQL Language Essentials

  • PL/SQL Program Structure
    Language Syntax Rules
    Embedding SQL
    Writing Readable Code
    Generating Readable Code
    Generating Database Output
    SQL * Plus Input of Program Block
4

  • DECLARE Section

  • About the Declare Section
    Declare Primitive Types
    Declaration Options
    Not Null
    Constant
    Data Dictionary Integration
    % Type
    Declare Simple User-Defined Types
    Type … Table
    Type … Record
    Extended User Defined Types
5

  • BEGIN Section

  • About the Begin Section
    Manipulating Program Data
    Logic Control & Braching
    GOTO
    LOOP
    IF-THEN-ELSE
    CASE
6

  • EXCEPTION Section

  • About the Exception Section
    Isolating the Specific Exception
    Pragma Exception_INIT
    SQLCODE &SQLERRM Example
    SQL%ROWCOUNT & Select … Into
7

  • Beyond the Basics : Explicit Cursors

  • About Explicit Cursors
    Extend Cursor Techniques
    For Update of Clause
    Where Current of Clause
    Using for … Loop Cursors
8

  • Introduction Database Resident Programming Units

  • About Database – Resident Programs
    Physical Storage & Execution
    Types of Stored Program Units
    Stored Program Unit Advantages
    Modular Design Principles
9

  • Creating Stored Procedures and Functions

  • Stored Procedures & Functions
    Create Procedure / Create Function
    Creating Procedures & Functions
    Raise_Salary() Procedure
    Salary_Valid() function
    The Parameter Specification
    Default Clause
    System & Object Privileges
    Using the Development Tools
10

  • Executing Stored Procedures and Functions

  • Calling Procedures & Functions
    Unit Testing with Execute
    Anonymous Block Unit Testing
    Specifying a Parameter Notation
    SQL Worksheet Unit Testing
    Calling Functions from SQL
11

  • Maintaining Stored Programming Units

  • Recompiling Programs
    Mass Recompilation Using UTL_RECOMP()
    Dropping Procedures & Functions
    Drop Procedures & Functions
    Drop Procedure / Function
    Data Dictionary Metadata
    Using USER_OBJECTS
    Using USER_SOURCE
    Using USER_ERRORS
    Using USER_OBJECT_SIZE
    Using USER_DEPENDENCIES
12

  • Managing Dependencies

  • Dependency Internals
    Tracking Dependencies
    The Dependency Tracking Utility
    SQL Developer Dependency Info
    Dependency Strategy Checklists
13

  • Creating & Maintaining

  • About Packages
    Creating Packages
    Maintaining Packages
    Performance Considerations
14

  • Advanced Package Capabilities

  • Definer & Invoker Rights
    White Lists & Accessible By
    Persistent Global Objects
    Defining Initilization Logic
    Object Orientation Support
15

  • Advanced Cursor Techniques

  • Using Cursor Variables
    Using SYS_REFCURSOR
    Using Cursor Expressions
16

  • Using System Supplied Packages

  • DBMS_OUTPUT()
    UTL_FILE()
    FOPEN() Example
17

  • Database Trigger Concepts

  • About Database Triggers
    DML Event Trigger Sub-Types
    Database Trigger Scenario
    Trigger Exhaustion Mechanisms
    Trigger within SQL Worksheet
18

  • Creating Database Triggers

  • Statement Level Triggers
    Using Raise Application_Error()
    Row-Level Triggers
    Examples of Triggers
    Employee_Salary_Check Example
    Employee_Journal Example
    Budget_Event Example
    Instead of Triggers
    Triggers within and Application
19

  • Maintaining Database Triggers

  • Call Syntax
    Trigger Maintenance Tasks
    Show Errors Trigger
    Drop Trigger
    Alter Trigger
    Multiple Triggers for a Table
    Handling Mutating Table Issues
20

  • Implementing System Event Triggers

  • What are System Event Triggers
    Defining the Scope
    Available System Events
    System Event Attributes

Audience

This course is geared for anyone needing to interface with an Oracle database such as end users, business analysts, application developers and database administrators / DBAs.

Language

English

Prerequisites

While there are no prerequisites for this course, please ensure you have the right level of experience to be successful in this training.

$2,195

Length: 3.0 days (24 hours)

Level:

Not Your Location? Change

Course Schedule:

Schedule select
11
Dec
Monday
10:00 AM ET -
6:00 PM ET
Filling Fast
Available
Loading...