Oracle 19c PL/SQL Fundamentals

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.

    Aug 16 2021

    Date: 08/16/2021 - 08/18/2021 (Monday - Wednesday) | 10:00 AM - 6:00 PM (EST)
    Location: ONLINE (Virtual Classroom Live)
    Delivery Format: VIRTUAL CLASSROOM LIVE Request Quote & Enroll

    Success! Your message has been sent to us.
    Error! There was an error sending your message.
    REQUEST MORE INFO:

    Oracle 19c PL/SQL Fundamentals

    August 16 - 18, 2021 | 10:00 AM - 6:00 PM (EST) | Virtual Classroom Live


    How Did You Hear of Global IT Training?

    Join Our Email List?

    Oct 18 2021

    Date: 10/18/2021 - 10/20/2021 (Monday - Wednesday) | 10:00 AM - 6:00 PM (EST)
    Location: ONLINE (Virtual Classroom Live)
    Delivery Format: VIRTUAL CLASSROOM LIVE Request Quote & Enroll

    Success! Your message has been sent to us.
    Error! There was an error sending your message.
    REQUEST MORE INFO:

    Oracle 19c PL/SQL Fundamentals

    October 18 - 20, 2021 | 10:00 AM - 6:00 PM (EST) | Virtual Classroom Live


    How Did You Hear of Global IT Training?

    Join Our Email List?

    Dec 6 2021

    Date: 12/06/2021 - 12/08/2021 (Monday - Wednesday) | 10:00 AM - 6:00 PM (EST)
    Location: ONLINE (Virtual Classroom Live)
    Delivery Format: VIRTUAL CLASSROOM LIVE Request Quote & Enroll

    Success! Your message has been sent to us.
    Error! There was an error sending your message.
    REQUEST MORE INFO:

    Oracle 19c PL/SQL Fundamentals

    December 6 - 8, 2021 | 10:00 AM - 6:00 PM (EST) | Virtual Classroom Live


    How Did You Hear of Global IT Training?

    Join Our Email List?

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

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

SQL DEVELOPER AND PL/SQL

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

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

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 PERCENT ROWTYPE WITH CURSORS
  • THE CURSOR FOR LOOP
  • DBMS OUTPUT


OPTIMIZATION

  • TIMING PL/SQL
  • FOR UPDATE / WHERE CURRENT OF

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

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

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

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

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

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

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

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

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

*Please Note: Course Outline is subject to change without notice. Exact course outline will be provided at time of registration.

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.

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

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.

Ready to Jumpstart Your IT Career?

CONTACT US NOW!