Section 1 – Creating PL/SQL

    01- Hands-On 01: Create PL/SQL to populate table using Notepad

    02- Hands-On 02: Create PL/SQL to add department row using procedure builder

    03- Hands-On 03: Create PL/SQL to remove department row

    04- Hands-On 04: Create PL/SQL to concatenate customer’s name

    05- Hands-On 05: Create PL/SQL to return department name

Section 2 – Debugging PL/SQL

    06- Hands-On 06: Debugging PL/SQL

Section 3 – Dependency

    07- Hands-On 07: Granting privileges

    08- Hands-On 08: Managing Objects Dependency

Section 4 – Package

    09- Hands-On 09: Creating Package

Section 5 – Developing and using Database

    10- Hands-On 10: Developing and using Database triggers

In this tutorial, you will see a substantial amount of Hands-On training on how to develop PL/SQL blocks.

Before going through your Hands-On training, lets take note of the following important definitions to remember:

• “CREATE PROCEDURE” privilege gives the user permission to write the program.

• “EXECUTE” privilege gives the user permission to run the program.

• A “Parameter” is a value passed in and out of the program.  Direction should be stated for parameter passing in procedures and functions.   The “IN,” “OUT,” and “IN OUT,” are possibilities for parameter passing directions.

• A named block such as a procedure or a function can have no or many Parameters.

• A Function must always have a return value.  Use “FUNCTION” if you have only one output parameter from your stored procedure.

• In Section 1, you will learn how to create a procedure and function.

• Procedure Builder is an Oracle Back-End tool.  It will be used for writing and debugging both server- and client-side PL/SQL programs.

• Server-side is where your database is located.  If you store your PL/SQL programs in the database server, we say “that it is stored on the server-side.”

• Client-side is your PC.  You access to the database remotely.  If you store your PL/SQL programs in the local machine, we say “that it is stored in the client-side.”

• The “Program unit” editor is used to write a PL/SQL stored procedure for client-side.  You use the compile option to compile and save a stored procedure locally.

• The “Stored Program Unit” editor” is used to write a PL/SQL stored procedure for server-side.  You use the save option to compile and save a stored procedure on the database server.

• The “Breakpoints” in “Procedure Builder” must be used for debugging programs. A breakpoint allows you to stop PL/SQL execution and test the contents of your runtime variables stack. 

• In Section 2, you are going to debug a procedure that calculates the factorial of a number.  This program doesn’t work and your assignment is to debug and fix the program.

• Object permission must be granted to a user directly not via a role if the user wants to pass the permission through its stored procedures to other users.

• Object Dependency is when a PL/SQL block relies on availability of another database object such as a table, procedure, function, or sequence.

• The “DBA_DEPENDENCIES” dictionary view will be used to see all object dependencies on the Oracle database.

• Objects and procedure dependencies are enforced using timestamps in Oracle by default. A timestamp is a character string of the date and time that an object was last modified or compiled.

• In Section 3, you are going to learn how to grant object privileges and experience some object dependencies.

• A package is a collection of many program units into one database object. Packages contain two parts:  a specification and a body.

• Package specification contains program unit definitions, type definitions, exceptions, and constants only.

• The Package body contains all program logic for all program units plus any program logic for private program unit.

• Private program units cannot be called from outside the package body.

• Public program units are those whose specification appear in the package specification and can be called from outside the package body.

• You must first compile package specification before its package body.

• In Section 4, you will experience how to develop, compile and run a package, as well as create Public and Private objects in a package

• A database trigger is a set of PL/SQL statements that execute each time an event such as an update, insert, or delete statement occurs on the database. They are similar to stored PL/SQL statements.  They are stored in the database and attached to a table.

• There are two types of database triggers: statement triggers and row triggers.

• A statement trigger will fire only once for a triggering statement.

• A row trigger fires once for every row affected by a trigger statement.

• Triggers can be set to fire either before or after Oracle processes the triggering insert, update, or delete statement.

• The keywords updating, deleting, or inserting can be used when multiple triggering events are defined.

• Once you create the trigger.  It is enabled and ready to execute. You can enable or disable the trigger.  Remember that No special privileges other than permission to access to the table is needed to run the trigger.

• In Section 5, you will experience how to develop, drop, compile and run a trigger.

Now, you should first read your case study, and try to solve the questions.  Then play the Hands-On training movie until you become familiar with the subject.  For more information about the subject, you are encouraged to read from a wide selection of available books.

Good luck!