Oracle Database 12c: Advanced PL/SQL
In the Oracle Database 12c R2: Advanced PL/SQL course, students learn how to use the advanced features of PL/SQL in order to design and tune PL/SQL to interface with the database and integrate with the other applications in the most efficient manner.
Description
In the Oracle Database 12c R2: Advanced PL/SQL course, students learn how to use the advanced features of PL/SQL in order to design and tune PL/SQL to interface with the database and integrate with the other applications in the most efficient manner. They learn to write powerful PL/SQL programs using external C/Java routines, fine-grained access packages, cursors, extended interfaces and collections.
What You'll Learn
- Apply PL/SQL designing best practices.
- Create PL/SQL applications that use collections.
- Work with JSON data in the database.
- 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.
Prerequisites
- Basic Knowledge of SQL, PL/SQL
- Familiarity with programming languages
- Knowledge of PL/SQL Program Units
- Oracle Database: Develop PL/SQL Program Units Ed 2
- Oracle Database 12c R2: SQL Workshop
Outline
14 modules
•
3 Days total
- Course Objectives
- Course Agenda
- Describe the full Human Resources (HR) Schema
- Identify the Appendices Used in this Course
- Review the online Oracle Database 12c SQL and PL/SQL documentation and the additional available resources
- List the PL/SQL development environments Available in this course
- Start SQL Developer and Create a Database Connection
- Use the SQL Worksheet
- Overview of Oracle Database Exadata Express Cloud Service
- Accessing Cloud Database using SQL Workshop
- Connecting to Exadata Express Database using Database Clients
- Using SQL Developer to work with Exadata Express Database
- Overview of Collections
- Use Associative arrays
- Use Nested tables
- Use Varrays
- Write PL/SQL programs that use collections
- Use Collections effectively
- Enhancements to PL/SQL Type Binds
- Binding PL/SQL only datatypes to SQL statements using DBMS_SQL
- Working with LOBs
- Overview of SecureFile LOBs
- JSON Data
- JSON data columns in tables
- Generation of JSON data with SQL/JSON generation function
- Querying JSON columns
- PL/SQL object types for JSON
- 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
- Access PL/SQL blocks from from Java classes using JDBC
- Understand and influence the compiler
- Tune PL/SQL code
- Enable intra unit inlining
- Identify and tune memory issues
- Recognize network issues
- Designing Applications for Real World Performance
- Describe result caching
- Use PL/SQL function cache
- Review PL/SQL function cache considerations
- Finding Coding Information
- PL/Scope Concepts
- DBMS_METADATA Package
- PL/SQL Enhancements
- PL/SQL Pragma to mark an item deprecated
- What is Tracing and Profiling
- Tracing PL/SQL Execution
- Tracing PL/SQL: Steps
- Controlling Access to Program Units
- Managing Access to data using PL/SQL
- Creating Secure Application roles to control access to applications
- SQL Injection Overview
- Reducing the Attack Surface
- Filtering Input with DBMS_ASSERT
- Brief introduction to Security implementation
- Fine Grained Access Control
- Application Context
- List the DBMS_RLS procedures
- Implement a policy
- Query the dictionary views holding information on fine-grained access