PL/SQL Training

PL/SQL Course Reviews

The course is practical oriented. Good place for learning.

PL/SQL Course Content

PL/SQL Concept

• Client/Server Architecture
• Explain the benefits of PL/SQL
• Identify the different types of PL/SQL blocks
• Use PL/SQL Anonymous Block
• Understand How PL/SQL Gets Executed

Programming Language Fundamentals

• Make Use of PL/SQL Language Components
• Make Use of PL/SQL Variables
• Handle PL/SQL Reserved words
• Make Use of Identifiers in PL/SQL
• Make use of Anchored Data Types
• Declare and Initialize variables
• Understand the Scope of a Block, Nested Blocks

SQL in PL/SQL

• Making Use of DML in PL/SQL
• Use the SELECT INTO Syntax for variable Initialization
• Use DML in PL/SQL Block Make
• Use of Sequence in PL/SQL Block Making Use of SAVEPOINT
• Make Use of COMMIT, ROLLBACK, and SAVEPOINT in PL/SQL Block

Conditional Control:IF

• Use the IF-THEN Statement
• Use the IF-THEN-ELSE Statement
• Use the ELSEIF Statement
• Use NestedIF Statement

Conditional Control: CASE

• Use the CASE Statement
• Use the Search CASE Statement
• Use the CASE Expression
• The NULLIF Function
• Use the COALESCE Function

Iterative Control

• Use simple loop with EXIT Condition
• Use Simple loop with EXIT WHEN Condition
• Use WHILE LOOP
• Use Numeric FOR Loops With the IN Option
• Use Numeric FOR Loops with REVERSE Option
• Use the CONTINUE statement
• Use the CONTINUE WHEN Condition
• Use Nested Loops
• User Defined Exceptions
• Exception Propagation

Exceptions Adv

• RAISE_APPLICATION_ERROR
• EXCEPTION_INIT_Pragma
• SQL_CODE and SQLERRM

Procedures

• Create Procedures
• Query the Data Dictionary for Information on Procedures
• Passing Parameters into and out of Procedures
• Use IN and OUT Parameters with Procedures

Function

• Create Stored Functions
• Make Use of Functions
• Invoke Functions in SQL Statements
• Write Complex Functions

Packages

• Create Package Specifications
• Create Package Bodies
• Call Stored Packages
• Create Private Objects
• Create Package Variables and Cursors
• Make Use of Cursor Variables
• Extend the Package

Exceptions

• Understand the Scope of an Exception
• Use User-Defined Exceptions
• Understand How Exceptions Propagate
• Re-raise Exception

Exceptions Advanced Concepts

• Use RAISE_APPLICATION _ERROR
• Use EXCEPTION_INIT Pragma
• Use SQLCODE and SQLERRM

Introduction to Cursors

• Make Use of Record Types
• Process an Explicit Cursor
• Make Use of Cursor Attributes
• Put It All Together
• Use a Cursor FOR Loop
• Process Nested Cursors

Advanced Cursors

• For UPDATE and WHERE CURRENT Cursors

Trigger

• Understand What a Trigger Is
• Use BEFORE and AFTER Triggers
• Use Row and Statement Triggers
• Use INSTEAD OF Triggers
• Compound Triggers
• Understand Mutating Tables
• Understand Compound Triggers

Collections

• Use Associative Arrays
• Use Nested Tables Use Varray
• Use Multilevel Collections
• Records
• Use Table-Based and Cursor-Based Records
• Use User-Defined Records

Dynamic SQL

• The Execution Flow of SQL
• What is Dynamic SQL?
• Declare Cursor Variables
• Dynamically executing a PL/SQL Block
• Configure Native Dynamic SQL to Compile PL/SQL Code
• Invoke DBMS_SQL Package
• Implement DBMS_SQL with a Parameterized DML Statement
• Dynamic SQL Functional Completeness

Native Dynamic SQL

• Use the EXECUTE IMMEDIATE Statement
• Use OPEN-FOR, FETCH, and CLOSE Statements

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 Functions
• Usage of Bulk Binding to Improve Performance

Bulk SQL

• Use the FORALL Statement
• Use the BULK COLLECT Statement
• Stored Code
• Get Stored Code Information from the Data Dictionary
• Enforce the Purity Level with the RESTRICT_REFERENCES Pragma
• Overload Modules

Object Types in Oracle

• Use Object Types
• Use Object Types with Collections
• Use Object Type Methods

Oracle Supplied Packages

• Access Files with UTL_FILE
• Schedule Jobs with DBMS_JOB
• Submit Jobs
• Generate an Explain Plan and Create HTML Pages
• Generate an Explain Plan with DBMS_XPLAN
• Creating Web Pages with the Oracle Web Toolkit
• Create an HTML Page with the Oracle Web Toolkit