The Oracle Database 11g &12c PL/SQL Essentials course provides step-by-step instructions on how to perform a variety of PL/SQL development tasks for activities based on real-life work scenarios. The WebLearning hands-on series reduces the time spent investigating what steps are required to perform a task. Because the step-by-step solutions are built for practical real world situations, not only is knowledge gained through valuable hands-on experience, but also the solutions presented may then be used as the foundation for production implementation, dramatically reducing time to deployment.

This course provides step-by-step instructions for PL/SQL Database Application Development tasks using Oracle SQL Developer Tool. Students learn to build an Oracle Database and then develop database related applications using PL/SQL.

This course introduces students to the fundamentals of PL/SQL using Oracle Database 11g database technology. In this course students learn the concepts of relational databases and the powerful PL/SQL programming language.

Students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Students also learn to create anonymous PL/SQL blocks and are introduced to stored procedures and functions. Students learn about declaring identifiers and trapping exceptions. Demonstrations and hands-on practice reinforce the fundamental concepts.

Certification 

This course will assist in preparation for the Oracle Database Administration and Application DevelopmentI OCP Certification exams.

Duration           

N/A

Audience 

Business Analysts

Functional Implementer

Semantic Layer Modellers

Database Administrators

Technical Consultant

Business Intelligence Developer

Application Developers

Prerequisites:

Familiarity with data processing concepts and techniques

Course Objectives:

Declaring PL/SQL Variables

Working with Composite Data Types

Conditionally control code flow (loops, control structures)

Explicit cursors

Handle runtime errors

Describe stored procedures and functions

Course Topics:

Overview

Business Scenario 

The Evolution of Oracle     

Milestones

Introduction to Databases and the Relational Model

Key Oracle DBMS Concepts        

Data Modeling and Database Design     

Stages of System Development  

Basic Types of Data Relationships         

The Role of Ordinality and Normalisation          

Relational Database Components

Oracle Database 11g/12c Development Tools

Oracle SQL Developer       

Navigate SQL Developer-voip      

Exploring Database Objects         

SQL*Plus and SQL Workshop     

Connecting to the  SQL*Plus Interface  

Introduction to Oracle Database 11g and 12c

Oracle Database 11g Options
Oracle Database 12c Options
Database Management Packs     
Oracle Database 11g/12c Related Products 

Introduction to PL/SQL
PL/SQL Overview
Benefits of PL/SQL Subprograms
Overview of the Types of PL/SQL blocks
Creating and Executing a Simple Anonymous Block
Generating Output from a PL/SQL Block

Declaring PL/SQL Identifiers
Different Types of Identifiers in a PL/SQL subprogram
Using the Declarative Section to Define Identifiers
Storing Data in Variables
Scalar Data Types
%TYPE Attribute
Bind Variables
Using Sequences in PL/SQL Expressions

Writing Executable Statements
Describing Basic PL/SQL Block Syntax Guidelines
Commenting Code
SQL Functions in PL/SQL
Data Type Conversion
Nested Blocks
Operators in PL/SQL

Interacting with the Oracle Server
Including SELECT Statements in PL/SQL to Retrieve data
Manipulating Data in the Server Using PL/SQL
The SQL Cursor concept
Using SQL Cursor Attributes to Obtain Feedback on DML
Saving and Discarding Transactions

Writing Control Structures
Conditional processing Using IF Statements
Conditional processing Using CASE Statements
Simple Loop Statement
While Loop Statement
For Loop Statement
The Continue Statement

Working with Composite Data Types
Using PL/SQL Records
Using the %ROWTYPE Attribute
Inserting and Updating with PL/SQL Records
INDEX BY Tables
INDEX BY Table Methods
INDEX BY Table of Records

Using Explicit Cursors
Understanding Explicit Cursors
Declaring the Cursor
Opening the Cursor
Fetching data from the Cursor
Closing the Cursor
Cursor FOR loop
Explicit Cursor Attributes
FOR UPDATE Clause and WHERE CURRENT Clause

Handling Exceptions
Understanding Exceptions
Handling Exceptions with PL/SQL
Trapping Predefined Oracle Server Errors
Trapping Non-Predefined Oracle Server Errors
Trapping User-Defined Exceptions
Propagate Exceptions
RAISE_APPLICATION_ERROR Procedure

Creating Stored Procedures and Functions
Understanding Stored Procedures and Functions
Differentiate between anonymous blocks and subprograms
Create a Simple Procedure
Create a Simple Procedure with IN parameter
Create a Simple Function
Execute a Simple Procedure
Execute a Simple Function

In a nutshell, Self-Study Training Guide products are similar to Microsoft’s step-by-step books except Self-Study Training Guide products come with far more theory and practice; updated regularly ( every 3 months) and are delivered on CD-ROM in e-Book format with FULL LICENCE KEY.

Today's competitive business environment and frequent software updates by software vendor’s demands rapid skill acquisition. WebLearning Publishing helps you keep up-to-date by delivering quality Training Guides which are updated regularly - every QUARTER (3 months) - with FREE UPDATES to the LATEST EDITION: A NEW effective and proven way of Publishing and Learning. 

These factors give Training Guides a clear advantage over conventional, inconvenient and expensive Classroom and Hard-copy print training methods.

Training Guides not only offers the advantage in terms of convenience and lower cost, but it also facilitates improved subject-matter comprehension.

NOTE: WebLearning Publishing Training Guide products are no longer available in Hard-copy Printformat. They are now delivered in eBook Format with FULL LICENCE KEY.

 The WebLearning unique Methodology

All WebLearning® Training Guide courses are based on the latest materials available at the time of publishing and are regularly updated every 3 months with FREE UPGRADES to the LATEST EDITION: A NEW, very effective and proven way of learning.

NOTE: WebLearning Publishing Training Guide products are no longer available in Hard-copy Printformat. They are now delivered in eBook Format with FULL LICENCE KEY

FACT:

• Material retention with Self-study Training Guide is 35% better than with conventional training methods
• Your learning curve with Self-study Training Guide is 20%-40% less than conventional methods
• The cost of Self-study Training Guide training is over 95% less than Classroom training
• Self-study Training Guide are self pacing, hands-on and offer step-by- step guide to actual software being studied.