Open Contact Form
Please Contact Us

Your Name (required)

Your Email (required)

Subject

Your Message

img

PL-SQL

Introduction to PL/SQL

  • Need the PL/SQL
  • Benefits of PL/SQL
  • Different type of PL/SQL blocks
  • Output Message of PL/SQL Block

Declaring PL/SQL Variables

  • Valid and Invalid identifier
  • Use of Variables
  • Data Types
  • %TYPE attributes
  • Bind Variables

Writing Executive Statements

  • Lexical Units in a PL/SQL blocks
  • SQL functions in PL/SQL
  • Nested Blocks
  • Sequences in PL/SQL expressions

Interacting with the Oracle Database Server

  • PL/SQL executable blocks
  • DML statements in PL/SQL
  • TCL in PL/SQL
  • Use of INTO clause in SQL statement
  • Implicit Vs Explicit Cursors

Writing Control Statement

  • Types of Control Structures
  • IF Statement
  • CASE Statement
  • Loop statement
  • Conditional Control Structure

Working with Composite Data Types

  • User defined PL/SQL records
  • %ROWTYPE attribute
  • INDEX BY Table
  • INDEX TABLE of records
  • Different among records
  • tables, and tables of records

Using Explicit Cursors

  • Implicit Vs Explicit Cursor
  • Using explicit Cursors
  • Control explicit Cursors
  • Cursor FOR loops
  • Cursors with Parameters
  • WHERE CURRENT OF Clause

Handling Exceptions

  • PL/SQL Exceptions
  • Unhandled Exceptions
  • Types of exceptions handlers
  • Exception in Nested Blocks
  • PL/SQL exceptions messages

Creating Stored Procedures

  • What is Procedure
  • Syntax and Developing Procedure
  • What are Parameters
  • IN/OUT/IN OUT Procedures
  • Calling PL/SQL Using Host Variables
  • and Using Default Options for Parameter
  • Invoking Procedures
  • and Handled Exceptions

Creating Stored Functions

  • Overview of Stored Functions
  • Syntax and Developing Store Functions
  • Advantages of User-Defined Functions in SQL statements
  • Functions in SQL expressions
  • Locations to call User-defined functions
  • Restrictions on Calling Functions from SQL Expressions
  • Removing Functions
  • Procedure Vs Functions

Creating Packages

  • PL/SQL Packages
  • Component of PL/SQL Packages
  • Visibility of Package Components
  • Developing PL/SQL Packages
  • Creating Package Specification and Body
  • Invoking Package Subprograms
  • Creating and Using Bodiless Packages
  • Advantages of Using Packages

Using Oracle-Supplied Packages in Application Development

  • Using Oracle Supplied Packages
  • List of some Oracle-supplied Packages
  • DBMS_OUTPUT
  • UTL_FILE,IS_OPEN,HTP,UTL_MAIL, DBMS_SCHEDULER
  • Creating a Job, Creating a Job for a Program with Arguments
  • Creating Job Using Scheduler
  • Setting a Repeat Interval for a job

Dynamic SQL and Metadata

  • Execution Flow of SQL
  • Dynamic SQL
  • Native Dynamic SQL
  • Using the EXECUTE IMMEDIATE Statement
  • Dynamic SQL with a DDL Statement
  • Dynamic SQL with DML Statement
  • Dynamic SQL with Single-Row Query
  • Dynamic SQL with Multi-Row Query
  • Declaring Cursor Variables
  • Native Dynamic SQL to compile PL/SQL Code
  • Using DBMS_SQL Package
  • DBMS_METADAT

Design Considerations for PL/SQL Code

  • Standardizing Constants and Exceptions
  • Local Subprograms
  • Definer’s Rights Vs Invoker Rights
  • Autonomous Transactions
  • Returning Clause
  • Bulk Binding
  • Bulk Collect
  • FORALL, BULK COLLECT INTO with RETURNNG Clause
  • Using NOCOPY Hint
  • PARALLEL_ENABLE Hint

Managing Dependencies

  • Understanding Dependencies
  • Local Dependencies
  • Displaying Direct Dependencies by Using USER_DEOENDENCIES
  • Understating Remote Dependencies

Manipulating Large Objects

  • What is LOB
  • Contrasting LONG and LOB Data Types
  • What is BFILES
  • Securing BFILES
  • Managing BFILES
  • Preparing to use BFILES
  • Using DBMS_LOB
  • Migrating from LONG to LOB
  • DBMS_LOB
  • DBMS_LOB.READ, and DBMS_LOB.WRITE?

Creating Triggers

  • Types of Triggers
  • Guidelines for Designing Triggers
  • Creating DML Triggers
  • Types of DML Triggers
  • Trigger Timing
  • Trigger Firing Sequences
  • Trigger Event Types and Body
  • Creating a DML Statement Trigger
  • Using Conditional Predicates
  • Creating a DML Row Trigger
  • Using OLS and NEW Qualifier
  • Restricting a Row Trigger
  • INSTEAD OF Trigger
  • Database Trigger Vs Stored Procedures

Application for Triggers

  • Creating Database Trigger
  • Creating Trigger on DDL statement
  • Creating Triggers on System Events
  • LOGONG and LOGOFF Trigger
  • CALL statement
  • Reading Data from a Mutating Table
  • Benefits of Database Triggers

Understanding and Influencing the PL/SQL Compiler

  • Native and Interpreted Compiler
  • Features and Benefits of Native Compiler
  • Parameters Influencing Compilation
  • Switching Between Native and Interpreted Compilation
  • Using Native Compilation
  • DBMS_WARNING Package