Advanced Oracle PL/SQL: Design Considerations and Object Types

Audience

The intended audience for this course are Application Developers. Required prerequisites would include Develop PL/SQL Program Units; Introduction to Oracle: SQL and PL/SQL; Introduction to Oracle for Experienced SQL Users. Additionally students should have a thorough knowledge of SQL SQL*Plus and working experience developing applications with PL/SQL.

Objective

• Identify the advantages of using subtypes declare a subtype and identify the benefits of using subprograms.
• Declare open fetch and close a cursor variable.
• Identify a parameter passing mode and the benefits of using the NOCOPY hint.
• Identify the DETERMINISTIC function definition and the benefits of using PARALLEL_ENABLE and differentiate definer's rights from invoker's rights.
• Identify the characteristics of the System Global Area and the shared pool.
• Identify the memory related design issue that directly affects the performance of a PL/SQL and reuse packaged memory.
• Identify the data checks performed by Oracle8i to enhance data purity.
• Use overloading to extend the use of a procedure or function name.
• Enhance cursor performance with the use of cursor parameters referencing fetching into a record from a cursor and using the implicit cursor FOR loop.
• Establish a system for standardized exception handling.
• Use the Wrapper utility to protect sensitive code.
• Identify the parts of an object type.
• Create an object type specification and body using the CREATE TYPE statement.
• Write statements that call methods within a PL/SQL block and call constructor methods.
• Create SELECT statements to perform queries against object tables using constructor methods.
• Identify different data dictionary views.
• Identify collections nested tables and VARRAY's.
• Identify the steps involved in the different collection types.
• Identify the rules and methods that apply to collection elements.
• Identify guidelines associated with implementing indexby tables.

Topics Include

Unit 1: PL/SQL Program Design Considerations

• Identify the different subtypes available in PL/SQL.
• Declare a subtype in a PL/SQL block.
• Identify the benefits of using subprograms.
• Identify the characteristics of cursor variables.
• Declare a cursor variable based on a cursor type.
• Open a cursor variable.
• Retrieve the contents of a cursor variable.
• Disable a cursor variable.
• Pass cursor variables as arguments.
• Identify the restrictions that apply to cursor variables.
• Match the PL/SQL passing modes with their corresponding descriptions.
• Identify the benefits of using the NOCOPY hint.
• Identify exception handling characteristics of the NOCOPY hint.
• Identify the restrictions that apply to the NOCOPY hint.
• Identify the guidelines to be followed when implementing the DETERMINISTIC hint.
• Identify the benefits of using the PARALLEL_ENABLE hint.
• Identify the characteristics of the invoker's rights model.
• Identify the characteristics of the definer's rights model.
• Identify the characteristics of an autonomous transaction.

Unit 2: PL/SQL Package Design Considerations

• Identify the characteristics of the System Global Area.
• Identify the items contained within the shared pool.
• Identify the memory related design issue that directly affects the performance of a PL/SQL package.
• Identify how to reuse packaged memory.
• Identify the rules that apply to SERIALLY_REUSABLE.
• Identify the data checks performed by Oracle8i to enhance data purity.
• Identify a particular use for the TRUST keyword.
• Identify when overloading can be used.
• Identify the rules that allow overloading to occur.
• Select the PL/SQL code that demonstrates the overloading function.
• Identify traits associated with explicit cursors.
• Fetch from a cursor into a record.
• Identify the advantages of passing parameters with cursors.
• Identify the reason for referencing an implicit cursor immediately after a SQL statement has been executed.
• Identify characteristics of the cursor FOR loop coding option.
• Explicitly close a cursor.
• Identify the ways to implement standardized exceptions.
• Identify how to improve a standardized error-handling package.
• Identify how standardized constants are used.
• Identify the advantages of using the Wrapper utility.
• Activate the Wrapper utility at the system prompt from within a simulated PL/SQL environment.
• Identify the guidelines for using the Wrapper utility.

Unit 3: Oracle Objects

• Identify the definitions of an object type.
• Label the parts of an object type.
• Create an object type specification using the CREATE TYPE statement.
• Create an object type body using the CREATE TYPE BODY statement.
• Write statements that call methods within a PL/SQL block.
• Call constructor methods.
• Create SELECT statements to perform queries against object tables using constructor methods.
• Create INSERT UPDATE and DELETE statements using constructor methods.
• Declare variables parameters fields or attributes to return values in a SQL statement using REF and DEREF.
• Match data dictionary views of objects and object types to its description.
• Match the data dictionary views used to store information about objects and object types available to developers to its description.

Unit 4: Collections

• Identify the description of a collection.
• Identify the description and use of the nested table collection type.
• Identify the description of VARRAYs.
• Identify the differences between nested tables and VARRAYs.
• Identify the steps in creating the nested table and VARRAY collection types.
• Sequence the steps in declaring nested tables in PL/SQL.
• Identify the use of the NESTED TABLE STORE AS clause.
• Sequence the steps in declaring VARRAYs in PL/SQL.
• Identify the steps in declaring collections as formal parameters of functions and procedures.
• Identify the ways of initializing collections.
• Identify the appropriate collection type for a given condition.
• Identify the rules in referencing and manipulating collection elements.
• Match the collection method to its description.
• Match the exception to the event which triggers the exception.
• Identify the differences between index-by tables and PL/SQL collections.
• Identify guidelines associated with implementing index-by tables.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media