Working within in a hands-on learning environment, guided by our expert team, attendees will develop a practical approach to Oracle Database Technology. Throughout the course participants will explore: 

• Identify the programming extensions that PL/SQL provides to SQL
• Write PL/SQL code to interface with the database
• Design PL/SQL anonymous blocks that execute efficiently
• Use PL/SQL programming constructs and conditional control statements
• Handle runtime errors
• Describe stored procedures and functions

Need different skills or topics? If your team requires different topics or tools, additional skills or custom approach, this course may be further adjusted to accommodate. We offer additional Oracle, database, scripting, administration, programming, security and other related topics that may be blended with this course for a track that best suits your needs.  

This course is geared for anyone needing to interface with an Oracle database such as end users, business analysts, application developers and database administrators / DBAs. Attendees should have incoming basic SQL experience, as well as an understanding of databases (required to manipulate it with PL/SQL). 

Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We'll work with you to tune this course and level of coverage to target the skills you need most. Topics, agenda and labs are subject to change, and may adjust during live delivery based on audience interests, skill-level and participation. 

1. Getting Started

• Quick Overview of Oracle Database 19c
• Available PL/SQL development environments
• Oracle documentation and additional resources

2. Introduction to PL/SQL

• Understanding the benefits and structure of PL/SQL
• Understanding PL/SQL blocks
• Generating output messages in PL/SQL

3. Declaring PL/SQL Variables

• Introducing variables
• Handling variables of different types
• Using the %TYPE attribute and composite data types
• Using bind variables

4. Writing Executable Statements

• Writing executable statements in a PL/SQL block
• Writing nested blocks
• Using operators and developing readable code

5. Using SQL Statements Within a PL/SQL Block

• SQL statements in PL/SQL blocks
• Manipulating data with PL/SQL
• Introducing SQL cursors

6. Writing Control Structures

• Using IF statements
• Using CASE statements and CASE expressions
• Constructing and identifying loop statements

7. Working with Composite Data Types

• Understanding composite data types
• Using PL/SQL records
• Manipulating data with PL/SQL records
• Advantages of the %ROWTYPE attribute
• Using PL/SQL collections
• Examining associative arrays
• Introducing nested tables
• Introducing VARRAY

8. Using Explicit Cursors

• What are explicit cursors?
• Using explicit cursors
• Using cursors with parameters
• Locking rows and referencing the current row

9. Handling Exceptions

• Define PL/SQL exceptions
• Recognize unhandled exceptions
• List and use different types of PL/SQL exception handlers
• Trap unanticipated errors
• Describe the effect of exception propagation in nested blocks
• Customize PL/SQL exception messages

10. Introducing Stored Procedures and Functions

• Differentiate between anonymous blocks and subprograms
• Create a simple procedure and invoke it from an anonymous block
• Create a simple function
• Create a simple function that accepts a parameter
• Differentiate between procedures and functions

11. Using SQL*Plus

• Log in to SQL*Plus
• Edit SQL commands
• Format the output by using SQL*Plus commands
• Interact with script files

12. Using SQL Developer

• Identify the menu items of Oracle SQL Developer
• Create a database connection
• Manage database objects
• Use SQL Worksheet
• Save and run SQL scripts
• Create and save reports

13. Commonly Used SQL Commands

• Execute a basic SELECT statement
• Create, alter, and drop a table
• Insert, update, and delete rows
• Perform join operations

14. REF Cursors

• Cursor Variables
• Defining REF CURSOR Types
• Using the OPEN-FOR, FETCH, and CLOSE Statements