Oracle PL/SQL Programming Workshop

User Avatar
Geoffrey Wiland
 4.7/5 (50)
$2,700.00

This class will teach you how to write efficient and scalable PL/SQL programs to create database-intensive PL/SQL applications. You will learn the critical and fundamental aspects of PL/SQL language.

Learning how to create efficient, maintainable, and scalable PL/SQL programs are important objectives of this course. To meet these objectives, the course includes instruction on such things as the proper use of stored procedures and functions, cursors, bind variables, bulk processing, packages, triggers, error handling and utilities. Hands-on workshops constitute approximately 50% of the course.

Enrollment includes Steven Feuerstein’s top rated text book “Oracle PL/SQL Best Practices“.

Hands-on workshops constitute approximately 50% of the class.

For customer-site training, as with all of our courses, this class is highly customizable to your specific training requirements.

Course Features

  • Lectures 0
  • Quizzes 0
  • Duration 3 Days
  • Skill level All levels
  • Language English
  • Students 0
  • Certificate Yes
  • Assessments Yes

Introduction to PL/SQL    

  • What is PL/SQL?
  • Why Use PL/SQL?
  • PL/SQL Program Structure
  • Anonymous Blocks
  • Using SQL Developer
  • Compile Errors
  • Server Output
  • Introduction to Procedures
  • Procedure Compile Errors
  • Procedure Compile Warnings
  • Introduction to Functions
  • Introduction to Packages
  • Querying the Data Dictionary
  • Introduction to Triggers
  • Tools for PL/SQL Development
  • Working in SQL*Plus
  • Workshop

Language Fundamentals

  • PL/SQL Statements
  • PL/SQL Symbols
  • Quoting Mechanism
  • Common PL/SQL Datatypes
  • Declaring Variables
  • Assignment
  • %TYPE Attribute
  • PL/SQL Records
  • %ROWTYPE Attribute
  • Programmer Defined Records
  • Workshop
  • Variable Scope
  • Nested Blocks
  • Functions
  • Regular Expressions
  • IF Statement
  • CASE Statement and Expression
  • Simple CASE
  • Searched CASE
  • Simple Loops
  • Nested Loops
  • Numeric FOR Loop
  • PL/SQL Arrays
  • Simple Array Example
  • Array Methods
  • Using Arrays for Processing Speed
  • Workshop

PL/SQL and SQL, Part I Basics

  • SELECT INTO Statement
  • Implicit Cursor Loops
  • Workshop
  • DML in PL/SQL
  • Cursor Attributes
  • Embedding DDL
  • Workshop

PL/SQL and SQL, Part II Cursors

  • What is a Cursor?
  • Explicit Cursor Processing
  • Cursor Attributes
  • Workshop
  • Dynamic Cursors
  • Dynamic Cursor Example
  • Variable Scope & Cursors
  • Cursor Parameters
  • Cursor Records
  • Cursor FOR LOOP
  • Workshop
  • Referencing the Current Row
  • FOR UPDATE Example
  • Workshop

PL/SQL and SQL, Part III Bulk Processing    

  • Bulk Processing
  • Bulk Collect
  • Bulk Collect: Fetch with Limit
  • Workshop
  • Bulk Delete: FORALL
  • Bulk Insert – FORALL
  • Advanced FORALL: IN INDICIES OF; IN VALUES OF
  • Returning into Arrays
  • Workshop

Stored Procedures    

  • What is a Stored Procedure?
  • CREATE PROCEDURE Syntax
  • Compile Errors
  • Compiler Warnings
  • Optimizing Compiler
  • Native Compilation
  • Conditional Compilation
  • Procedure Signatures
  • Calling Procedures
  • Procedure Synonyms
  • Referencing Parameters by Name
  • Returning Sets: REF CURSOR
  • Dropping Stored Procedures
  • Procedure Dependencies
  • ALTER COMPILE Statement
  • PL/SQL Procedure Privileges
  • Granting Execute Privilege
  • The Data Dictionary
  • Workshop

PL/SQL Functions    

  • What is a Function?
  • CREATE FUNCTION Syntax
  • Calling Functions
  • The Data Dictionary
  • Workshop
  • Table Functions
  • Building a Table Function
  • Using a Table Function
  • Pipelined Functions
  • Pipelined Example
  • Test Data Generator Example
  • Workshop

Error Handling

  • Types of Errors
  • Runtime Errors
  • Exception Concepts
  • Predefined Named Exceptions
  • EXCEPTION Block Syntax
  • Handling Exceptions
  • Workshop
  • Recovering from Errors
  • Logging Exceptions
  • Debugging with FORMAT_ERROR_BACKTRACE
  • RAISE_APPLICATION_ERROR Statement
  • Workshop
  • Raising Exceptions
  • Raising User Exceptions
  • EXCEPTION_INIT Pragma

PL/SQL Packages

  • Concepts
  • Package Benefits
  • Package Contents
  • Package Specification
  • Package Body
  • Package Privileges
  • Calling Packaged Objects
  • Initialization Code
  • Session Variables
  • Package Body Variables
  • Workshop
  • Package Overloading
  • Workshop
  • Compiling Packages
  • DROP PACKAGE Statement
  • Workshop
  • Sample of Oracle Supplied Packages

DML Triggers

  • DML Trigger Concepts
  • Trigger Execution
  • Trigger Ordering
  • CREATE TRIGGER Syntax
  • Row Triggers
  • :OLD and :NEW Reference Variables
  • Trigger Attributes
  • Audit Trigger
  • Derived Value Trigger
  • Trigger Restrictions
  • Security Privileges
  • Workshop
  • Statement Level Triggers
  • Workshop
  • Autonomous Transactions
  • Autonomous Triggers
  • Workshop
  • INSTEAD OF Triggers
  • Workshop

File I/O Using UTL_FILE    

  • UTL_FILE Concepts
  • Setup for UTL_FILE
  • Permissions
  • Opening Files
  • Closing Files
  • Reading Files
  • Writing Files – PUT
  • Writing Files – PUT_LINE
  • Writing Files – NEW_LINE
  • Writing Files – PUTF
  • Workshop

Sending Email with UTL_MAIL

  • Introduction to UTL_MAIL
  • Setup for Emailing from the Database
  • UTL_MAIL Coding
  • Other Oracle Supplied Packages
author avatar
Geoffrey Wiland

    https://skillbuilders.com/our_team/geoffrey-wiland/

    Leave A Review

    Please provide as much detail as you can to justify your rating and to help others.




     5/5

    I liked and enjoyed the whole course. I also learned a lot from it. Geoff did a great job explaining the course material in a clear and concise way.

    Thank you very much Geoff for the knowledge you provided during this course and for your patience!
    amal


    Amal Zerhloul
    December 11, 2020
     5/5



    john.fisher@votec.net
    December 11, 2020
     5/5

    Geoff was very patient and very knowledgeable.

    After going over the lesson and before going into the labs on our own, it would help to do an instructor led example that closely resembles what the lab entails. The course is too fast paced unless you are already a knowledgeable person which is unlikely.

    It would be helpful if we could copy the syntax code shown on the PDF slides
    Liliana Rosete Legorreta
    September 18, 2020
     4.4/5

    The instructor is very knowledgeable and very open to the students suggestions about the training.

    Should have more time to cover the course material.

    Daniyal Ahmed
    September 18, 2020
     4.3/5

    Instructor, Geoff, was good - good pace and clearly understandable.

    Either make more workshops that are 'quicker' and cover less concepts, or include better examples of the workshops in the materials and code before it.

    Meeting software was good
    Matthew Davis
    September 18, 2020
     4.8/5


    I would have some more lab time.

    Link Maynard
    April 24, 2020
     4.5/5

    I thought Geoff was a great instructor. I liked the labs that re-enforced the concepts we learned.

    I would have liked to see some more one-on-one breakouts. I also would have liked if the instructor showed more examples, rather than reading examples from the slide. The labs were sometimes more difficult than the examples provided.

    I liked the hand raising aspect. Everything seemed to work really well for the course being all online.
    Vince Yacano
    April 24, 2020
     4.7/5

    Subject matter is very useful and clear for people just learning PL/SQL.


    DeQuane Wallace
    April 24, 2020
     4.9/5

    Stepping and talking through solutions for workshops


    Libby Rubesa
    April 24, 2020
     4.8/5

    I thought that Geoff was very knowledgeable about the subject matter. He never talked down to any of the students and really took time to go over lab answers and walkthroughs. He also made an effort to really get to know each student.

    Maybe a little more lab time or live examples, and less of the powerpoint slides. But overall the class was very good and learning the basics to PL/SQL

    I thought that the software that was used was very accessible and allowed for smooth for the lessons.
    TJ Colleran
    April 24, 2020
     5/5

    I liked how wide of a base of knowledge we covered. It really felt like I was able to see and understand all of the basics of PL/SQL and how it interacts with Oracle. Geoff was a good teacher and knew what he was talking about.

    Maybe make the Oracle portal program required to be the same, Geoff was using sql developer I believe and I was using Toad and it got confusing every once in a while when things wouldn\'t line up.

    No.
    Jackson Brill
    April 24, 2020
     5/5

    Geoff did a great job of explaining the course material in a clear and concise manner. Having the workshops to do really cemented the material after the lessons.

    I felt the pacing of the course was slightly slow for someone such as myself that is more experienced in coding which made it difficult to pay attention for the extended periods in between workshops.

    All of the online software and delivery techniques used were good. I have no recommendations for further improvements for them.
    Daniel Becker
    April 24, 2020
     5/5

    Geoff was very open to questions and informative, particularly with our business process questions.


    Will Johnson
    January 16, 2020
     4.7/5
    anil edupuganti
    August 7, 2017
     3.6/5
    Eric Davilia
    August 7, 2017
     5/5
    Constance Eberle
    August 7, 2017
     4.4/5
    Jason Bechtel
    August 7, 2017
     4.5/5
    Mahmoud Mahran
    August 7, 2017
     4.8/5
    Madhu Murthy Tumulati Ananda
    August 7, 2017
     4.2/5
    Naveen Gupta
    August 7, 2017
     4.6/5
    Joe Zullich
    August 7, 2017
     5/5
    Sigmund Tanseco
    June 19, 2017
     5/5
    Dan Brennan
    February 27, 2017
     4.5/5

    Geoff is great about answering questions. The materials are great to have.

    I would actively go through examples while typing instead of just going through slides. It should be an exercise similar to the one in the lab. And then have the lab. Then go through the lab correcting errors like we did so we can engage more in the ans
    Rachel Kimmitt
    December 12, 2016
     5/5

    [What did you particularly like about the course?] Being able to work with the course material and ask questions about real world issues we are having.
    Douglas Burkes
    December 12, 2016
     5/5

    I liked doing the workshops. They helped to answer any questions I might of had
    Dan Dailey
    December 12, 2016
     5/5

    Instructor is excellent - we have more united physician team members taking sql and plsql classes in the near future based on past.
    Arleah D'Agostini
    September 12, 2016
     4.9/5

    The instructor was very patient and also very knowledgeable about the subject.
    Kenneth Harper
    September 12, 2016
     4.9/5
    Vitor Oliveira
    April 18, 2016
     5/5

    Instructor was focused an re-iterated key concepts.
    Eric Thon
    February 8, 2016
     5/5

    The instructor tailored the course content to my skill level, while still achieving the course objectives. He also was very generous in rescheduling when I had no access to the internet for several days!

    The online delivery was great, no suggestions for improvement.
    Linda MacMinn
    November 16, 2015
     5/5

    Hands-on examples.

    Specify the target audience better. It was very helpful having coding experience prior to the beginners' course. However, one without prior experience had many difficulties.
    Kostatin Bojovic
    May 26, 2015
     3.7/5

    The book with the course was very helpful
    Ryan Beno
    May 26, 2015
     5/5

    Should be 3 days instead of 2.
    Linda Huack
    May 26, 2015
     3.7/5

    He cover a lot of info in 2 days.
    Guillermo J. Rivera
    May 26, 2015
     4/5

    The instructor was very prepared.
    Marcelino Reyes
    May 26, 2015
     5/5
    Kiran Gudikandula
    May 26, 2015
     3.1/5

    The instructo was nice
    Rachel Sullivan
    May 26, 2015
     4.9/5

    Got a better understanding of Pl/Sql that made reading I've already done, make more sense. I have a better picture of what some of my coding practices should be, especially using packages more!
    Annie Matsudaira
    November 12, 2012
     4.2/5

    the instructor was able to immediately respond to questions and comments while presenting the material

    more exercises that are short and quick to reinforce the various subjects presented.
    katie bourbeau
    November 12, 2012
     4.6/5
    Tim Cassel
    April 2, 2012
     5/5

    Small class size and interaction with others and instructor.

    [No improvements necessary.] Liked the share screen option.
    Stephanie Collins
    April 2, 2012
     4.3/5
    Ken Bates
    April 25, 2011
     4.4/5
    Stephen Moles
    February 7, 2011
     4.9/5

    Instructor, Dan McGhan, helped everyone on the class to set up the lab and help us individually to debug our programming exercises if we needed help. Dan is a great instructor and he answers all students' questions. I like the GoToMeeting setup, too
    Daniel Law
    February 7, 2011
     5/5

    The delivery of audio and video is quite good except just a few instance of choppy video.
    Allen Leung
    February 7, 2011
     4.8/5
    Abe Colpitts
    August 9, 2010
     4.4/5
    navin ikramullah
    August 9, 2010
     4.9/5

    The instructor was very knowledgeable in this area. The use of GoToMeeting made it easy to interact.
    Brandon Austin
    May 24, 2010
     4.9/5

    Instructor was very patient and knowledgeable. Being new to PL/SQL, I was worried about being too slow, but the pace was okay.

    This was the first time I took a course over the Internet with Headphones and a Microphone. It worked out better than I expected.
    Wendy Copeland
    May 24, 2010
    $2,700.00
    Details Price Qty
    06/07 - 06/11show details +$2,700.00 (USD)  

    You May Like

    Copyright © Skillbuilders.com

    Free Online Registration Required

    The tutorial session you want to view requires your registering with us.

    It’s fast and easy, and totally FREE.

    And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

     

    ×

    Message modal