Oracle PL/SQL Developer - OCA Training Course

  PART-1 Oracle Database SQL Fundamental
Live Lectures 1-20 

Lecture-1 Introduction
Live Lecture 
·      Course Objectives, Course Agenda & Appendixes

·      Overview of Oracle Database 12c & Related Products

·      Relational database management concepts & terminologies

·      Introduction to SQL & its development environments

·      What is the Oracle SQL Developer?

·      Starting SQL*Plus from Oracle SQL Developer

·      The Human Resource (HR) Schema

·      Tables used in the Course

·      Practical Exercise


Lecture-2 Retrieving Data using the SQL SELECT Statement
Live Lecture 
·      Capabilities of the SELECT statement

·      Arithmetic expressions & NULL values in the SELECT statement

·      Column aliases

·      Concatenation operator

·      Literal character strings

·      Alternative quote operator

·      DISTINCT keyword

·      Use of the DESCRIBE command

·      Relational database management concepts & terminologies

·      Practical Exercise


Lecture-3 Restricting and Sorting Data
Live Lecture 
·      Limiting the Rows

·      Rules of precedence for the operators in an expression

·      Substitution Variables

·      Using the DEFINE and VERIFY command

·      Practical Exercise


Lecture-4 Single-Row Functions to Customize Output
Live Lecture 
·      Difference between single row & multiple row functions

·      Manipulate strings with character function in the SELECT & WHERE clauses

·      Manipulate numbers with the ROUND, TRUNC & MOD functions

·      Perform arithmetic with date data

·      Manipulate dates with the date functions

·      Practical Exercise


Lecture-5 Conversion Functions & Conditional Expressions
Live Lecture 
·      Describe implicit & explicit data type conversion

·      TO_CHAR, TO_NUMBER,& TO_DATE conversion functions

·      Nest multiple functions

·      Apply the NVL, NULLIF,& COALESCE functions to data

·      Conditional IF THEN ELSE logic in a SELECT statement

·      Practical Exercise


Lecture-6 Reporting Aggregated Data Using Group Functions
Live Lecture 
·      Group Functions

·      Creating Groups of Data

·      Restricting Group Results

·      Practical Exercise


Lecture-7 Displaying Data from Multiple Tables Using Joins
Live Lecture 
·      Introduction to JOINS

·      Types of Joins

·      Natural join

·      Self-join

·      Non equijoins

·      OUTER join

·      Practical Exercise


Lecture-8 Using Sub queries to Solve Queries
Live Lecture 
·      Introduction to Sub queries

·      Single Row Sub queries

·      Multiple Row Sub queries

·      Practical Exercise


Lecture-9 Using the SET Operators
Live Lecture 
·      Set Operators

·      UNION and UNION ALL operator

·      INTERSECT operator

·      MINUS operator

·      Matching the SELECT statements

·      Using ORDER BY clause in set operations

·      Practical Exercise


Lecture-10 Managing Tables using D.M.L. statements
Live Lecture 
·      Data Manipulation Language

·      Database Transactions

·      Practical Exercise


Lecture-11 Introduction to Data Definition Language
Live Lecture 
·      Data Definition Language

·      Practical Exercise


Lecture-12 Introduction to Data Dictionary Views
Live Lecture 
·      Introduction to Data Dictionary

·      Describe the Data Dictionary Structure

·      Using the Data Dictionary views

·      Querying the Data Dictionary Views

·      Practical Exercise

 


Lecture-13 Creating Sequences, Synonyms, Indexes
Live Lecture 
·      Overview of sequences

·      Overview of synonyms

·      Overview of indexes

·      Practical Exercise


Lecture-14 Creating Views
Live Lecture 
·      Overview of views

·      Practical Exercise


Lecture-15 Managing Schema Objects
Live Lecture 
·      Managing constraints

·      Creating and using temporary tables

·      Creating and using external tables

·      Practical Exercise


Lecture-16 Retrieving Data by Using Sub queries
Live Lecture 
·      Retrieving Data by Using a Sub query as Source

·      Working with Multiple-Column sub queries

·      Using Scalar sub queries in SQL

·      Correlated Sub queries

·      Working with the WITH clause

·      Practical Exercise


Lecture-17 Manipulating Data by Using Sub queries
Live Lecture 
·      Using Sub queries to Manipulate Data

·      Inserting by Using a Sub query as a Target

·      WITH CHECK OPTION Keyword on DML Statements

·      Correlated Sub queries to Update and Delete rows

·      Practical Exercise


Lecture-18 Controlling User Access
Live Lecture 
·      System privileges

·      Creating a role

·      Object privileges

·      Revoking object privileges

·      Practical Exercise


Lecture-19 Manipulating Data
Live Lecture 
·      Overview of the Explicit Default Feature

·      Using multi-table INSERTs

·      Using the MERGE statement

·      Performing flashback operations

·      Tracking Changes in Data

·      Practical Exercise


Lecture-20 Managing Data in Different Time Zones
Live Lecture 
·      CURRENT_DATE, CURRENT_TIMESTAMP,& LOCALTIMESTAMP

·      Working with INTERVAL data types

·      Practical Exercise

  Part-2 Oracle Database: Program with PL/SQL
Live Lectures 21-40 
This Oracle Database Program with PL/SQL training begins with a prologue to PL/SQL and after that investigates the advantages of this effective programming dialect. Through hands-on instruction from master Oracle trainers, you'll figure out develop stored procedures, functions, packages and then some.


Lecture-21 Introduction to PL/SQL
Live Lecture 
·         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?

·         Practical Exercise


Lecture-22 Declare PL/SQL Variables
Live Lecture 
·         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

·         Practical Exercise


Lecture-23 Write Anonymous PL/SQL Blocks
Live Lecture 
·         Basic PL/SQL Block Syntax Guidelines

·         Learn to Comment the Code

·         Deployment of SQL Functions in PL/SQL

·         How to convert Data Types?

·         Describe Nested Blocks

·         Identify the Operators in PL/SQL

·         Practical Exercise


Lecture-24 SQL Statements in a PL/SQL block
Live Lecture 
·         Invoke SELECT Statements in PL/SQL

·         Retrieve Data in PL/SQL

·         SQL Cursor concept

·         Avoid Errors by using Naming Conventions

·         Using Retrieval & DML Statements

·         Data Manipulation in the Server using PL/SQL

·         Understand the SQL Cursor concept

·         Use SQL Cursor Attributes to Obtain Feedback on DML

·         Save and Discard Transactions

·         Practical Exercise


Lecture-25 Control Structures
Live Lecture 
·         Conditional processing using IF Statements

·         Conditional processing using CASE Statements

·         Describe simple Loop Statement

·         Describe While Loop Statement

·         Describe For Loop Statement

·         Use the Continue Statement

·         Practical Exercise


Lecture-26 Composite Data Types
Live Lecture 
·         Use PL/SQL Records

·         The %ROWTYPE Attribute

·         Insert and Update with PL/SQL Records

·         INDEX BY Tables

·         Examine INDEX BY Table Methods

·         Use INDEX BY Table of Records

·         Practical Exercise


Lecture-27 Explicit Cursors
Live Lecture 
·         What are Explicit Cursors?

·         Declare the Cursor

·         Open the Cursor

·         Fetch data from the Cursor

·         Close the Cursor

·         Cursor FOR loop

·         %NOTFOUND & %ROWCOUNT Attributes

·         FOR UPDATE Clause & WHERE CURRENT Clause

·         Practical Exercise


Lecture-28 Exception Handling
Live Lecture 
·         Understand Exceptions

·         Handle Exceptions with PL/SQL

·         Trap Predefined Oracle Server Errors

·         Trap Non-Predefined Oracle Server Errors

·         Trap User-Defined Exceptions

·         Propagate Exceptions

·         RAISE_APPLICATION_ERROR Procedure

·         Practical Exercise


Lecture-29 Stored Procedures
Live Lecture 
·         Create a Modularized & Layered Subprogram Design

·         Modularize Development With PL/SQL Blocks

·         Understand the PL/SQL Execution Environment

·         Benefits of using PL/SQL Subprograms

·         Difference between Anonymous Blocks & Subprograms

·         Create, Call, and Remove Stored Procedures

·         Implement Procedures Parameters & Parameters Modes

·         View Procedure Information

·         Practical Exercise


Lecture-30 Stored Functions
Live Lecture 
·         Create, Call, and Remove a Stored Function

·         Advantages of using Stored Functions

·         Steps to create a stored function

·         Invoke User-Defined Functions in SQL Statements

·         Restrictions when calling Functions

·         Control side effects when calling Functions

·         View Functions Information

·         Practical Exercise


Lecture-31 Debugging Subprograms
Live Lecture 
·         How to debug Functions and Procedures

·         Debugging through SQL Developer


Lecture-32 Packages
Live Lecture 
·         Advantages of Packages

·         Describe Packages

·         Components of a Package

·         Develop a Package

·         Enable visibility of a Packages Components

·         Package Specification & Body using the SQL CREATE Statement & Developer

·         Invoke the Package Constructs

·         View the PL/SQL Source Code using the Data Dictionary

·         Practical Exercise


Lecture-33 Deploying Packages
Live Lecture 
·         Overloading Subprograms in PL/SQL

·         Use the STANDARD Package

·         Forward Declarations to solve Illegal Procedure Reference

·         Implement Package Functions in SQL & Restrictions

·         Persistent State of Packages

·         Persistent State of a Package Cursor

·         Control side effects of PL/SQL Subprograms

·         Invoke PL/SQL Tables of Records in Packages

·         Practical Exercise


Lecture-34 Implement Oracle-Supplied Packages
Live Lecture 
·         What are Oracle-Supplied Packages?

·         Examples of some of the Oracle-Supplied Packages

·         How does the DBMS_OUTPUT Package work?

·         Interact with Operating System Files using the UTL_FILE Package

·         Invoke the UTL_MAIL Package

·         Write UTL_MAIL Subprograms

·         Practical Exercise


Lecture-35 Dynamic SQL
Live Lecture 
·         Execution Flow of SQL

·         Dynamic SQL

·         Declare Cursor Variables

·         Dynamically Executing a PL/SQL Block

·         Configure Native Dynamic SQL to Compile PL/SQL Code

·         How to invoke DBMS_SQL Package?

·         Implement DBMS_SQL with a Parameterized DML Statement

·         Dynamic SQL Functional Completeness

·         Practical Exercise


Lecture-36 Design Considerations for PL/SQL Code
Live Lecture 
·         Standardize Constants and Exceptions

·         Understand Local Subprograms

·         Write Autonomous Transactions

·         Implement the NOCOPY Compiler Hint

·         Invoke the PARALLEL_ENABLE Hint

·         Cross-Session PL/SQL Function Result Cache

·         DETERMINISTIC Clause with Functions

·         Usage of Bulk Binding to Improve Performance

·         Practical Exercise


Lecture-37 Triggers
Live Lectutre 
·         Describe Triggers

·         Identify the Trigger Event Types and Body

·         Business Application Scenarios for Implementing Triggers

·         DML Triggers using CREATE TRIGGER Statement & SQL Developer

·         Trigger Event Types, Body, and Firing (Timing)

·         Difference between Statement Level Triggers & Row Level Triggers

·         Create Instead of and Disabled Triggers

·         Manage, Test and Remove Triggers

·         Practical Exercise


Lecture-38 Creating Compound & Event Database Triggers
Live Lecture 
·         Compound Triggers

·         Identify Timing-Point Sections of a Table Compound Trigger

·         Compound Trigger Structure for Tables and Views

·         Compound Trigger to Resolve the Mutating Table Error

·         Comparison of Database Triggers to Stored Procedures

·         Create Triggers on DDL Statements

·         Create Database-Event & System-Events Triggers

·         System Privileges Required to Manage Triggers

·         Practical Exercise


Lecture-39 PL/SQL Compiler
Live Lecture 
·         PL/SQL Compiler

·         Initialization Parameters for PL/SQL Compilation

·         New PL/SQL Compile Time Warnings

·         PL/SQL Compile Time Warnings for Subprograms

·         Benefits of Compiler Warnings

·         PL/SQL Compile Time Warning Messages Categories

·         Setting the Warning Messages Levels:

·         Using SQL Developer

·         PLSQL_WARNINGS Initialization Parameter

·         DBMS_WARNING Package Subprograms

·         View Compiler Warnings:

·         Using SQL Developer, SQL*Plus, or Data Dictionary Views

·         Practical Exercise


Lecture-40 Manage Dependencies
Live Lecture 
·         Overview of Schema Object Dependencies

·         Query Direct Object Dependencies using the USER_DEPENDENCIES View

·         Query an Objects Status

·         Invalidation of Dependent Objects

·         Display the Direct and Indirect Dependencies

·         Fine-Grained Dependency Management in Oracle Database 12c

·         Understand Remote Dependencies

·         Recompile a PL/SQL Program Unit

·         Practical Exercise