Oracle PL-SQL

 

This Oracle Database: Program with PL/SQL training starts with an introduction to PL/SQL and then explores the benefits of this powerful programming language. Through hands-on instruction from expert Oracle instructors, you'll learn to develop stored procedures, functions, packages and more. Practice Material, Certification, Resume & Interview Guidance are included in this PL-SQL.

 

Training & Schedules

Brochure 

Trainer 1 : Mr.Vamsi Krishna

Schedules(IST) Demo Date Start Date Register
3 PM to 4:30 PM August 21st August 22nd Register

Trainer 2 : Mr.Narayana

Schedules(IST) Demo Date Start Date Register
6 AM to 7 AM Aug 22nd Aug 23rd Register
3 PM to 4:30 PM Aug 25th Aug 28th Register
7 AM to 10 AM Aug 26th Aug 27th Register
* Job Support Available Register for another Schedules

PL-SQL Training

 
 

Oracle PL-SQL Course Fee : 4,000/-

DUR : 4 Weeks


Highlights

  • ✔ Completely Practical and Realtime
  • ✔ Theory Material provided in Advance
  • ✔ Highly Interactive and Interesting
  • ✔ Certification Guidance and FAQs
  • ✔ 80% Hands on Training, 20% Theroy part Explanantion

One Real-Time Project

 

 

Oracle PL-SQL Training Course Content

PL-SQL Training PDF

MODULE-1

MODULE-2

Introduction

  • Approach to Data Management, File and
  • File System
  • Disadvantages of file base data management
  • Database Terminology
  • Database Models – Hierarchal Model,
  • Network Model, Relational Model

Introduction to RDBMS

  • Features of RDBMS
  • Advantages of RDBMS over FMS & DBMS
  • E.F Codd Rules for RDBMS
  • Normalization Process
  • Client server Technology
  • Oracle Corporation Products
  • Oracle Versions about SQL & SQL*PLUS
  • Difference between OLTP & OLAP

Sub Language Commands

  • Data Definition Language(DDL)
  • Data Retrieval Language (DRL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Database Security and Privileges (DCL)

Introduction to SQL

  • Schema & Schema Objects
  • Oracle Data Types
  • DDL Commands
  • DML Command DRL Commands Operators
  • Arithmetic and Logical Operators
  • Set Operators (UNION, UNION ALL, INTERSECT, MINUS)
  • Special Operators – IN (NOT IN), BETWEEN (NOT BETWEEN),
  • LIKE (NOT LIKE), IS NULL (IS NOT NULL)

Built-in Functions

  • Arithmetic Functions
  • Character Functions
  • Date functions
  • Conversion Functions
  • Aggregate Functions
  • OLAP Functions & General Functions

Grouping the result of a query

  • Using Group by and Having Clause of DRL Statement
  • Using Order by clause
  • Working with, Sequences, Working with Synonyms,
  • Working with Index and Clusters, Creating Cluster Tables,
  • implementing Locks, Working with roles

Working with Integrity Constraints

  • Importance of Data integrity
  • Working with different types of integrity constraints
  • NOT NULL constraint, UNIQUE constraint,
  • PRIMARY KEY constraint, REF constraint
  • Understanding ON DELETE clause in referential integrity constraint
  • Working with multiple constraints upon a column
  • Adding constraints to a table
  • Dropping of constraints, Enabling /
  • disable constraints
  • Querying for constraints information

Querying Multiple Tables (Joins)

  • Equi Join/Inner Join/Simple Join,
  • Cartesian Join, Non-Equi Join, Self Join

Working with Sub Queries

  • Understanding the practical approach
  • to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select
  • Usage of Sub Queries.
  • The Impact of Having Clause in Sub Queries,
  • IN, ANY SOME, ALL Operators in Sub Queries
  • PAIR WISE and NON PAIR WISE Comparison in Sub Queries, Be…
  • Aware of NULL’S, Correlated Sub Queries,
  • Handling Date Retrieval with EXISTS and NOT EXISTS Operators

Working with DCL, TCL Commands

  • Grant, Revoke,
  • Commit, Rollback, Savepoint
  • Read Consistency

Maintaining Database Objects

  • VIEWS IN ORACLE
  • Understanding the Standards of VIEWS
  • in Oracle
  • Types of VIEWS: Relational Views,
  • Practical approach of SIMPLE VIEWS and COMPLEX VIEWS,
  • Column definitions in VIEWS, Using VIEWS for DML Operations,
  • forced Views, Putting CHECK Constraint upon
  • VIEWS, Creation of READ ONLY VIEWS, Understanding the IN LINE VIEWS
  • TOP-N Analysis, About Materialized
  • Views, View Triggers
  • Outer Joins – Types, Advantages and Usage
  • Cross Joins – Advantages and Limitations
  • Self Joins, Merge Joins and Sub Queries
  • Purity Levels, User _Source Dictionary table Packages in PL/SQL:
  • Creating PACKAGE Specification

Packages

  • PACKAGE Body, Private and Public Objects in PACKAGE
  • XCEPTIONS in PL/SQL: Types of Exceptions: User Defined Exceptions
  • SQL Error Code Values

Pseudo Columns in Oracle

  • Understanding Pseudo Columns in Oracle
  • Type of Pseudo Columns in Oracle
  • CURRVAL AND NEXTVAL, LEVEL, ROWID, ROWNUM

Data Partitions & Parallel Process

  • Range Partitions, Hash Partition
  • List Partition, Composite Partition, Parallel
  • Query Process

Locks

  • Row level Locks
  • Table level Locks
  • Shared Lock
  • Dead Lock
  • Implicit Locking

PL/SQL (Procedure Language – SQL)

  • introduction to Programming Languages
  • Introduction PL/SQL, The Advantages of
  • PL/SQL
  • PL/SQL Architecture, PL/SQL Data types
  • Variable and Constants, Using Built-in
  • Functions
  • Conditional and Unconditional Statement
  • Simple if, if..else. Nested if..else, if.. else
  •  Ladder, Selection Case, Simple Case, GOTO Label and EXIT
  • Iterations in PL/SQL, Simple LOOP, WHILE LOOP,
  • FOR LOOP and NESTED LOOPS
  • SQL within PL/SQL, Composite Data types
  • (complete), Cursor Management in PL/SQL
  • Implicit Cursors, Explicit Cursors, Cursor
  • Attributes
  • Cursor with Parameters, Cursors with LOOPs Nested Cursors,
  • Cursors with Sub Queries, Ref. Cursors, Record and PL/SQL Table Types.

Advanced PL/SQL

  • Procedures in PL/SQL: STORES PROCEDURE,
  • PROCEDURE with Parameters (IN, OUT and IN OUT
  • POSITIONAL Notation and NAMED Notation,
  • Procedure with Cursors, Dropping a Procedure
  • Functions in PL/SQL: Differences between
  • Procedures and Functions,
  • User Defined Functions, Nested Functions,
  • using stored function in SQL statements
  • Pragma_Autonomous_Transaction(),
  • Returning into clause, Bulk Collect,
  • For All, Definer/Invoker Rights
  • About flash Back Queries, Dynamic SQL,
  • Flash back command, Purge Command, Recycle bin, Regular expressions,
  • DML Error Logging, Virtual Columns, Read only tables,
  • Cross tab Views using, Pivot/Unpivot Operators Follows Clause,
  • Compound Triggers, New data types
  • Working with LARGE Objects

Database Triggers in PL/SQL

  • Types of Triggers, Row Level Triggers,
  • Statement Level Triggers, DML Triggers, DDL Triggers, Schema Triggers
  • Using OLD and NEW References, Trigger Auditing,
  • Enabling / Disabling Triggers, Dropping Triggers

Implementing Object Technology

  • What is Object Technology?
  • OOPS-Object instances, Creation of objects
  • Creating user Defined Date Types
  • Creating Object Tables
  • Inserting rows in a table using object
  • Retrieving date from Object based
  • Tables
  • Calling a Method, Indexing Abstract
  • Data type Attributes

Using Collections

  • Advantages of collection
  • Ref Cursor (Dynamic Cursor), Weak Ref Cursor,
  • Strong Ref Cursor, Nested Tables
  • Associative Arrays, VARRAYS or
  • VARYING arrays
  • Creating tables using nested tables
  • Inserting, Updating & Deleting
  • Nested, Table Records

Advanced Features

  • New Date function, Rename column,
  • Inner Join/Natural Join,
  • Left Outer Join/Right Outer Join, Full Outer Join
  • Multiple inserts, Insert All Command,
  • Merge statement, NVL2(), NULLIF(), COALESCE(),
  • CASE expression of Select Command, Temporary Tables/Global Tables
  • New Function EXTRACT (), Autonomous Transaction

Oracle Built-in Packages

  • DBMS_OUTPUT Package
  • DBMS_DDL Package
  • DBMS_JOB Package
  • DBMD_SCHEDULER Package
  • UTL_FILE Package
  • UTL_MAIL Package

Introduction to Oracle Database

  • Introduction to Oracle Database
  • Architecture
  • Physical Structures Logical structures
  • DB Memory Structures

Oracle Utilities

  • Oracle Data Pump
  • SQL * Loader
  • External Tables

Register Today