Oracle Database 19c: Advanced PL/SQL

This Oracle Database 19c: Advanced PL/SQL training teaches you how to use the advanced features of PL/SQL to design and tune PL/SQL to interface with the database and other applications. Expert Oracle University instructors will help you explore advanced features of program design, packages, cursors, extended interface methods and collections. In this course, you will be introduced to Oracle Database Exadata Express Cloud Service.

Course duration, academic hours: 24
Price (excl. VAT) 1800,00 
Price (with VAT): 2178,00 
Lecturer: Gints Plivna
Gints Plivna
Gints Plivna works as a data architect at Swedbank. The lecturer has a deep interest in databases, professional experience with relational database management systems (RDBMS) Oracle, Teradata, SQL Server, MySQL, as well as NoSQL DBMS - MongoDB. Instructor of Oracle SQL and PL/SQL certified courses since 2007.

Signis Vāvere
Apply for the course:

Apply or ask a question!

 

If you want to apply for the course or ask a question about it, feel free to use this form.

Please enter your first and last names
Please enter company name
Please enter your phone number
Please enter your email

Thank you! We will contact you.

Course target

In this course, you will learn to handle complex data. You learn about collections, which enable you to work with a logical group of homogenous data. You will also learn to manage large objects and JSON data in the database. Apart from handling complex data, you will learn how to interface database with other programming languages such as C and Java.

Further, you will be introduced to utilities such as PL/Scope and hierarchical profiler to analyze, trace, and profile PL/SQL code. These functions help you in improving the performance of the application. In addition, you will be introduced to various security mechanisms that can be used to secure applications.

Audience
  • Database Administrator
  • Developer
At Course Completion
  • Write powerful PL/SQL programs.
  • Explore programming efficiency.
  • Use external C and Java routines.
  • Apply PL/SQL designing best practices.
  • Create PL/SQL applications that use collections.
  • Implement a virtual private database with fine-grained access control.
  • Write code to interface with external C and Java applications.
  • Write code to interface with large objects and use SecureFile LOBs.
  • Write and tune PL/SQL code effectively to maximize performance.
  • Gain an understanding of the Oracle Database Exadata Express Cloud Service.
Prerequisites
  • Basic Knowledge of SQL, PL/SQL
  • Familiarity with programming languages
  • Oracle Database SQL and PL/SQL New Features
  • Oracle Database: Introduction to SQL/PLSQL Accelerated
  • Oracle Database 19c: SQL Tuning for Developers
  • Oracle Database: Develop PL/SQL Program Units
Training materials

Oracle University Event Learning Subscription.

Certification Exam
Course outline

1. Introduction

  • Course Objectives
  • Course Agenda
  • Describe the development environments
  • Identify the tables, data, and tools used in this course

2. PL/SQL Programming Concepts: Review

  • Identify PL/SQL block structure
  • Packages, procedures and functions
  • Cursors
  • Handle exceptions
  • Dependencies

3. Designing PL/SQL Code

  • Describe the predefined data types
  • Create subtypes based on existing types for an application
  • List the different guidelines for cursor design
  • Describe cursor variables
  • White List

4. Overview of Collections

  • Overview of collections
  • Use Associative arrays
  • Navigate using associative methods
  • Use Nested tables
  • Use Varrays
  • Compare nested tables and varrays

5. Using Collections

  • Write PL/SQL programs that use collections
  • Use Collections effectively
  • Enhancements to PL/SQL Type Binds

6. Manipulating Large Objects

  • Working with LOBs
  • Overview of SecureFile LOBs

7. Using Advanced Interface Methods

  • Calling External Procedures from PL/SQL
  • Benefits of External Procedures
  • Understand how an external routine is called from PL/SQL
  • C advanced interface methods
  • Java advanced interface methods

8. Performance and Tuning

  • Understand and influence the compiler
  • Tune PL/SQL code
  • Enable intra unit inlining
  • Identify and tune memory issues
  • Recognize network issues

9. Improving Performance with Caching

  • Describe result caching
  • Use SQL query result cache
  • Use PL/SQL function cache
  • Review PL/SQL function cache considerations

10. Analyzing PL/SQL Code

  • Finding Coding Information
  • PL/Scope Concepts
  • DBMS_METADATA Package
  • PL/SQL Enhancements

11. Profiling and Tracing PL/SQL Code

  • Tracing PL/SQL Execution
  • Tracing PL/SQL: Steps

12. Implementing VPD with Fine-Grained Access Control

  • Understand how fine-grained access control works overall
  • Describe the features of fine-grained access control
  • Describe an application context
  • Create an application context
  • Set an application context
  • List the DBMS_RLS procedures
  • Implement a policy
  • Query the dictionary views holding information on fine-grained access

13. Safeguarding Your Code Against SQL Injection Attacks

  • SQL Injection Overview
  • Reducing the Attack Surface
  • Filtering Input with DBMS_ASSERT

14. Oracle Cloud Overview

  • Introduction to Oracle Cloud & Oracle Cloud Services
  • Cloud Deployment Models
  • Evolving from On-premises to Exadata Express
  • What is in Exadata Express?
  • Exadata Express for Users & Developers
  • Oracle Exadata Express Cloud Service
  • Getting Started with Exadata Express
  • Service Console & Web Access through Service Console

If you want to get more information about this course, contact us by phone +371 67505091 or send us an e-mail at mrn@bda.lv.