ORACLE - PSLSQL Course Content

PL/SQL

Introduction to PLSQL
PL/SQL Engine Architecture Structure Of PL/SQL The PLSQL Development Environments Executing SQL Statements Creating PLSQL Blocks Save to SQL Scripts Executing from SQL Scripts Creating Anonymous Blocks Generate Output from a PL/SQL Block
Declaring PLSQL variables
Use of variables What are Identifiers Declaring PLSQL Variables Types of Variables Datatypes %TYPE Attribute Bind Variables Printing Bind variables Substitution Variables Using Define for variables
Writing PLSQL Codes
PLSQL Block Syntax SQL Functions in PLSQL Nesting Functions Datatype Conversion Nested Blocks Commenting Codes
Interacting with database
SELECT statements in PLSQL DMLS using PLSQL Code SQL Cursor Implicit Cursors Attributes
Control Statements
Controlling flow of execution Types of IF Statements CASE Statements Handling Nulls Interactive Control Statements
Looping Structures
FOR Loop WHILE Loops GOTO Statements CONTINUE Statements Nested Loops and Labels
Composite Datatypes
PLSQL records %ROWTYPE Attribute DMLs using %ROWTYPE Associative Arrays Using INDEX BY table Structure INDEX BY table of records
Explicit Cursors
Cursors Controlling explicit Cursors Fetching data from cursors Cursors and records Closing cursors Cursor FOR loop Cursor Attributes Parameterized cursors For Update Cursor with subqueries Where CURRENT of
Exceptions
What is Exception? Handling Exceptions Types of Exception Predefined Errors Non-predefined Exceptions User Defined Exceptions RAISE_APPLICATION_ERROR Handling Exceptions in block SQLCODE and SQLERRM in PLSQL
Database Triggers
Types of triggers Creating DML Triggers Statement Vs Rows Level triggers Using OLD and NEW Qualifiers INSTEAD OF Triggers AUTONOMOUS Transaction Enabling and Disabling triggers Compiling Triggers Dropping Triggers System Level Triggers LOGON and LOGOFF triggers
Procedures and Functions
Creating procedures IN , OUT, IN OUT Parameters Passing parameters to functions Calling Functions from PLSQL Invoking procedures in PLSQL Viewing Codes using Views Viewing USER_SOURCE Viewing USER_ERRORS Viewing SHOW ERRORS Viewing USER_OBJECTS Compiling invalid Objects
Create Packages
Structures of Package What is STANDARD Package Public and Private Constructs Creating Package Specification Creating package Body Removing package Granting and Revoking permissions PLSQL Tables and records in packages
Oracle Supplied packages
DBMS_ERRLOG DBMS_SCHEDULER DBMS_JOBS Running / Breaking Jobs UTL_FILE Creating Directory
Using DDLS in PLSQL
Dynamic SQL EXECUTE IMMEDIATE EXECUTE IMMEDIATE using variables