Oracle Database 19c: Advanced PL/SQL

Required Prerequisites:

• Basic computer literacy is required

Suggested Prerequisites

• Knowledge of database concepts.
• Previous experience with any command-line program

• Database Administrator
• Developer

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

• 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

Oracle University Event Learning Subscription.

Preparation for exam:  Oracle Database Program with PL/SQL 1Z0-149

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