Oracle 19c PL/SQL Fundamentals (with SQL Developer and SQL*Plus)

Quickstart your core PL/SQL programming, SQL Developer and SQL*Plus skills to work with Oracle databases. Also available for Oracle 12c and 18c.

TTORPLSQS3

Introductory

3 Days

Course Overview

Oracle 19C PL/SQL Fundamentals provides a complete, hands-on, comprehensive introduction to PL/SQL including the use of both SQL Developer and SQL*Plus.

Course Objectives

TBD

Course Prerequisites

This course is appropriate for anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality. That would include end users, business analysts, application developers and database administrators.

 

Attendees should have basic computer skills. A basic knowledge of databases is helpful, but not required.

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

 

 

  1. PL/SQL PROGRAM STRUCTURE
  • PL/SQL VS. SQL
  • PL/SQL ENGINES AVAILABLE
  • ANONYMOUS PL/SQL BLOCK STRUCTURE
  • OBJECT NAMING RULES           
  • VARIABLE DECLARATIONS
  • AVAILABLE DATATYPES
    • Scalar Datatypes
    • Using Extended Datatypes
    • Object Types
  • EXECUTABLE STATEMENTS
  • EXPRESSIONS
  • BLOCK LABELING
  • VARIABLE SCOPING RULES
  • COMMENTS IN PROGRAMS AND SCRIPTS
  • BASIC CODING STANDARDS
  1. PL/SQL FLOW CONTROL
  • CONDITIONAL CONTROL
  • COMPARISON OPERATORS
  • LOGICAL OPERATORS
    • Truth Tables
  • REPETITION CONTROL              
    • The Simple Loop
    • WHILE Loop
    • FOR Loop
    • CONTINUE Statements In Loops
    • Step Loops
  • THE GOTO STATEMENT
  • CASE EXPRESSIONS / STATEMENTS
  • BIND VARIABLES
  • SUBSTITUTION VARIABLES

 

  1. SQL DEVELOPER AND PL/SQL
  • SQL DEVELOPER AND PL/SQL
  • CREATING AND EXECUTING SCRIPTS

 

  1. SELECT INTO
  • SELECTING SINGLE ROWS OF DATA
  • ANCHORING VARIABLES TO DATATYPES
  • DML IN PL/SQL
  • RETURNING … INTO
  • SEQUENCES IN PL/SQL
  • TRANSACTION CONTROL IN PL/SQL
  • AUTONOMOUS TRANSACTIONS

 

  1. THE PL/SQL CURSOR
  • DECLARING EXPLICIT CURSORS
  • OPENING AND CLOSING EXPLICIT CURSORS
  • USING EXPLICIT CURSORS TO RETRIEVE VALUES
  • EXPLICIT CURSOR ATTRIBUTES
  • USING A LOOP WITH AN EXPLICIT CURSOR
  • USING %ROWTYPE WITH CURSORS
  • THE CURSOR FOR LOOP
  • DBMS_OUTPUT
  • LAB 5:  THE PL/SQL CURSOR
  • LAB 5 SOLUTIONS:  THE PL/SQL CURSOR              

 

              

  1. OPTIMIZATION          
  • TIMING PL/SQL          
  • FOR UPDATE / WHERE CURRENT OF

 

  1. PL/SQL EXCEPTION HANDLING
  • THE EXCEPTION SECTION
  • ORACLE NAMED EXCEPTIONS
  • PRAGMA EXCEPTION_INIT
  • USER DEFINED EXCEPTIONS
    • The Scope Of User-Defined Exceptions
  • RAISING NAMED EXCEPTIONS
  • EXCEPTION PROPAGATION
  • RAISING AN EXCEPTION AGAIN
  • LIFE AFTER AN EXCEPTION
  • WHEN OTHERS
  • TAKING YOUR BALL AND GOING HOME
  • DBMS_ERRLOG

 

  1. STORED PROCEDURES
  • PROCEDURES
  • BENEFITS OF STORED PROCEDURES
    • Database Security
    • Performance
    • Productivity
    • Portability
  • PARAMETERS AND STORED PROCEDURES
    • Parameter Notation
  • STORED OBJECT CREATION
    • Syntax For Creating A Procedure
  • COMPILATION ERRORS
  • VIEWING COMPILED CODE
  • DROPPING A PROCEDURE
  • THE ALTER COMMAND AND STORED PROCEDURES

 

  1. CREATING FUNCTIONS IN PL/SQL
  • FUNCTIONS
  • PURITY LEVELS
  • USING WHITE LISTS
  • OPTIMIZATIONS
  • PARALLEL_ENABLE
  • DETERMINISTIC FUNCTIONS
  • PL/SQL RESULT CACHE
  • NOCOPY
  • DBMS_OUTPUT IN FUNCTIONS
  • USING THE WITH CLAUSE FOR FUNCTIONS
  • PRAGMA UDF
  • PRAGMA INLINE
  • USING SQL DEVELOPER WITH STORED PROCEDURES
  • DEBUGGING

                             

  1. PACKAGES
  • CREATING PACKAGES
  • PACKAGE BENEFITS
    • Security
    • Persistent State  
    • I/O Efficiency
  • A SIMPLE PACKAGE
  • OVERLOADING
  • BODILESS PACKAGES
  • SOURCE CODE ENCRYPTION
  • CREATING PACKAGES FROM PROCEDURES AND FUNCTIONS

 

  1. CREATING DML TRIGGERS
  • DML TRIGGERS
  • DML TRIGGER STRUCTURE
  • CONDITIONAL TRIGGERING PREDICATES              
  • TRIGGERS FOR BUSINESS RULES ENFORCEMENT
  • MUTATING AND CONSTRAINING TABLES
  • COMPOUND TRIGGERS            
  • CONTROLLING FIRING ORDER
  • DDL FOR TRIGGERS
  • VIEWING TRIGGER SOURCE
  • INSTEAD OF TRIGGERS
  •        
  1. ADVANCED CONCEPTS
  • EMBEDDED PROCEDURES
  • THE OPTIMIZING COMPILER
  • PL/SQL COMPILER WARNINGS
  • COMPILING FOR DEBUGGING
  • CONDITIONAL COMPILATION / INQUIRY DIRECTIVES
    • Error Directives   
    • Inquiry Directives
    • Using Static Constants
  • DBMS_DB_VERSION
  • NATIVE COMPILATION
    • Recompiling All Database Objects

 

  1. FILE OPERATIONS
  • MOVING FILES BETWEEN DATABASES
  • DIRECTORY ACCESS
  • FILE MANIPULATION
    • FCLOSE Procedure
    • FCLOSE_ALL Procedure
    • FCOPY Procedure
    • FFLUSH Procedure
    • FGETATTR Procedure
    • FGETPOS Function
    • FOPEN Function
    • FREMOVE Procedure
    • FRENAME Procedure
    • FSEEK Procedure
    • GET_LINE Procedure
    • GET_RAW Procedure
    • IS_OPEN Function
    • NEW_LINE Procedure
    • PUT Procedure
    • PUT_LINE Procedure
    • PUTF Procedure  
    • PUT_RAW Procedure

 

  1. COLLECTIONS
  • DEFINING RECORDS
  • COLLECTIONS
  • Associative Arrays
  • Nested Tables
    • VARRAYs / VARYING ARRAYs
    • Assignments
    • Comparing Collections
  •  
  • COLLECTION METHODS
    • EXISTS
    • FIRST
    • LAST
    • COUNT
    • LIMIT
    • PRIOR
    • NEXT
    • DELETE
    • TRIM
  • SET THEORY AND NESTED TABLES

 

  1. BULK OPERATIONS
  • BULK BINDING
  • FORALL
  • SQL%BULK_ROWCOUNT
  • SAVE EXCEPTIONS / SQL%BULK_EXCEPTIONS
  • BULK COLLECT
    • The LIMIT Clause
    • FORALL And The INDICES OF Clause
    • FORALL And VALUES OF
  • PIPELINED TABLE FUNCTIONS
  • MULTIDIMENSIONAL COLLECTIONS

Course Materials

Each student will receive a Student Guide with course notes, code samples, software tutorials, diagrams and related reference materials and links (as applicable). Our courses also include step by step hands-on lab instructions and and solutions, clearly illustrated for users to complete hands-on work in class, and to revisit to review or refresh skills at any time. Students will also receive the project files (or code, if applicable) and solutions required for the hands-on work.

Raise the bar for advancing technology skills

Attend a Class!

Live scheduled classes are listed below or browse our full course catalog anytime

Special Offers

We regulary offer discounts for individuals, groups and corporate teams. Contact us

Custom Team Training

Check out custom training solutions planned around your unique needs and skills.

EveryCourse Extras

Exclusive materials, ongoing support and a free live course refresh with every class.

Attend a Course

Please see the current upcoming available open enrollment course dates posted below. Please feel free to Register Online below, or call 844-475-4559 toll free to connect with our Registrar for assistance. If you need additional date options, please contact us for scheduling.

Course Title Days Date Time Price
Oracle 19c PL/SQL Fundamentals (with SQL Developer and SQL*Plus) 3 Days Aug 16 to Aug 18 10:00 AM to 06:00 PM EST $2,395.00 Enroll
Oracle 19c PL/SQL Fundamentals (with SQL Developer and SQL*Plus) 3 Days Oct 18 to Oct 20 10:00 AM to 06:00 PM EST $2,395.00 Enroll
Oracle 19c PL/SQL Fundamentals (with SQL Developer and SQL*Plus) 3 Days Dec 6 to Dec 8 10:00 AM to 06:00 PM EST $2,395.00 Enroll

New Site, BIG Savings!
We're celebrating the launch of our lonnngggg awaited new site with with *50% off all 2021 Public Classes* booked by April 30!  Check out our Current Offers for Individuals, Teams and Organizations to Learn for Less!

See our latest Offers and Promotions

Learn. Explore. Advance!

Extend your training investment! Recorded sessions, free re-sits and after course support included with Every Course
Trivera MiniCamps
Gain the skills you need with less time in the classroom with our short course, live-online hands-on events
Trivera QuickSkills: Free Courses and Webinars
Training on us! Keep your skills current with free live events, courses & webinars
Trivera AfterCourse: Coaching and Support
Expert level after-training support to help organizations put new training skills into practice on the job

The voices of our customers speak volumes

Special Offers
Limited Offer for most courses.

SAVE 50%

Learn More