Introduction to PL/SQL

  • PL/SQL STRUCTURE, Syntax, ANONYMOUS Example
  • PL/SQL First Program: Hello World Example
  • PL/SQL Data Types: Character, Number, Boolean, Date, LOB
  • Variable and Constants
  • Using built_in functions
  • Conditional and unconditional statements
    • Simple IF, ELSIF, ELSE…IF
    • 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/SQ

  • Implicit Cursors
  • Explicit Cursors
  • Cursor attributes
  • Cursor with parameters
  • Cursors with LOOPs
  • Cursors with sub queries
  • Ref.Cursors

Record and PL/SQL Table types

Advanced PL/SQL

Procedures in PL/SQL:

  • Stored Procedure
  • Procedure with parameters (IN,OUT and IN OUT)
  • POSITIONAL Notation and NAMED Notation
  • Procedure with Cursors
  • Dropping a procedure

Functions in PL/SQL

  • Difference between Procedures and Functions
  • User defined functions
  • Nested functions
  • Using stored function in SQL statements

Packages in PL/SQL

  • Creating PACKAGE specification and PACKAGE body
  • Private and public objects in PACKAGE

Exceptions in PL/SQL

  • User defined exceptions
  • Pre defined exceptions
  • RAISE_APPLICATION_ERROR
  • PRAGMA_AUTONOMOUS_TRANSACTION
  • SQL Error code values

Database Triggers in PL/SQL

Types of Triggers

  • Row level triggers
  • Statement level triggers
  • DDL Triggers
  • Trigger auditing

File input/output:

  • PL/SQL file I/O (input/output) using UTL_FILE package

Implementing object technology (Optional)

  • What is object technology?
  • OOPS-object instances
  • Creation of objects
  • Creating user defined data types
  • Creating object tables
  • Inserting rown in a table using objects
  • Retrieving data from object based tables
  • Calling a method
  • Indexing abstract data type attributes

Using LOBS

  • Large objects (LOBS)
  • Creting tables-LOB
  • Working with LOB values
  • Inserting, Updating & Deleting values in LOBs
  • Populating lobis DBMS_LOB routines
  • Using B-FILE

Using Collections

  • Advantages of collection
  • Ref Cursor (Dynamic Cursor)
  • Weak Ref Cursor
  • Strong Ref Cursor
  • Nested tables VARRAYS or VARYING arrays
  • Creating tables using nested tables
  • Inserting, Updating & Deleting nested table records
  • Nested table in PL/SQL

Oracle data base architecture

  • Introduction to oracle database architecture
  • Physical structures, Logical structures
  • DB Memory structures background process
  • 2-tier, 3-tier, N-tier architecture

Advanced features

  • 9i joines
  • 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 traction
  • Pragma_autonomous_transaction()
  • Returning into clause
  • Bulk collect
  • About flash back queries
  • Dynamic SQL
  • New 11g features

DBA Concepts

  • Data base
  • Table space
  • Types of tablespaces
  • Datafiles

Interview Preparations/Q&A