DURATION: 3 days (24 hours)
COURSE INTRODUCTION
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.
COURSE OBJECTIVE
After finish the course, student will have knowledge and skills to:
- Design PL/SQL packages and program units that execute efficiently
- Write code to interface with external applications and the operating system
- Create PL/SQL applications that use collections
- Learn to work with JSON data in relational database
- Write and tune PL/SQL code effectively to maximize performance
- Implement a virtual private database with fine-grained access control
- Write code to interface with large objects and use SecureFile LOBs
AUDIENCE
- Application Developers
- Database Administrators
- Support Engineer
- Technical Consultant
PREREQUISITES
- Basic Knowledge of SQL, PL/SQL
- Knowledge of PL/SQL Program Units
- Familiarity with programming languages
- Oracle Database: SQL Workshop I Ed 2
- Oracle Database: Develop PL/SQL Program Units Ed 2
RELATED COURSES
Oracle Database: SQL Tuning for Developers
COMPLETION CERTIFICATE
After finish the course, student will receive completion certificate issued by SmartPro.
COURSE CONTENT
1. Introduction
- 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
2. Oracle Database Exadata Express Cloud Service
- 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
3. Overview of Collections
- Overview of Collections
- Use Associative arrays
- Use Nested tables
- Use Varrays
4. Using Collections in PL/SQL
- 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
5. Manipulating Large Objects
- Working with LOBs
- Overview of SecureFile LOBs
6. Working with JSON Data
- 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
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
- Access PL/SQL blocks from from Java classes using JDBC
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
- Designing Applications for Real World Performance
9. Improving Performance with Caching
- Describe result caching
- 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
- PL/SQL Pragma to mark an item deprecated
11. Profiling and Tracing PL/SQL Code
- What is Tracing and Profiling
- Tracing PL/SQL Execution
- Tracing PL/SQL: Steps
12. Securing application through PL/SQL
- Controlling Access to Program Units
- Managing Access to data using PL/SQL
- Creating Secure Application roles to control access to applications
13. Safeguarding Your Code Against SQL Injection Attacks
- SQL Injection Overview
- Reducing the Attack Surface
- Filtering Input with DBMS_ASSERT
14. Security Features implemented through PL/SQL
- 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
.png)
