Сhat now
Training Center MUKКурсыOracleDatabaseOracle Database: Develop PL/SQL Program Units Ed 2

Oracle Database: Develop PL/SQL Program Units Ed 2

Course code
Orcl_Db_Develop
Duration
3 Days, 24 Hours
Course Overview
Objectives
Prerequisites
Course Outline
Course Overview

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.

Objectives

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
Prerequisites

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 Outline

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
Request the training
Oracle Database: Develop PL/SQL Program Units Ed 2
Course code:
Orcl_Db_Develop
Duration:
3 Days, 24 Hours
Delivery format
Dates
25.01.23
Location
Kyiv
Сhat now
Свяжитесь со мной
Сhat now
Отправить заявку
Registration for the webinar
Отправить заявку
Your application has been received! We will contact you soon.