Отримати консультацію
Training Center MUKКурсыOracleDatabaseOracle Database: Develop PL/SQL Program Units Ed 2

Oracle Database: Develop PL/SQL Program Units Ed 2

Код курсу
3 Днів, 24 Ак. Годин
Опис курсу
Програма курсу
Опис курсу

Course overview

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.


Course Objectives

  • 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
  • Use conditional PL/SQL compilation and obfuscate (hide) code

Suggested Prerequisite

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

Required Prerequisite

  • Oracle Database 12c: Introduction for Experienced SQL Users


  • 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?

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
  • 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
Реєстрація на найближчий курс
Oracle Database: Develop PL/SQL Program Units Ed 2
Код курсу:
3 Днів, 24 Ак. Годин
Отримати консультацію
Свяжитесь со мной
Отримати консультацію
Отправить заявку
Реєстрація на вебінар
Отправить заявку
Ваша заявка отримана!
Ми зв`яжимося з вами найближчим часом.