We use cookies to offer you a better experience and to analyse how visitors use our site, you may consent to our cookies by continuing to use our site, or you may view more information and choose to reject cookies by clicking here. Thank you

Contact us online or call us on 0191 377 8377

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields

PDF
Course Oracle PL-SQL Stored Program Units
  • Course Duration3 Days
  • Course Options
    Public Scheduled
    Public Scheduled
    Fixed content, fixed date, fixed location, fixed length, multiple organisations - All you need to do is choose which course you would like to attend and contact us to book!
    Private
    Private
    Flexible content, flexible date, flexible location, variable length, private to your organisation - Courses can be held at one of our training venues or at another location convenient to you. Training can be set over a number of days or weeks to allow for practice time between training sessions.

Overview
The Oracle PL/SQL Stored Program Units course provides practical experience in developing and writing triggers, functions, procedures and packages. It also introduces some of the Oracle-supplied packages.
Prerequisites
    • Prerequisite Courses : Oracle SQL, Oracle PL/SQL
    • Practical experience of Oracle SQL*Plus or SQL Developer and a good working knowledge of the fundamentals of the Oracle PL/SQL programming language is required. This can be gained by attendance on the pre-requisite courses.
Objectives
    • To provide the skills needed to develop, write and maintain triggers, stored program units and packages.
Target Audience
    • Oracle database administrators and software development personnel who need to write new or maintain existing, PL/SQL triggers, program units and packages.

      Practical experience of Oracle SQL and SQL*Plus or SQL Developer and a good working knowledge of the fundamentals of the Oracle PL/SQL programming language are required.
Skills Gained
    • Implementing various types of trigger
    • Creating, managing and invoking stored procedures
    • Creating, managing and executing stored functions
    • Developing and using packages
    • Using package features and constructs effectively
    • Wrapping packages
    • Using Cursor Variables
    • Using records and tables or associative arrays in packages
    • Implementing Bulk Binding and Bulk Collecting techniques
    • Managing program unit dependencies
    • Implementing Native Compilation and Compile-time Warnings
    • Implementing Native Dynamic SQL
    • Using a variety of Oracle-supplied packages
Course Content
    • DAY 1

    • Course Introduction
      Administration and Course Materials
      Course Structure and Agenda
      Delegate and Trainer Introductions
    • TRIGGERS
      DML Triggers
      The CREATE TRIGGER Statement
      Writing Trigger Code
      INSTEAD OF Triggers
      Calling Procedures from Triggers
      Coding Restrictions
      System Event and DDL Triggers
      Attribute Functions
      Compound Triggers
      Create Trigger Follows Clause
      Managing Triggers
      Privileges Required for Triggers
      Dictionary Information Concerning Triggers
    • PROCEDURES
      What is a Procedure?
      The CREATE PROCEDURE Statement
      Procedure Parameters
      Invoking Procedures
      Local Subprograms
      Named Association Parameter Passing
      Definer's Rights and Invoker's Rights
      Autonomous Transactions
      Managing Procedures
      Privileges Required for Procedures
      Dictionary Information Concerning Procedures
      The Call Statement
    • FUNCTIONS
      What is a Function?
      The CREATE FUNCTION Statement
      Executing Functions
      Invoker's Rights
      Autonomous Transactions
      DBMS_OUTPUT
      Using Functions in SQL Statements
      Deterministic and Parallel-Enabled Functions
      Function Result Cache
      Managing Functions
      Privileges Required for Functions
      Dictionary Information Concerning Functions
    • DAY 2

    • PACKAGES
      What is a Package?
      Public and Private Components
      Creating a Package
      Example Package
      Persistent States
      One-time-only Procedures
      Overloading
      Purity Level Checking
      Forward Declarations
      Wrapping Packages
      Managing Packages
      Privileges Required for Packages
      Dictionary Information Concerning Packages
    • USING PL/SQL RECORDS AND TABLES IN PACKAGES
      Overview of PL/SQL Records, Index-by Tables and Associative Arrays
      Using PL/SQL Records and Tables in Packages
      Table Built-in Functions
      The NOCOPY Hint
      Bulk Collection
      Bulk Binding DML Statements
    • CURSOR VARIABLES (REF CURSORS)
      Declare Cursor Variables
      Use Cursor Variables
      Open and Close Cursor Variables
      Fetch Rows
      Cursor Variable Attributes
      Pass Cursor Variables as Parameters
      Refcursor Datatype
    • MANAGING DEPENDENCIES
      Dependent and Referenced Objects
      Invalidation and Recompilation
      Local and Remote Dependencies
      Recompilation Considerations
    • DAY 3
    • NATIVE COMPILATION AND COMPILE-TIME WARNINGS
      Introduction
      Native Compilation
      Automatic Recompilation
      Automatic Program Sublining
      WHEN OTHERS ...THEN NULL
      Data Dictionary Information
      Compiler Warning Categories
      Using the DBMS_WARNING Package
    • ORACLE-SUPPLIED PACKAGES
      Overview of Oracle-Supplied Packages
      Using the DBMS_SQL Package
      Using Native Dynamic SQL
      The DBMS_METADATA Package
      The UTL_MAIL Package
      The DBMS_APPLICATION_INFO Package
      The DBMS_UTILITY Package
      Scheduling Jobs using the DBMS_SCHEDULER Package
    • Note
      The 3 Day Stored Program Units course can be combined with the 2 Day Oracle PL/SQL course, this is equivalent to attending the 5 Day Oracle Program with PL/SQL course.
What's included
    • For courses held on our premises, complimentary tea, coffee and biscuits are available. Lunch is not provided, however, there is a wide variety of eateries within a short walk of the centre.
    • Comprehensive course materials and exercises

 

Course Dates Please contact us to discuss dates for this course, thank you

IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk

Printed: 21 Nov 19

 

 

 

Contact us

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields