Best PL / SQL Training Provided By IT Software Training LLC

Who Should attend

PL/SQL course is for fresh graduates with computer science/information technology background, developers and testers in any software platform, who have interest in working on database systems and have prior knowledge on SQL.

Prerequisites

Should have the knowledge of SQL

Course Description

In this course you learn about PL/SQL syntax, blocks, programming constructs, cursors, composite data types, exceptions handling. You learn how to write PL/SQL program units and execute them. You will learn how to design reusable program units, such as procedures and functions. You will also learn about Packages, overloading of subprograms, triggers, how to send mail from plsql, how to create jobs, how to read and write data in external file, how to write dynamic sql statements, how to set compilers etc.

Course Content

Introduction to Plsql

• What is Pl/Sql?
• Benefits of Pl/Sql
• Pl/Sql Block structure
• Block Types
• Create and execute an Anonymous Pl/Sql Block
• Test the output of a Pl/Sql Block

Declaring Pl/Sql variables

• What is variable
• What is Identifier
• Uses of variables
• Declaring and Initializing Pl/Sql variables
• Types of variables
• Data types
• Binary_Float and Binary_Double
• Declaring variables with the %Type attribute
• Declaring Boolean variables, NOT NULL variables, CONSTANT variables
• Declaring, using and Printing Bind variables Substitution variables
• Using Define for User variable

Statements in Plsql

• Writing comments in Plsql block
• Sql Functions in Plsql Block
• What is Literal? and how many types of Literals are there?
• Nested PL/SQL Blocks
• Variable Scope and Visibility
• Qualify an Identifier
• Operators in Pl/Sql

Interacting with Oracle Server from Pl/Sql Block

• Valid and invalid SQL statements in Pl/Sql
• SELECT statements in Pl/Sql
• Naming conventions
• Insert Statement in Pl/Sql
• Update Statement in plsql
• Delete statement in PlSql
• Merge statement in Pl/Sql

Control Structures

• Types of Control Structures and uses
• If statements
• Null values in IF statements
• Case Expressions
• Case Search Expressions
• Case statement
• Handling Nulls
• Loop statements
• Basic Loops
• While Loops
• For Loops
• Guidelines For Loops
• Nested Loops and Labels

Working with Composite Data types

• Composite Data types
• Creating Pl/Sql Records
• %ROWTYPE attribute and its advantages
• Inserting and Updating a Record by using %ROWTYPE attribute
• Index By Tables Or Associative Arrays
• Create an Index By Table
• Using Index By Table Methods
• Index By Table of Records
• Object Types
• Nested Tables
• Varray

Cursors

• What is Cursor
• Types of Cursors
• Implicit Cursor and its attributes
• Explicit Cursor
• Uses of Explicit Cursor
• Declaring the Cursor, Opening the Cursor, Fetching the data from cursor, closing the cursor,
• Cursors and Records
• Cursor FOR loops
• Explicit Cursor attributes
• Use of %ISOPEN attribute
• Use of %ROWCOUNT and %NOTFOUND attribute
• Cursor For Loop using Sub query
• Cursors with parameters
• For Update Clause
• Where Current of clause
• Cursors with Subqueries

Handling Exceptions

• What is Exception? Example of an Exception.
• Handling exceptions in Pl/Sql
• Exception Types
• Trapping exceptions
• Trapping Predefined Oracle Server Errors
• Trapping Non-Predefined Oracle Server Errors
• Trapping user-Defined Oracle Server Errors
• Functions for Trapping Exceptions
• Calling Environments
• Propagating Exceptions in a Subblock
• RAISE_APPLICATION_ERROR procedure

Stored Procedures

• What is Subprogram
• Benefits of subprograms
• Differences Between Anonymous Blocks and Subprograms
• What is a Procedure?
• Syntax for creating procedures
• Creating Procedures
• What are Parameters? Types of parameters.
• Parameter Modes
• Examples using parameter and Mode
• Using Default option for Parameters
• Invoking Procedures
• Data Dictionary to view procedures
• Removing procedures

Stored Functions

• What is a Function
• Syntax for creating functions
• Creating functions
• Different ways of calling or invoking a function
• Locations to call user defined functions
• Restrictions on calling functions from sql statements
• Mutating table error
• Removing Functions
• Data Dictionary view for functions
• Difference between Procedures and Functions

User Defined Packages

• What is Package
• Uses of Package
• Components of Package
• Visibility of Package components
• Syntax for package specification
• Syntax for package body
• Creating package specification
• Creating package body
• Invoking Package Subprograms
• Creating Bodiless Package
• Removing package
• Data Dictionary for Packages

More Package concepts

• Overloading subprograms
• Overloading and the Standard Package
• Forward Declarations
• Package Initialization Block
• Persistent State of Packages
• Using Pl/Sql tables of Records in Packages
• PL/SQL Wrapper

Triggers

• What is Trigger
• Types of Triggers
• DML Triggers
• Types of DML Triggers
• Trigger Timing
• Trigger Firing Sequence
• Trigger Event Types and Body
• Creating a DML statement trigger
• Creating a DML Row Trigger
• Creating Trigger using OLD and NEW qualifiers
• Restricting a Row Trigger
• Implementing an Integrity Constraint with a trigger
• Creating triggers on views(using INSTEAD OF)
• Difference between Database Triggers and Stored Procedures
• Managing Triggers
• Removing Triggers

DDL and System Events Triggers

• Creating Triggers for DDL events
• Creating Triggers for System events
• CALL statements
• Mutating Table Error
• Benefits of Database Triggers
• Managing Triggers
• Business Application Scenarios for Implementing Triggers
• Data Dictionary for Triggers

Design Considerations

• Standardizing Constants and Exceptions
• Local Subprograms
• Definer's Rights Versus Invoker's Rights
• Autonomous Transactions
• Returning Clause
• Bulk Binding
• Bulk Binding FORALL example
• Bulk Binding using BULK COLLECT INTO with queries
• Bulk Binding using BULK COLLECT INTO with cursors
• Bulk Binding using BULK COLLECT INTO with a RETURNING clause.
• NOCOPY Hint
• Effects of the NOCOPY Hint
• NOCOPY Hint can be ignored
• PARALLEL_ENABLE Hint

Manipulating Large Objects

• What is a LOB
• Difference between LONG and LOB data types
• Internal LOBs
• Example of storing Image in BLOB datatype column
• What are BFILES?
• Securing BFILES
• Creating DIRECTORY OBJECT
• Populating a BFILE columns with SQL
• Populating a BFILE columns WITH PL/SQL
• Using DBMS_LOB routines with BFILES
• Migrating from LONG TO LOB
• Example using DBMS_LOB.READ and DBMS_LOB.WRITE
• Initializing LOB columns added to a table
• Updating LOB BY using DBMS_LOB in PL/SQL
• Selecting CLOB values by using DBMS_LOB
• Selecting CLOB values by using SQL
• Selecting CLOB values in PL/SQL
• Removing LOBs

Oracle Supplied Packages

• Uses of Oracle Supplied packages
• List of Some Oracle-Supplied Packages
• How the DBMS_OUTPUT package works
• Using of UTL_FILE package
• Exceptions in the UTL_FILE package
• Examples of UTL_FILE package
• Using HTP package with example
• Installing and Using UTL_MAIL package
• DBMS_SCHEDULER package
• Creating and Managing jobs using DBMS_SCHEDULER package
• Data Dictionary views for jobs

Dynamic SQL

• Execution flow of SQL
• What is Dynamic SQL
• Native Dynamic SQL
• Using the Execute Immediate statement
• Dynamic SQL with DDL statement
• Dynamic SQL with DML statements
• Dynamic SQL with a single-row query
• Dynamic SQL with Multi-row Query
• Declaring Cursor Variables
• Dynamically Executing a PL/SQL Block

Dependencies

• Understanding Dependencies
• Local Dependencies
• Displaying Direct Dependencies using USER_DEPENDENCIES data dictionary view
• Displaying Direct and Indirect dependencies
• A scenario of Local Dependencies
• A scenario of Local Naming Dependencies
• Understanding Remote Dependencies
• REMOTE_DEPENDENCIES_MODE parameter
• Remote Dependencies and Time Stamp Mode
• Remote Dependencies and Signature Mode
• Recompiling a PL/SQL Program Unit
• Unsuccessful Recompilation
• Successful Recompilation
• Package and Dependencies

Pl/Sql Compilers and Compilation Warnings

• Types of Pl/Sql compilers
• Benefits of Native Compilation
• Considerations When using Native compilation
• Parameters Influencing compilation
• Switching Between Native and Interpreted Compilation
• Data Dictionary to compilation information
• Compiler Warning
• Setting Compiler Warning Levels using PLSQL_WARNINGS
• DBMS_WARNING package
• DBMS_WARNING procedures
• DBMS_WARNING functions
• Setting Compiler Warning Levels using DBMS_WARNING package.

Career Path

After learning PL/SQL, one can work as PL/SQL developer. There are many opportunities for PL/SQL developers with Shell Scripting as well. Java and PL/SQL is also a very good combination as there are lot of Java developer positions that require knowledge of PL/SQL. If you want to work as Oracle DBA, having knowledge of PL/SQL will be helpful. If you want to work on any Oracle tools, knowledge of PLSQL is mandatory. For ETL testers, Data Warehouse testers etc., PL/SQL is nice to have.