Oracle Database: Develop PL/SQL Program Units

3 Days

Description

This course is designed for developers with basic PL/SQL and SQL language skills. Students learn to develop, execute, and manage PL/SQL stored program units such as procedures, functions, packages, and database triggers. Students also learn to manage, PL/SQL subprograms and triggers

Learn To:

  • Create, and execute stored procedures and functions.
  • Design and use PL/SQL packages.
  • Create overloaded package subprograms for more flexibility.
  • Utilize Oracle supplied packages in application development.
  • Create triggers to solve business challenges.
  • Build and execute SQL statements dynamically.
  • Manage PL/SQL subprograms and triggers.
  • Understand and influence the PL/SQL compiler.
  • Manage dependencies.

Benefits to You

Students are introduced to the utilization of some of the Oracle-supplied packages. Additionally students learn to use Dynamic SQL, understand design considerations when coding using PL/SQL, understand and influence the PL/SQL compiler, and manage dependencies. In this course, students learn and use Oracle SQL Developer as the main environment tool to develop these program units. SQL*Plus is introduced as optional tools. Demonstrations and hands-on practice reinforce the fundamental concepts.

Audience:

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

Course Objectives:

  • Use conditional PL/SQL compilation and obfuscate (hide) code
  • Create triggers to solve business challenges
  • Manage dependencies between PL/SQL subprograms
  • Design PL/SQL code for predefined data types, local subprograms, additional pragmas and standardized constants and exceptions
  • Use the compiler warnings infrastructure
  • Create, use, and debug stored procedures and functions
  • Design and use PL/SQL packages to group and contain related constructs
  • Create overloaded package subprograms for more flexibility
  • Use the Oracle supplied PL/SQL packages to generate screen output, file output, and mail output
  • Write dynamic SQL for more coding flexibility

Upcoming Classes

Virtual Classroom Live
October 09, 2017

$2,205.00
3 Days    12:00pm EST - 8:00pm EST
view class details and enroll
Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Course Overview

  • Introduction
    • Course Objectives, Course Agenda and Appendixes Used in this Course
    • Full Human Resources (HR) Schema
    • Online Oracle Database 12c SQL and PL/SQL documentation
    • PL/SQL development environments available in this course
    • Using the SQL Worksheet
    • Executing SQL Statements
    • Working With Script Files
    • Creating and Executing Anonymous Blocks
  • Creating Stored Procedures
    • Describe PL/SQL blocks and subprograms
    • Describe the uses and benefits of procedures
    • Create, call, and remove procedures
    • Use formal and actual parameters
    • Identify the available parameter-passing modes
    • Pass parameters using the positional, named, or combination techniques
    • Handle exceptions in procedures
    • View the procedure information
  • Working with Oracle Database Exadata Express Cloud Service
    • Overview of Oracle Database Exadata Express Cloud Service
    • Accessing Cloud Database using SQL Workshop
    • 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
  • Creating Functions and Debugging Subprograms
    • Creating Stored Functions
    • The Difference Between Procedures and Functions
    • Developing Functions
    • Creating and Executing and Removing Functions
    • Identifying the Advantages of Using Stored Functions in SQL Statements
    • Using User-Defined Functions in SQL Statements
    • Using a PL/SQL Function in the SQL WITH Clause
    • Restrictions When Calling Functions from SQL statements
  • Creating Packages
    • Using PL/SQL Packages
    • The Components of a PL/SQL Package
    • The Visibility of a Package’s Components
    • Developing a PL/SQL Package
    • Creating the Package Specification and Package Body
    • Invoking the Package Constructs
    • Creating and Using Bodiless Packages
    • Removing a Package
  • Working With Packages
    • Overloading Subprograms
    • Using Forward Declarations to Solve Illegal Procedure Reference
    • Initializing Packages
    • Using Package Functions in SQL and Restrictions
    • Controlling Side Effects of PL/SQL Subprograms
    • Persistent State of Packages
    • Persistent State of Package Variables and Cursors
    • Using PL/SQL Tables of Records in Packages
  • Using Oracle-Supplied Packages in Application Development
    • Using Oracle-Supplied Packages
    • Examples of Some of the Oracle-Supplied Packages
    • How Does the DBMS_OUTPUT Package Work?
    • Using the UTL_FILE Package to Interact With Operating System Files
    • Using the UTL_MAIL Package
  • Using Dynamic SQL
    • Introduction to Dynamic SQL
    • The Execution Flow of SQL
    • Working With Dynamic SQL
    • When Do You Need Dynamic SQL?
    • Using Native Dynamic SQL (NDS)
    • Dynamic SQL with mock up application
    • Using BULK COLLECT and FORALL
    • Dynamic SQL using DBMS_SQL package
  • Design Considerations for PL/SQL Code
    • Standardize constants with a constant package
    • Standardize exceptions with an exception package
    • Write PL/SQL code that uses local subprograms
    • Grant Roles to PL/SQL Packages and Standalone Stored Subprograms
    • Use the NOCOPY compiler hint to pass parameters by reference
    • Use the PARALLEL ENABLE hint for optimization
    • Use the AUTONOMOUS TRANSACTION pragma to run independent transactions within a single transactio
    • Describe the differences between invoker rights and definer rights
  • Creating Triggers
    • Describe different types of triggers
    • Database triggers and their use
    • Create database triggers
    • Describe database trigger firing rules
    • Remove database triggers
  • Creating Compound, DDL, and Event Database Triggers
    • Compound triggers
    • Mutating tables
    • Create triggers on DDL statements
    • Create triggers on system events
    • Display information about triggers
  • Using PL/SQL compiler
    • Using the PL/SQL CompilerUsing the Initialization Parameters for PL/SQL Compilation
    • Using the PL/SQL Compile Time Warnings
    • Viewing the Current Setting of PLSQL_WARNINGS
    • Viewing the Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views
    • Guidelines for Using PLSQL_WARNINGS
    • Conditional Compilation
  • Managing Dependencies
    • Describe dependent and referenced objects
    • Track procedural dependencies with dictionary views
    • Predict the effect of changing a database object upon stored procedures and functions
    • Manage local and remote procedural dependencies

Upcoming Classes

Virtual Classroom Live
October 09, 2017

$2,205.00
3 Days    12:00pm EST - 8:00pm EST
view class details and enroll
Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Prerequisites

Required Prerequisites:

  • Basic Knowledge of PL/SQL
  • Familiarity with programming languages
  • Oracle Database: Introduction to SQL Ed 1.1
  • Oracle Database: PL/SQL Fundamentals

Suggested Prerequisites:

  • Oracle SQL Tuning for Developers Workshop

Upcoming Classes

Virtual Classroom Live
October 09, 2017

$2,205.00
3 Days    12:00pm EST - 8:00pm EST
view class details and enroll
Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date