Oracle Database 19c: متقدم PL/SQL

Introduction

• Course Agenda
• Lesson Agenda
• Assumptions
• Course Objectives
• Course Agenda
• Appendixes Used in This Course
• Lesson Agenda
• PL/SQL Development Environments
• Oracle SQL Developer
• Specifications of SQL Developer
• SQL Developer 19.x.x Interface
• Coding PL/SQL in SQL*Plus
• Lesson Agenda
• Tables Used in This Course
• Order Entry Schema
• Human Resources Schema
• Lesson Agenda
• Oracle Database 19c: Focus Areas
• Oracle Database 19c
• Lesson Agenda
• Introduction to Oracle Cloud
• Oracle Cloud Services
• Cloud Deployment Models
• Lesson Agenda
• Oracle SQL and PL/SQL Documentation
• Lesson Agenda
• New Features in 18c and 19c
• Polymorphic Table Functions
• Qualified Expressions
• SQL Macros
• Non-Persistance Support for Object Types
• Summary

Working with Exadata Express Cloud Service

• Course Agenda
• Lesson Objectives
• Lesson Agenda
• Evolving from On-premises to Exadata Express
• Exadata Express for Users
• Exadata Express for Developers
• Getting Started with Exadata Express
• Managing Exadata
• Lesson Agenda
• Service Console
• Web Access Through Service Console
• Client Access Configuration Through Service Console
• Database Administration Through Service Console
• Lesson Agenda
• SQL Workshop
• Lesson Agenda
• Connecting Through Database Clients
• Enabling SQL*Net Access for Client Applications
• Downloading Client Credentials
• Connecting Oracle SQL Developer
• Connecting Oracle SQLcl
• Summary
• Practice 2: Overview

Overview of Collections

• Course Agenda
• Objectives
• Lesson Agenda
• Collections
• Why Collections?
• Collection Types
• Lesson Agenda
• Using Associative Arrays
• Creating an Associative Array
• Traversing an Associative Array
• Collection Methods
• Lesson Agenda
• Nested Tables
• Creating Nested Table Types
• Nested Tables: Example
• Collection Constructors
• Declaring Collections: Nested Table
• Using Nested Tables
• Referencing Collection Elements
• Using Nested Tables in PL/SQL
• Lesson Agenda
• Varrays
• Declaring Collections: Varray
• Using Varrays
• Quiz
• Summary
• Practice 3: Overview

Using Collections

• Course Agenda
• Objectives
• Lesson Agenda
• Usage of Collections in Applications
• Working with Collections in PL/SQL
• Assigning Values to Collection Variables
• Accessing Values in the Collection
• Working with Collection Methods
• Using Collection Methods
• Manipulating Individual Elements
• Querying a Collection by Using the TABLE Operator
• Querying a Collection with the TABLE Operator
• Lesson Agenda
• Collection Exceptions
• Collection Exceptions: Example
• Lesson Agenda
• Listing Characteristics for Collections
• Lesson Agenda
• PL/SQL Bind Types
• Subprogram with a BOOLEAN Parameter
• Quiz
• Summary
• Practice 4: Overview

Handling Large Objects

• Course Agenda
• Objectives
• Lesson Agenda
• What Is a LOB?
• Types of LOBs
• LOB Locators and LOB Values
• Lesson Agenda
• DBMS_LOB Package
• Security Model of the DBMS_LOB Package
• What Is a DIRECTORY Object?
• Managing BFILEs: Role of a DBA
• Managing BFILEs: Role of a Developer
• Lesson Agenda
• Working on BFILEs
• Preparing to Use BFILEs
• Creating BFILE Columns in the Table
• Populating a BFILE Column with PL/SQL
• Using Data in the BFILE Column
• Lesson Agenda
• Working on CLOBs
• Initializing the LOB Columns Added to a Table
• Populating LOB Columns
• Loading Data to a LOB Column
• Writing Data to a LOB
• Lesson Agenda
• Reading LOBs from the Table
• Updating LOB by Using DBMS_LOB in PL/SQL
• Selecting CLOB Values by Using SQL
• Selecting CLOB Values by Using DBMS_LOB
• Selecting CLOB Values in PL/SQL
• Removing LOBs
• Quiz
• Lesson Agenda
• Temporary LOBs
• Creating a Temporary LOB
• Lesson Agenda
• SecureFile LOBs
• Storage of SecureFile LOBs
• Creating a SecureFile LOB
• Quiz
• Summary
• Practice 5: Overview

JSON Data in Database

• Course Agenda
• Objectives
• Lesson Agenda
• What Is JSON?
• Structure of JSON Data
• JSON Data: Example
• Why JSON?
• Lesson Agenda
• JSON Data in Oracle Database: Scenario
• JSON Data in Oracle Database
• Creating a Table with JSON Column
• JSON or Not?
• Inserting Data into JSON Columns
• Lesson Agenda
• SQL/JSON Generation Functions
• JSON_OBJECT Function
• JSON_ARRAY Function
• JSON_OBJECTAGG Function
• JSON_ARRAYAGG Function
• SQL/JSON Functions
• Lesson Agenda
• Retrieving SQL Data from JSON Object
• Accessing JSON Data
• JSON_VALUE Function
• Using SQL/JSON Functions
• Lesson Agenda
• PL/SQL Objects for JSON
• JSON Object Types in PL/SQL
• JSON Object Methods
• Getter and Setter Methods
• JSON Methods in PL/SQL: Example
• Summary
• Practice 6: Overview

Advanced Interface Methods

• Course Agenda
• Objectives
• Lesson Agenda
• PL/SQL External Procedures
• Oracle Database with Different Languages
• Scenario
• Lesson Agenda
• External Procedure Execution Architecture
• Components for External C Procedure Execution
• Defining an External C Procedure
• Define a C Function
• Creating an Alias Library
• Publishing External C Procedures
• Call Specification Syntax
• Call Specification
• Publishing an External C Routine
• Executing an External C Procedure
• Lesson Agenda
• Executing Java Programs from PL/SQL
• External Procedure Execution Architecture
• Development Steps for Java Class Methods
• Loading Java Class Methods
• Publishing a Java Class Method
• Executing the Java Routine
• Creating Call Specifications in Packages
• Quiz
• Summary
• Practice 7: Overview

Performance and Tuning

• Course Agenda
• Objectives
• Lesson Agenda
• Compiling a PL/SQL Unit
• Deciding on a Compilation Method
• Configuring the Compiler
• Viewing the Compilation Settings
• Setting Up a Database for Native Compilation
• Modifying Compilation Mode of a Program Unit
• Lesson Agenda
• PL/SQL Optimizer
• Subprogram Inlining: Introduction
• Using Inlining
• Inlining Concepts
• Inlining: How to Enable It?
• PRAGMA INLINE Example
• Inlining: Summary
• Lesson Agenda
• Why PL/SQL Tuning?
• Tuning PL/SQL Code
• Avoid Implicit Data Type Conversion
• NOT NULL Constraint
• PLS_INTEGER Data Type for Integers
• Using the SIMPLE_INTEGER Data Type
• Modularizing Your Code
• Bulk Binding
• FORALL Instead of FOR
• BULK COLLECT
• Exception While Bulk Collecting
• Handling FORALL Exceptions
• Tuning Conditional Control Statements
• Passing Data Between PL/SQL Programs
• Quiz
• Summary
• Practice 8: Overview

Improving Performance with Caching

• Course Agenda
• Objectives
• Lesson Agenda
• What Is Caching?
• Memory Architecture
• Caching in the Database Instance
• What Is Result Caching?
• Lesson Agenda
• Configuring the Server Result Cache
• Setting Result_Cache_Max_Size
• Setting the Result Cache Mode
• Using the DBMS_RESULT_CACHE Package
• Lesson Agenda
• SQL Query Result Cache
• Examining the Memory Cache
• Examining the Execution Plan for a Query
• Examining Another Execution Plan
• Executing Both Queries
• Viewing Cache Results Created
• Viewing Cache Results Found
• Lesson Agenda
• PL/SQL Function Result Cache
• Marking PL/SQL Function Results to Be Cached
• Clearing the Shared Pool and Result Cache
• Creating a PL/SQL Function by Using the RESULT_CACHE Clause
• Calling the PL/SQL Function Inside a Query
• Viewing Cache Results Created
• Calling the PL/SQL Function Again
• Viewing Cache Results Found
• Confirming That the Cached Result Was Used
• Lesson Agenda
• Oracle Database In-Memory
• Quiz
• Summary
• Practice 9: Overview

Analyzing PL/SQL Code

• Course Agenda
• Objectives
• Lesson Agenda
• PL/SQL Code Analysis
• Data Dictionary Views
• Analyzing PL/SQL Code
• Using SQL Developer for Code Analysis
• Using ALL_ARGUMENTS
• ALL_ARGUMENTS
• Using SQL Developer to Report on Arguments
• Lesson Agenda
• PL/Scope
• Using PL/Scope
• USER_IDENTIFIERS View
• Sample Data for PL/Scope
• Collecting Information on Identifiers
• Viewing Identifier Information
• Performing a Basic Identifier Search
• Using USER_IDENTIFIERS to Find All Local Variables
• Finding Identifier Actions
• Lesson Agenda
• Oracle Supplied Packages for Code Analysis
• Using DBMS_DESCRIBE
• DBMS_UTILITY Package
• Using DBMS_UTILITY.FORMAT_CALL_STACK
• Using DBMS_UTILITY
• Finding Error Information
• DBMS_METADATA Package
• DBMS_METADATA Subprograms
• FETCH_xxx Subprograms
• Filters on Metadata
• SET_FILTER Procedure
• Examples of Setting Filters
• Programmatic Use: Example 1
• Programmatic Use: Example 2
• Browsing APIs
• Using the UTL_CALL_STACK Package
• DEPRECATE Pragma
• Quiz
• Summary
• Practice 10: Overview

Profiling and Tracing PL/SQL Code

• Course Agenda
• Objectives
• Lesson Agenda
• Tracing PL/SQL Execution
• Tracing PL/SQL: Steps
• Step 1: Enable Specific Subprograms
• Steps 2 and 3: Identify a Trace Level and Start Tracing
• Step 4 and Step 5: Turn Off and Examine the Trace Data
• plsql_trace_runs and plsql_trace_events
• Lesson Agenda
• Profiling PL/SQL Code
• Hierarchical Profiling
• Hierarchical Profiling Concepts
• Using the PL/SQL Profiler
• Understanding Raw Profiler Data
• Using the Hierarchical Profiler Tables
• Using DBMS_HPROF.ANALYZE
• Using DBMS_HPROF.ANALYZE to Write to Hierarchical Profiler Tables
• Analyzer Output from the DBMSHP_RUNS Table
• Analyzer Output from the DBMSHP_FUNCTION_INFO Table
• plshprof: A Simple HTML Report Generator
• Using plshprof
• Using the HTML Reports
• Quiz
• Summary
• Practice 11: Overview

Securing Applications through PL/SQL

• Course Agenda
• Objectives
• Lesson Agenda
• Invoker’s Rights and Definer’s Rights
• Why Invoker’s Rights?
• AUTHID clause
• Lesson Agenda
• White Lists
• ACCESSIBLE BY Clause
• Using ACCESSIBLE BY Clause in Packages
• Lesson Agenda
• What Is an Application Security Policy?
• Implementing Application Security Policy
• DBMS_RLS package
• Defining a Policy
• Defining a Policy Function
• Defining a Policy
• Lesson Agenda
• Application Context – Concept
• Application Context – Implementation
• USERENV Application Context
• Creating an Application Context
• Setting a Context
• Lesson Agenda
• Virtual Private قاعدة البيانات
• Implementing a Virtual Private Database
• Setting Up a Context
• Creating the Package
• Define the Security Policy
• Setting Up the Logon Trigger
• Policy in Action
• Data Dictionary Views
• Using the ALL_CONTEXT Dictionary View
• Policy Groups
• Quiz
• Summary
• Practice 12: Overview

Safeguarding Your Code against SQL Injection Attacks

• Course Agenda
• Objectives
• Lesson Agenda
• SQL Injection
• SQL Injection: Example
• Scenario
• Types of SQL Injection
• Avoidance Strategies against SQL Injection
• Protecting against SQL Injection: Example
• Lesson Agenda
• Reducing the Attack Surface
• Expose the Database Only Via PL/SQL API
• Using Invoker’s Rights
• Strengthen Database Security
• Lesson Agenda
• Using Static SQL
• Using Dynamic SQL
• Lesson Agenda
• Using Bind Arguments with Dynamic SQL
• Using Bind Arguments with Dynamic PL/SQL
• What If You Cannot Use Bind Arguments?
• Lesson Agenda
• DBMS_ASSERT Package
• Understanding DBMS_ASSERT
• Oracle Identifiers
• Working with Identifiers in Dynamic SQL
• Choosing a Verification Route
• Validate Input Using DBMS_ASSERT
• Avoiding Injection by Using DBMS_ASSERT.SIMPLE_SQL_NAME
• DBMS_ASSERT Guidelines
• Quiz
• Summary
• Practice 13: Overview

Advanced Security Mechanisms

• Course Agenda
• Objectives
• Lesson Agenda
• Real التطبيق الأمن
• How It Works Without RAS?
• How It Works with RAS?
• Real Application Security – Components
• Implementing a RAS Data Security Policy
• Application Sessions in RAS
• RAS Sessions
• Lesson Agenda
• Transparent Data Encryption
• Encrypting a Table Column Using TDE
• Encrypting a Tablespace Using TDE
• Keystores in TDE
• Lesson Agenda
• Oracle Data Redaction
• Data Redaction Methods
• Benefits of Data Redaction
• Summary
• A Table Descriptions and Data
• B Using SQL Developer
• Objectives B-2
• What Is Oracle SQL Developer? B-3
• Specifications of SQL Developer B-4
• SQL Developer Interface B-5
• Creating a Database Connection B-7
• Browsing Database Objects B-10
• Displaying the Table Structure B-11
• Browsing Files B-12
• Finding Database Objects B-13
• Creating a Schema Object B-15
• Creating a New Table: Example B-16
• Using the SQL Worksheet B-17
• Executing SQL Statements B-20
• Saving SQL Scripts B-21
• Executing Saved Script Files: Method 1 B-22
• Executing Saved Script Files: Method 2 B-23
• Formatting the SQL Code B-24
• Using Snippets B-25
• Using Snippets: Example B-26
• Using Recycle Bin B-27
• Debugging Procedures and Functions B-28
• Database Reporting B-29
• Creating a User-Defined Report B-30
• Search Engines and External Tools B-31
• Setting Preferences B-32
• Resetting the SQL Developer Layout B-33
• Data Modeler in SQL Developer B-34
• Summary B-35
• C Using SQL*Plus
• Objectives C-2
• SQL and SQL*Plus Interaction C-3
• SQL Statements Versus SQL*Plus Commands C-4
• SQL Versus SQL*Plus C-5
• Using SQL*Plus C-6
• SQL Plus Commands: Categories C-7
• Logging In to SQL*Plus C-8
• Displaying the Table Structure C-9
• SQL*Plus Editing Commands C-11
• Using LIST, n, and APPEND C-13
• Using the CHANGE Command C-14
• SQL*Plus File Commands C-15
• Using the SAVE and START Commands C-16
• SERVEROUTPUT Command C-17
• Using the SQL*Plus SPOOL Command C-18
• Using the AUTOTRACE Command C-19
• Summary C-20
• D PL/SQL Programming Concepts: Review
• Objectives D-2
• Lesson Agenda D-3
• PL/SQL Block Structure D-4
• Naming Conventions D-5
• Procedures D-7
• Procedure: Example D-8
• Stored Functions D-9
• Functions: Example D-10
• Ways to Execute Functions D-11
• Lesson Agenda D-12
• Restrictions on Calling Functions from SQL Expressions D-13
• Lesson Agenda D-15
• PL/SQL Packages: Review D-16
• Components of a PL/SQL Package D-17
• Creating the Package Specification D-18
• Creating the Package Body D-19
• Lesson Agenda D-20
• Cursor D-21
• Processing Explicit Cursors D-23
• Explicit Cursor Attributes D-24
• Cursor FOR Loops D-25
• Cursor: Example D-26
• Lesson Agenda D-27
• Handling Exceptions D-28
• Exceptions: Example D-30
• Predefined Oracle Server Errors D-31
• Predefined Oracle Server Exceptions D-32
• Trapping Non-Predefined Oracle Server Errors D-34
• Trapping User-Defined Exceptions D-36
• Lesson Agenda D-37
• RAISE_APPLICATION_ERROR Procedure D-38
• Lesson Agenda D-40
• Dependencies D-41
• Displaying Direct and Indirect Dependencies D-43
• Lesson Agenda D-44
• Using Oracle-Supplied Packages D-45
• Some of the Oracle-Supplied Packages D-46
• DBMS_OUTPUT Package D-47
• UTL_FILE Package D-48
• Summary D-49