Advanced Oracle PL/SQL: Implementation and Advanced Features

Audience

The intended audience for this course is application developers. Required prerequisites are Develop PL/SQL Program Units Introduction to Oracle: SQL and PL/SQL and Introduction to Oracle for Experienced SQL Users. Suggested prerequisites are a thorough knowledge of SQL SQL*Plus and working experience developing applications with PL/SQL.

Objective

• Read and write to operating system text files by using the UTL_FILE package.
• Access BFILEs.
• Implement external routines.
• Publish load and package Java class methods.
• Tune PL/SQL code.
• Trace PL/SQL execution.
• Tune memory and network issues.
• Use the DBMS_SESSION DBMS_JOB DBMS_ALERT and DBMS_LOCK packages.

Topics Include

Unit 1: Advanced Interface Methods

• Match each exception of the UTL_FILE package with its appropriate description.
• Match the subprogram with its description.
• Label subprograms when shown in a PL/SQL block.
• Identify the characteristics of BFILEs.
• Identify guidelines for creating directories.
• Sequence the steps necessary for managing BFILEs.
• Select the correct use of the BFILENAME function.
• Load BFILEs.
• Identify the benefits of external routines.
• Match the uses of external routine components with the corresponding descriptions.
• Sequence the steps involved in an external routine.
• Sequence the steps for creating an Alias library using PL/SQL.
• Publish an external C routine.
• Match the syntax definition with its appropriate description.
• Sequence the steps for Java class methods.
• Sequence the steps for loading a Java class method.
• Publish a Java class method.

Unit 2: Performance and Tuning

• Complete a data manipulation command.
• Identify the benefit of bulk binding.
• Identify the method of rephrasing a conditional statement to efficiently implement a conditional construct.
• Identify a practice that addresses data type and constraint issues.
• Identify a method of efficiently supporting index-by tables in PL/SQL code.
• Identify the advantages of native dynamic SQL over the DBMS_SQL package.
• Identify the implementations of SQL or PL/SQL in a specific operation.
• Sequence the steps involved in tracing PL/SQL execution.
• Enable subprograms for PL/SQL tracing.
• Enable PL/SQL tracing.
• Disable PL/SQL tracing.
• Display PL/SQL trace information.
• Identify the importance of sizing the shared pool.
• Pin specific objects in memory.
• Identify PL/SQL techniques that address network issues.

Unit 3: Advanced Features of Supplied Packages

• Identify the DBMS_SESSION subprograms.
• Change roles.
• Change the NLS setting for a session.
• Reclaim unused memory.
• Identify the DBMS_JOB subprograms.
• Submit jobs.
• Change characteristics of submitted jobs.
• Execute jobs.
• Remove submitted jobs.
• Mark submitted jobs as broken.
• Identify the DBMS_ALERT subprograms.
• Prepare alerts.
• Register alerts.
• Remove alerts.
• Identify the differences between the DBMS_ALERT and DBMS_PIPE packages.
• Identify the features of the DBMS_LOCK package.
• Allocate locks for a session.
• Request locks for a session.
• Convert locks for a session.
• Release locks for a session.
• Suspend a session.

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