Oracle Database 11g: Advanced PL/SQL
Duration : 3 Days (24 Hours)
Overview
Course Details
Prerequisites
Oracle Database 11g: Advanced PL/SQL Course Overview:
The Oracle Database 11g: Advanced PL/SQL training and certification course provides a comprehensive understanding of advanced PL/SQL features, enabling participants to design and optimize PL/SQL code effectively. The training covers how PL/SQL interfaces with the database and other applications in the most efficient manner, ensuring optimal performance and reliability. By the end of the course, learners will be equipped with the skills to develop robust and high-performance PL/SQL solutions for their database and application needs.
Intended Audience:
- Database Administrator
- Developer
Learning Objectives of Oracle Database 11g: Advanced PL/SQL:
- 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
- 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
Module 1: Introduction
- Course objectives
- Course agenda
- Tables and data used for this course
- Overview of the development environments: SQL Developer, SQL Plus
Module 2: PL/SQL Programming Concepts Review
- Identify PL/SQL block structure
- Create procedures
- Create functions
- List restrictions and guidelines on calling functions from SQL expressions
- Create packages
- Review of implicit and explicit cursors
- List exception syntax
- Identify the Oracle supplied packages
Module 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
- Cursor variables
Module 4: Using Collections
- Overview of collections
- Use Associative arrays
- Use Nested tables
- Use VARRAYs
- Compare nested tables and VARRAYs
- Write PL/SQL programs that use collections
- Use Collections effectively
Module 5: Manipulating Large Objects
- Describe a LOB objec
- Use BFILEs
- Use DBMS_LOB.READ and DBMS_LOB.WRITE to manipulate LOBs
- Create a temporary LOB programmatically with the DBMS_LOB package
- Introduction to SecureFile LOBs
- Use SecureFile LOBs to store documents
- Convert BasicFile LOBs to SecureFile LOB format
- Enable reduplication and compression
Module 6: Using Advanced Interface Methods
- Calling External Procedures from PL/SQL
- Benefits of External Procedures
- C advanced interface methods
- Java advanced interface methods
Module 7: Performance and Tuning
- Understand and influence the compiler
- Tune PL/SQL code
- Enable intra unit inlining
- Identify and tune memory issues
- Recognize network issues
Module 8: Improving Performance with Caching
- Describe result caching
- Use SQL query result cache
- PL/SQL function cache
- Review PL/SQL function cache considerations
Module 9: Analyzing PL/SQL Code
- Finding Coding Information
- Using DBMS_DESCRIBE
- Using ALL_ARGUMENTS
- Using DBMS_UTILITY.FORMAT_CALL_STACK
- Collecting PL/Scope Data
- The USER/ALL/DBA_IDENTIFIERS Catalog View
- DBMS_METADATA Package
Module 10: Profiling and Tracing PL/SQL Code
- Tracing PL/SQL Execution
- Tracing PL/SQL: Steps
Module 11: 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
Module 12: Safeguarding Your Code Against SQL Injection Attacks
- SQL Injection Overview
- Reducing the Attack Surface
- Avoiding Dynamic SQL
- Using Bind Arguments
- Filtering Input with DBMS_ASSERT
- Designing Code Immune to SQL Injections
- Testing Code for SQL Injection Flaws
Oracle Database 11g: Advanced PL/SQL Course Prerequisites:
- Fundamental Knowledge of SQL
- PL/SQL Programming experience
Discover the perfect fit for your learning journey
Choose Learning Modality
Live Online
- Convenience
- Cost-effective
- Self-paced learning
- Scalability
Classroom
- Interaction and collaboration
- Networking opportunities
- Real-time feedback
- Personal attention
Onsite
- Familiar environment
- Confidentiality
- Team building
- Immediate application
Training Exclusives
This course comes with following benefits:
- Practice Labs.
- Get Trained by Certified Trainers.
- Access to the recordings of your class sessions for 90 days.
- Digital courseware
- Experience 24*7 learner support.
Got more questions? We’re all ears and ready to assist!
Request More Details
