Introduction: Developing Program Unit Introduction

Note on the important definitions to remember  

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

You will:

Create a table named "cust_stat."

Write a stored procedure using notepad to populate customers  _statistics.

Run the stored procedure.

Test the stored procedure.  

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

You will:

Write a procedure to add a record using Procedure Builder.

Use input and output parameters.

Run the stored procedure.

Test the stored procedure.  

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

You will:

Write a PL/SQL procedure to remove a record using Procedure Builder.

Use input and output parameters.

Run the stored procedure.

Test the stored procedure.

Save the procedure  

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

You will:

Write a PL/SQL function to concatenate the customer's last name and first name separated by comma.

Use input parameters.

Run the stored procedure.

Test the stored procedure.

Save the function program.  

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

You will:

Write a PL/SQL function to return a department name.

Use department number as an input parameter.

Run the stored procedure.

Test the stored procedure.

Save the function in the local library,

Save the function in the database server.  

Hands-On 06: Debugging PL/SQL Stored Procedure

You will:

Debugging a PL/SQL using Procedure Builder

Using Breakpoint

Using Stack

Compiling and running debug procedure

Test debug procedure

Introducing TEXT_IO package

PL/SQL Library  

Hands-On 07: Granting object privileges

You will:

Copy, alter, and drop a table.

Create a table.

Create, alter, and drop a view.

Create a private synonym.

Create a public synonym.

Create, alter and drop a user.

Grant object and system privileges.

Revoke object and system privileges.  

Hands-On 08: Managing Objects Dependency

Directory Views

Security

Tracking Dependency  

Hands-On 09: Creating Package

You will:

Create a Package Specification.

Create a Package Body.

Copy and paste a stored program units' header to a Package Specification.

Copy and paste a stored program units to a Package Body.

Compile the Package Specification.

Compile the Package Body.

Run and test the package

Store packages locally or on database server.  

Hands-On 10: Developing and using Database triggers

You will:

Creating Triggers.

Using Triggers.

Dropping Triggers.