Download Full Outline
Course
Oracle 19c PL/SQL Fundamentals
CompTIA Certified Badge
Gain the Core Skills Required to Setup, Run and Manage Databases using PL/SQL and Oracle Database Technology
ID:TTOR12019
Duration:3 Days
Level:Introductory
Format:

What You'll Learn

Overview

CompTIA Authorized Partner Badge

 Oracle 19c PL/SQL Fundamentals is a three-day, hands-on course that introduces Oracle database programming using the PL/SQL programming language. Throughout the course students will explore the core syntax, structure and features of the language. This course will also lay the foundation for the entire Oracle PL/SQL programming series, allowing one to progress from introductory topics to advanced application design and programming and finally onto writing complex high-performance applications. 

 

The course also explores applying the newly learned skills to the development of database applications. Participants will learn how to use database-resident stored program units such as procedures, functions, packages and database triggers. Students will also learn about the latest features in Oracle 19c.  

 

NOTE: This training is NOT Official Oracle University training. This is independent, adjustable content that aligns with current topics, skills and tools that participants need to excel in these areas.   

Expand All

Objectives

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.  

Audience

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). 

Pre-Requisites

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).

Take Before: Attendees should have skills equivalent to those listed in the courses below, or should have attended one of these as a prerequisite: 

Introduction to SQL Programming Basics
Introduction to Writing SQL Queries

Agenda

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

Related Courses

Oracle 19c PL/SQL Fundamentals
Oracle 19c Database Administration II / Oracle 19c DBA II
Oracle 19c Database Multitenant Architecture
Oracle 19c Database Tuning
Oracle 19c Database Backup and Recovery
Introduction to SQL Programming Basics
Introduction to Writing SQL Queries
Advanced SQL Programming

Connect with us

Tailor your learning experience with Trivera Tech. Whether you need a custom course offering or want to schedule a specific date and time for corporate training, we are here to help. Our team works with you to design a solution that fits your organization's unique needs; whether that is enrolling a small team or your entire department. Simply let us know how many participants you'd like to enroll and the skills you want to develop, and we will provide a detailed quote tailored to your request.

Contact Trivera Today to discuss how we can deliver personalized training that equips your team with the critical skills needed to succeed!