Oracle Database: Program With PL/SQL

Course details

This course starts with an introduction to PL/SQL and proceeds to list the benefits of this powerful programming language. Participants are made aware of how to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. In addition, creation of anonymous PL/SQL blocks as well as stored procedures and functions are covered in this course. Participants enhance their developer skills by learning to develop, execute, and manage PL\SQL stored program units such as procedures, functions, packages, and database triggers. Understanding the basic functionality of how to debug functions and procedures using the SQL Developer Debugger gives way to refined lines of code. Participants also learn to manage PL/SQL subprograms, triggers, declaring identifiers, and trapping exceptions. The utilization of some of the Oracle-supplied packages is also in the course. Additionally participants learn to use Dynamic SQL, understand design considerations when coding using PL/SQL, understand and influence the PL/SQL compiler, and manage dependencies.

This course is a bundle of Oracle Database: PL/SQL Fundamentals and Oracle Database:Develop PL/SQL Program Units courses. Students use Oracle SQL Developer to develop these program units. SQL*Plus and JDeveloper are introduced as optional tools.

This is appropriate for a 10g and 11g audience. There are minor changes between 10g and 11g features. A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.

Course Objectives :

• Use the Oracle supplied PL/SQL packages to generate screen output, file output, and mail output
• Describe the features and syntax of PL/SQL
• Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
• Manage dependencies between PL/SQL subprograms
• Handle runtime errors
• Create triggers to solve business challenges
• Design PL/SQL code for predefined data types, local subprograms, additional programs and standardized constants and exceptions
• Design PL/SQL anonymous block that execute efficiently
• Create and debug stored procedures and functions
• Use conditional compilation to customize the functionality in a PL/SQL application without removing any source code
• Design PL/SQL packages to group related constructs
• Create overloaded package subprograms for more flexibility
• Write dynamic SQL for more coding flexibility

Course Topics:

• Introduction
• Course Objectives
• Course Agenda
• Describe the Human Resources (HR) Schema
• PL/SQL development environments Available in this course
• Introduction to SQL Developer
• Introduction to PL/SQL
• Overview of PL/SQL
• Identify the benefits of PL/SQL Subprograms
• Overview of the Types of PL/SQL blocks
• Create a Simple Anonymous Block
• How to generate Output from a PL/SQL Block?
• Declare PL/SQL Identifiers
• List the different Types of Identifiers in a PL/SQL subprogram
• Usage of the Declarative Section to Define Identifiers
• Use variables to store data
• Identify Scalar Data Types
• The %TYPE Attribute
• What are Bind Variables?
• Sequences in PL/SQL Expressions
• Write Executable Statements
• Describe Basic PL/SQL Block Syntax Guidelines
• Learn to Comment the Code
• Deployment of SQL Functions in PL/SQL
• How to convert Data Types?