Oracle Database 19c PL and Advanced SQL

• Basic knowledge of SQL and relational database concepts
• Familiarity with Oracle Database 19c environment

Audience

• Database administrators
• Database developers
• SQL programmers
• IT professionals

Oracle Database 19c PL and Advanced SQL refers to a set of tools, techniques, and programming practices used within the Oracle Database 19c environment to perform advanced data manipulation and database management.

This instructor-led, live training (online or onsite) is aimed at intermediate-level database administrators who wish to have an in-depth understanding of Oracle Database 19c PL/SQL programming and advanced SQL techniques.

By the end of this training, participants will be able to:

• Understand the fundamentals and advanced features of PL/SQL.
• Develop advanced SQL queries to retrieve and manipulate data efficiently.
• Implement PL/SQL programming constructs to manage data and database operations.
• Optimize SQL queries for better performance.
• Use advanced PL/SQL features like collections, bulk processing, and error handling.
• Learn to debug and manage PL/SQL programs effectively.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Introduction to Oracle Database 19c

• Overview of Oracle Database 19c
• Installation and configuration
• Oracle database architecture
• Tools for database management

SQL Review and Advanced Concepts

• Basic SQL review
  • Select, insert, update, delete
  • Joins, subqueries, and set operations
• Advanced SQL concepts
  • Analytical functions
  • Hierarchical queries
  • Advanced grouping and aggregation

Introduction to PL/SQL

• Overview of PL/SQL
• PL/SQL block structure
• Variables, constants, and data types
• Control structures: if, loop, case

PL/SQL Program Units

• Procedures and functions
• Packages and package bodies
• Triggers and trigger management
• Autonomous transactions

Advanced PL/SQL Features

• Collections: associative arrays, nested tables, VARRAYs
• Bulk processing with BULK COLLECT and FORALL
• dynamic SQL with EXECUTE IMMEDIATE
• PL/SQL records and composite data types

Error Handling and Debugging

• Exception handling: Predefined and user-defined exceptions
• PL/SQL debugging techniques
• Using DBMS_OUTPUT and DBMS_DEBUG

Performance Tuning and Optimization

• SQL query optimization techniques
• Understanding execution plans
• Indexing strategies
• Using hints for query optimization

PL/SQL Best Practices

• Writing efficient PL/SQL code
• Coding standards and conventions
• Security considerations in PL/SQL
• Managing dependencies and version control

Advanced SQL Topics

• Advanced joins and subqueries
• Recursive queries
• Using regular expressions in SQL
• Implementing full-text search

Summary and Next Steps