TUSC Services

E-Business Suite Business Intelligence Custom Development Database Services Managed (DBA) Services Training/Mentoring Oracle Licensing

PL/SQL Fundamentals II

 Click here

Course Overview

This course introduces students to the advanced features of the PL/SQL programming language in the Oracle database environment. The course requires the students to interact with the Oracle development environment and complete challenging hands-on exercises.

This course focuses on core language topics, emphasizing cursor usage and defining and implementing PL/SQL records and collections. Students also take an in-depth look at packages, subprograms and transaction processing concepts.

Class discussions are based on real-world examples to illustrate how advanced coding techniques are used to in the field. Throughout the course, students are instructed on how to apply the best practices for developing PL/SQL applications.

Course Goals

• To assist students with developing the programming skills necessary to design efficient PL/SQL packages and program units.
• To provide instruction on finding program ambiguities, testing and tracing code execution.
• To complete challenging hands-on exercises in an interactive, instructor-led environment.

Prerequisites

• Introduction to PL/SQL course

Who Should Attend?

• Technical Consultants
• Database Designers
• PL/SQL Developers

Course Materials

• Student Handbook
• PL/SQL Reference Book

Duration

• Two (2) days

Course Outline

• Introduction
  • PL/SQL Block Structure
  • Procedures
  • Functions
  • PL/SQL Packages
  • Cursors
  • Exceptions
  • Dependencies
  • Oracle-Supplied Packages
• Design Considerations
  • Standardizing Code
  • Local Subprograms
  • Invoker's Rights
  • Autonomous Transactions
  • RETURNING Clause
  • Bulk Binding
  • The NOCOPY Hint
  • PARALLEL_ENABLE Hint
• Using Cursors
  • Cursor Guidelines
  • Cursor Variables
  • Passing Cursor Variables
  • Cursor Variables vs Static Cursors
  • Predefined Data Types
  • Subtypes
  • Using Subtypes
• Collection Data Types
  • Object Types
  • Collection Types
  • Nested Tables
  • Varrays
  • Methods
  • Set Operations
  • Associative Arrays
• Dynamic SQL
  • Dynamic SQL
  • Native Dynamic SQL
  • The EXECUTE IMMEDIATE Statement
  • The DBMS_SQL Package
• Optimizing PL/SQL Code
  • Code Modularization
  • SQL vs PL/SQL
  • Bulk Binding
  • Conditional Control Statements
  • Implicit Data Type Conversions
  • Using PLS_INTEGER
  • The NOT NULL Constraint
  • Passing Data Between PL/SQL Programs 7-21
  • Identifying Memory Issues
  • Identifying Network Issues
  • Native and Interpreted Compilation
• Analyzing PL/SQL Code
  • Using DBMS_DESCRIBE
  • Using ALL_ARGUMENTS
  • Using DBMS_UTILITY.FORMAT_CALL_STACK
  • Tracing Code Execution
  • Profiling PL/SQL