Professional Training On Advanced Pl Sql Oracle 11g

Duration: 40 Hours
Prerequisites
• Oracle SQL
Course Content

1. Language Fundamentals
• Identifiers
• literals
• Anonymous Block Structure
• Nested Block or Named Block or PRAGMA
• Scope & Visibility of variables
2. Different Types of Loops (Iterative Processes)
• Simple Loop
• WHILE- Loop
• FOR- Loop
• Labels in Loop
• Scope Rules
• Exit
3. Working with Objects & Collection
• Describing Object
• Constructor method on objects
• Nested Table
• VARRAY
• Associative Array
• Collection Methods
• Manipulate Collection
• Distinguish between the different types of collections and when to use them
4. Dynamic SQL and PL/SQL
• Declaring dynamic SQL and PL/SQL by using DBMS_SQL package
• Declaring dynamic SQL and PL/SQL by using Execute Immediate
• Using Dynamic SQL in FORALL statement
• Dynamic statement using binding variables
• Advantage of using Dynamic SQL, PL/SQL
5. Tuning of PL/SQL Program
6. First PL/SQL code
7. Working with different type of Data types
• Date, Time and Interval Types
• Boolean Types
• Characters, String and National Character Types
• Number Types
• LOB Types
• Composite Data Types
8. Exception Handler
• What is Exception Handling
• How to Handle Exception
• Structure of Exception Handling
• Types of Exception Handling
• PRAGMA init Exception
9. Working with Records
• Declaring Records
• Benefit of using Record
• Passing Values To and From Record
• Comparing two Records
10. Bulk Processing of Data
• What is Bulk collect operation
• How does it impact performance
• Declaring BULK COLLECT operation with simple select statement
• BULK COLLECT with cursors
• BULK COLLECT using FORALL statement
• Catching exception in Bulk collect using SAVE EXCEPTION
11. Design Considerations for PL/SQL Code
• Standardize Constants and Exceptions
• Understand Local Subprograms
• Write Autonomous Transactions
• Implement the NOCOPY Compiler Hint
• Invoke the PARALLEL_ENABLE Hint
• The Cross-Session PL/SQL Function Result Cache
• The DETERMINISTIC Clause with Function