Home | workshop | PL/SQL Stored Program Units

PL/SQL Stored Program Units

By
Font size: Decrease font Enlarge font

This course includes classroom labs for live hands-on training

Course Duration: 3 days

Course Code:

Course Description: This course provides practical experience in developing and writing triggers, functions, procedures and packages. It also introduces some of the Oracle-supplied packages. It will benefit Oracle database administrators and software development personnel who need to write new or maintain existing, PL/SQL triggers, program units and packages.

The delegates will practise:

    * 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

Prerequisites: Attendance on SQL*Plus and PL/SQL

Prerequisite Courses

Oracle SQL
PL/SQL

COURSE MODULES


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

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

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

Subscribe to comments feed Comments (21 posted)

avatar
aislinn 29/01/2014 18:53:17
i must thank you for the efforts you've put in penning this blog. excellent blog post .

www.n8fan.net
avatar
replica watches 16/12/2015 13:52:24
Good information, you shared such a interesting points.
avatar
ysl Replica handbags 13/09/2015 19:05:09
Thailand, cadger from Malaysia, and oxen, deer, calf, goat, and ostrich from all over the world. This bag is said to acquire a two-to-three cat-and-mouse list.
avatar
Ralph Lauren Outlet UK 27/09/2016 13:50:31
Implementing Native Compilation and Compile-time Warnings.
avatar
edupdf.org 23/09/2015 07:32:33
I really enjoyed reading your article. I found this as an informative and interesting post, so i think it is very useful and knowledgeable. I would like to thank you for the effort you have made in writing this article.


<a href="http://www.edupdf.org">edupdf.org</a>
avatar
Cheap Ray Ban 27/05/2017 15:57:16
It will benefit Oracle database administrators and software development personnel who need to write new or maintain existing, PL/SQL triggers, program units and packages.
avatar
ufgop.org 23/09/2015 07:40:30
Love it! Very interesting topics, I hope the incoming comments and suggestion are equally positive. Thank you for sharing this information that is actually helpful.


ufgop.org
<a href="http://www.ufgop.org">ufgop.org</a>
avatar
Software Development Company 28/09/2015 15:57:27
The place, the moment of development and demand of the latest technology can put pressure on developers. Some developers can accept the pressure.
avatar
Anushka Jain 16/10/2015 17:51:22
This article is really fantastic and thanks for sharing the valuable post.
http://www.expert5th.in/packers-and-movers-chennai/ #
Packers and Movers in Chennai
http://www.expert5th.in/packers-and-movers-mumbai/ #
Packers and Movers in Mumbai
http://www.expert5th.in/packers-and-movers-delhi/ #
Packers and Movers in Delhi
http://www.expert5th.in/packers-and-movers-gurgaon/ #
Packers and Movers in Gurgaon
avatar
Packers and Movers 16/10/2015 17:52:07
they have to be handled very efficiently and you sit in the house comfortable and make sure that the packers and moving companies Bangalore Thanks for sharing such a great blog..Keep posting Please visit for More information about -
Packers and Movers India @ http://www.expert5th.in/
http://www.expert5th.in/packers-and-movers-pune/ #
Packers and Movers in Pune
http://www.expert5th.in/packers-and-movers-bangalore/ #
Packers and Movers in Bangalore
http://www.expert5th.in/packers-and-movers-hyderabad/ #
Packers and Movers in Hyderabad
avatar
cheap replica watches uk 10/11/2015 06:02:06
This course provides practical experience in developing and writing triggers, functions, procedures and packages. It also introduces some of the Oracle-supplied packages.
avatar
replica watches 16/12/2015 13:53:16
Good information, you shared such a interesting points.
avatar
mulberry replica 17/12/2015 12:01:47
Special views, and thx for your share,its the info. i'm finding!
avatar
Replica Handbags UK 27/09/2016 14:01:55
Handbags and purses from top bag designers, make sure you are up with the latest UK styles this Season! Free delivery ... best sales, style tips & offers.
1 2 next total: 17 | displaying: 1 - 10

Post your comment

  • Bold
  • Italic
  • Underline
  • Quote

Please enter the code you see in the image:

Captcha
  • Email to a friend Email to a friend
  • Print version Print version
  • Plain text Plain text

Tagged as:

No tags for this article

Rate this article

0