Oracle Database 10g: PL/SQL Fundamentals Self-Study Course

Oracle University Self-Study CD Courses are a great accompaniment and reliable alternative to our inClass courses, and provide an excellent ongoing resource for reviewing key concepts and practicing skills.

In this course, the learner will get familiar with the procedural language to access Oracle database. The learner will learn how to write and use anonymous PL/SQL blocks, program constructs, simple and composite PL/SQL data types, programming guidelines, and accessing and manipulating data using PL/SQL. Additionally, the learner will also learn how to write and use PL/SQL program units, such as stored functions, stored procedures, and packages along with some standard Oracle supplied packages and exception handling techniques in PL/SQL.

Learn to:

• Identify features of PL/SQL
• Identify benefits of PL/SQL
• Identify features of PL/SQL blocks
• Identify features of PL/SQL blocks within a subprogram
• Identify features of triggers

Audience

• Database Designers
• Forms Developer
• Technical Consultant
• Business Intelligence Developer
• PL/SQL Developer
• Portal Developer

Course Topics Programming with PL/SQL

• Identify features of PL/SQL
• Identify benefits of PL/SQL
• Identify features of PL/SQL blocks
• Match different PL/SQL program constructs with their features
• Identify features of the iSQL* Plus programming environment
• Identify features of PL/SQL variables
• Identify guidelines for declaring PL/SQL variables
• Identify features of various PL/SQL data types

PL/SQL Blocks

• Identify features of PL/SQL blocks within a subprogram
• Match types of PL/SQL named blocks with their descriptions
• Match types of PL/SQL environments with their descriptions
• Identify the syntax for creating a stored procedure
• Identify features of the parameters used in stored procedures
• Identify the syntax for passing parameters to a stored procedure
• Identify tasks involved in using stored procedures
• Identify the syntax for creating a PL/SQL function

PL/SQL Packages

• Identify features of PL/SQL packages
• Sequence steps for developing PL/SQL packages
• Identify the syntax for creating a PL/SQL package
• Identify tasks involved in managing PL/SQL packages
• Identify features of the overloaded subprograms contained in PL/SQL packages
• Identify features of forward referencing in PL/SQL packages
• Identify features of the PL/SQL package state
• Identify features of the PL/SQL wrapper utility

Triggers

• Identify features of triggers
• Identify the code to create a Data Manipulation Language (DML) trigger
• Identify the code to create a Data Manipulation Language (DML) row trigger
• Identify the code to create an INSTEAD OF trigger
• Match the options for managing triggers with their descriptions
• Identify guidelines for testing triggers
• Identify the benefits of triggers in different business application scenarios
• Identify the syntax for creating triggers for DDL and system events

Dynamic SQL Statements

• Match the stages in the execution of SQL statements with their descriptions
• Identify features of dynamic SQL
• Identify the features of the DBMS_SQL package
• Identify features of the subprograms in the DBMS_METADATA package
• Identify the features of Large Objects (LOBs)
• Identify features of internal Large Objects (LOBs)
• Identify features of Binary Files (BFILEs)
• Identify tasks involved in managing Binary Files (BFILEs)

Large Object (LOB) data type

• Identify features of the DBMS_LOB package
• Identify the code to work with Large Object (LOB) values
• Identify methods of selecting Character Large Object (CLOB) values
• Identify features of temporary Large Objects (LOBs)

PL/SQL Design Considerations

• Identify guidelines for standardizing constants and exceptions
• Identify benefits of using local subprograms
• Sort the characteristics of the rights models according to their category
• Identify features of autonomous transactions
• Identify features of bulk binding
• Identify features of dependencies
• Identify tasks you need to perform for displaying dependencies
• Identify features of local dependencies. Identify features of remote dependencies

Compilation Tools and Options

• Identify features of native compilation
• Identify settings to be specified for native compilation
• Sequence the steps to enable native compilation of PL/SQL code
• Identify features of compiler warnings
• Identify features of the PLSQL_WARNINGS parameter
• Identify features of the DBMS_WARNINGS package
• Identify features of the NOCOPY and PARALLEL_ENABLE hints

Course Objectives

• Create, execute, and maintain procedures, functions, packages, and triggers
• Manage dependencies
• Manipulate large objects
• Conditionally control code flow (loops, control structures, and explicit cursors)
• Handle runtime errors
• Manage program constructs
• Describe the features and syntax of PL/SQL