Oracle SQL

Oracle PL/SQL

Oracle Forms

Oracle Reports

Residential Courses

Prices

Location

Oracle PL/SQL: Procedures, Functions and Packages
Computer based training course 

Course ID: 61115

Course Overview

This course is the third in a series of three titles, and discusses the features of stored subprograms and packages. This course explains how to create and execute stored procedures and functions as well as how to create stored packages. This course also discusses built-in server-side packages and their use in debugging stored subprograms. This course covers how to manage stored subprograms and packages. It also introduces learners to database triggers: how to create them and their application to complement the capabilities of the Oracle base product.

Learn To:

• Create subprograms and packages.
• Manage procedures and functions.
• Create triggers.

Content Emphasis

Skills-Based

Audience

Application Developers, Database Administrators, and Technical Support Professionals. Prior to taking this course, students should have knowledge equivalent to the computer courses entitled: Oracle SQL and SQL *Plus: Basic SELECT Statements (course 61110), Oracle SQL and SQL *Plus: DML and DDL (course 61112), Oracle PL/SQL: Basics (course 61113) and Oracle PL/SQL: Procedures, Functions and Packages (course 61114).

Total Learning Time

6 - 8 Hour(s)

Course Contents

Unit 1: Developing Program Units

Duration: 1 - 2 Hour(s)

• Select the processes involved in creating a stored procedure and a stored function using SQL*Plus.
• Identify the effects of storing code separately on the server and on the client.
• Perform the steps required to create a stored procedure using SQL*Plus.
• Perform the steps required to create a stored function using SQL*Plus.
• Match runtime exceptions with their descriptions and the directions for their handling.
• Perform the steps required to create a stored package using SQL*Plus.
• Perform the steps to invoke stored procedures in SQL* Plus using IN and OUT parameters.
• Perform the steps required to invoke a stored function using SQL* Plus.
• Perform the steps required to invoke a stored subprogram with multiple arguments in SQL* Plus.
• Identify the rules for calling user-defined functions in SQL expressions.

Unit 2: Oracle Supplied Packages

Duration: 1 - 2 Hour(s)

• Match the different Oracle supplied packages with their uses.
• Identify correct combinations of DBMS_PIPE subprograms and their uses.
• Identify the uses of the DBMS_DDL package.
• Match the subprograms of the DBMS_JOB package with their uses.
• Match the subprograms of the DBMS_OUTPUT package with their uses.
• Perform the steps to output messages onto the screen using the DBMS_OUTPUT package.
• Identify the uses of dynamic SQL.
• Sequence the steps needed to use the DBMS_SQL package to write dynamic SQL.
• Match the DBMS_SQL subprograms with their descriptions.
• Perform the steps required to write dynamic SQL using EXECUTE IMMEDIATE.
• Identify the distinctive properties of a LOB datatype.
• Identify the features of internal LOBs.
• Identify the features of external LOBs.
• Match the subprograms of the DBMS_LOB package with their uses.
• Identify the features of using SQL to create, insert, update, select, and remove LOBs.

Unit 3: Managing Subprograms and Packages

Duration: 2 Hour(s)

• Identify the steps involved in managing subprograms using SQL*Plus.
• Perform the steps to display details about stored subprograms from the Oracle data dictionary.
• Perform the steps required to display compile time errors stored in the Oracle data dictionary.
• Perform the steps required to grant privileges on a stored subprogram using SQL*Plus.
• Identify the correct combination of different types of dependencies and their description.
• Perform the steps required to display dependencies stored in the Oracle data dictionary.
• Identify the situations in which a dependent subprogram can be recompiled.
• Identify how the Oracle server remote dependency mechanism works.
• Match the different tasks involved in managing packages with the methods of implementing them.
• Identify the features of package dependency.

Unit 4: Developing Database Triggers

Duration: 2 Hour(s)

• Match the sections of a database trigger with their descriptions.
• Perform the steps required to create statement level and row level database triggers.
• Perform the steps required to create an INSTEAD_OF database trigger.
• Perform the steps required to create a schema level trigger.
• Perform the steps required to create a BEFORE and an AFTER trigger.
• Identify the rules to be followed while reading or writing data using triggers.
• Match the components of the Procedure Builder Trigger Editor with their uses.
• Match the tasks performed in an Oracle database with the role triggers have in achieving them.
• Perform the steps required to manage database triggers.

Learning Object Delivery Component(s)

• Oracle PL/SQL: Database Programming

Anglesey has over 125 miles of coastline designated "areas of Natural Outstanding Beauty".

Tyn Y Felin is a mere five minutes away from the A55 North Wales Coast Expressway and is a perfectly situated coastal retreat on the serene and beautiful Isle of Anglesey.