Menu Close

Oracle 11g PL/SQL

Oracle 11g PL/SQL

Course Schedule
Starts on Frequency’s / Timing Duration
Flexible Date & Time Flexible Date & Time 40 Hours
This course is designed for those who wish to learn the basics to advanced PL/SQL concepts at expert level. In this course, participants learn how to identify the programming extensions that PL/SQL provides to SQL and how to write PL/SQL code to interface with the database. Designing PL/SQL anonymous blocks that execute efficiently, using PL/SQL programming constructs and conditional control statements, handling run-time errors, and creating stored procedures and functions are also covered..

• Developer, Application Developers

• System Analysts, Business Analysts

• PL/SQL Developer ,Forms Developer

• Basic SQL/ SQL Expert Knowledge

Introduction to PL/SQL
  • Explain the need for PL/SQL
  • Explain the benefits of PL/SQL
  • Identify the different types of PL/SQL blocks
  • Output messages in PL/SQL
Declaring PL/SQL Variables
  • Recognize valid and invalid identifiers
  • List the uses of variables, declare and initialize variables, use bind variables
  • List and describe various data types using the %TYPE attribute
Writing Executable Statements
  • Identify lexical units in a PL/SQL block
  • Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions
  • Write nested blocks and qualify variables with labels
  • Write readable code with appropriate indentation
Interacting with the Oracle Database Server
  • Create PL/SQL executable blocks using DML and transaction control statements
  • Make use of the INTO clause to hold the values returned by a SQL statement
Writing Control Structures
  • Identify the uses and types of control structures
  • Construct and identify loop statements
  • Apply guidelines when using conditional control structures
Working with Composite Data Types
  • Create user-defined PL/SQL records
  • Create a record with the %ROWTYPE attribute
  • Create an INDEX BY table and INDEX BY table of records
  • Describe the differences among records, tables, and tables of records
Using Explicit Cursors
  • Distinguish between usage of implicit and explicit cursors, use SQL cursor attributes
  • Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data
  • Declare and use cursors with parameters
  • Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause
Handling Exceptions
  • Define PL/SQL exceptions
  • Recognize unhandled exceptions
  • Handle different types of exceptions
  • Propagate exceptions in nested blocks and call applications
Creating Stored Procedures and Functions
  • Differentiate between anonymous blocks and subprograms
  • Create a simple procedure and invoke it from an anonymous block
  • Create a simple function
  • Create a simple function that accepts a parameter
  • Differentiate between procedures and functions
Creating Procedures
  • Create a simple procedure and invoke it from an anonymous block
  • Work with procedures
  • Handle exceptions in procedures, remove a procedure, and display a procedure’s information
Creating Functions
  • Differentiate between a procedure and a function
  • Describe the uses of functions
  • Work with functions (create, invoke and remove functions)
Creating Packages
  • Identify the benefits and the components of packages
  • Work with packages
Working with Packages
  • Overload package subprograms, use forward declarations
  • Create an initialization block in a package body
  • Manage persistent package data states for the life of a session and use PL/SQL tables and records in packageS
Creating Triggers
  • Describe different types of triggers and their uses
  • Create database triggers
  • Manage triggers
Creating Compound, DDL, and Event Database Triggers
  • Create triggers on DDL statements
  • Create triggers on system events
  • Design PL/SQL packages to group related constructs
  • Create overloaded package subprograms for more flexibility
  • Design PL/SQL anonymous blocks that execute efficiently
  • Use the Oracle supplied PL/SQL packages to generate screen output, file output and mail output
  • Write dynamic SQL for more coding flexibility
  • Describe the features and syntax of PL/SQL
  • Create and debug stored procedures and functions
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Manage dependencies between PL/SQL subprograms
  • Handle runtime errors

Course Fee

To know the current Course Fees, kindly contact us +973 1753 0775,6660 0550.

  • 30 Hours Training
  • Study Materials
WhatsApp chat