Oracle Fundamentals and PL SQL for beginners

Oracle Fundamentals and PL SQL for beginners

Course Overview

Brief Introduction to PL/SQL

• What is PL/SQL

• SQL Versus PL/SQL
• PL/SQL Architecture
• Advantages of PL/SQL

Working with a different type of Datatypes

• Date, Time and Interval Types
• Boolean Types
• Characters, String and National Character Types
• Number Types
• LOB Types
• Composite data types

Exception Handler

• What is Exception Handling
• How to Handle Exception
• Structure of Exception Handling
• Types of Exception Handling
• SAVE Exception in Bulk Collect
• PRAGMA init Exception

Cursors in PL/SQL

• Why cursor is used
• Declaring Cursors
• Different Types of a cursor
• Cursor Attributes
• Sending parameter to a cursor
• Different ways of using the cursor
• Cursor Variable (Reference Cursor)
• Advantage of using cursor variable over a normal cursor
• Passing Cursor variable as the parameter
• Restriction on Cursor Variables

Working with Procedures, Functions, and Packages

• Basic of stored procedure
• Basics of functions
• Basics of packages
• Advantage of using procedures, function, and packages
• Passing cursor variables to procedures, function, and packages
• Recompiling functions and stored procedures
• Forward declaration
• Declaring and using persistent global variables in packages
• Creating table Function
• Function overloading
• Restrict Reference Pragma
• Pinning packages in the SGA with dbms_shared_pool.keep

Overview of some advanced PL/SQL topics

• Table Function
• Pipelined table function
• Error log tables
• Hints in PL/SQL

Writing PL/SQL blocks using tools

• PL/SQL Developers
• Toad
• SQL Plus
• SQL Developers etc.

Control Structures

• IF-THEN-ELSE Statement
• Case Statement
• GOTO Statement
• NULL Statement

Working with Objects & Collection

• Describing Object
• Constructor method on objects
• Nested Table
• VARRAY
• Associative Array
• Collection Methods
• Manipulate Collection
• Distinguish between the different types of collections and when to use them

Dynamic SQL and PL/SQL

• When to use
• Declaring dynamic SQL and PL/SQL by using DBMS_SQL package
• Declaring dynamic SQL and PL/SQL by using Execute Immediate
• Using Dynamic SQL in the FORALL statement
• A dynamic statement using binding variables
• Advantage of using Dynamic SQL and PL/SQL

Debugging and Error tracking of PL/SQL code

• Using Error log message tables
• Using some built-in packages
• Using tools like PL/SQL developers

Tuning of PL/SQL program

Language Fundamentals

• identifiers
• literals
• Executable and Non Executable statement
• Anonymous Block Structure
• Nested Block
• Named Block
• PRAGMA
• Scope & Visibility of variables

Different types of loops

• Simple Loop
• WHILE- Loop
• FOR- Loop
• Labels in Loop
• Scope Rules
• Exit Statement

Working with Records

• Declaring Records
• The benefit of using the record
• Passing Values To and From Record
• Comparing two records

Bulk processing of data using Bulk Collect

• What is Bulk collect operation
• How does it impact performance
• Declaring BULK COLLECT operation with simple select statement
• BULK COLLECT with cursors
• BULK COLLECT using FORALL statement
• Catching exception in Bulk collect using SAVE EXCEPTION

Working with Triggers.

• Describe Triggers
• Identify the Trigger Event Types and Body
• Business Application Scenarios for Implementing Triggers
• Create DML Triggers using the CREATE TRIGGER Statement
• Identify the Trigger Event Types, Body, and Firing (Timing)
• Differences between Statement Level Triggers and Row Level Triggers
• Create Instead of and Disabled Triggers
• How to Manage, Test and Remove Triggers?