Oracle PLSQL Course Syllabus

Duration : 1 Month

Category :Oracle, PL/SQL, Database

Course Objective
Curriculum

The objective of the Oracle PL/SQL course is to develop strong programming skills for building robust, secure, and high-performance database applications using Oracle’s procedural extension of SQL. This course enables learners to understand and implement core PL/SQL concepts such as blocks, control structures, cursors, exceptions, procedures, functions, packages, and triggers.

Learners will gain hands-on experience in writing modular and reusable PL/SQL code, automating business logic, managing transactions, and optimizing database operations. The course emphasizes industry best practices, real-time problem solving, and the development of practical skills required for roles such as PL/SQL Developer, Backend Developer, and Database Programmer.

By the end of the course, learners will be able to design and implement scalable PL/SQL programs that enhance application performance and support enterprise database solutions effectively.

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
Operators
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

Native Dynamic SQL
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE using variables
DBMS_JOBS

Using DDLS in PLSQL

Running / Breaking Jobs
UTL_FILE
Creating Directory

Alexzender Alex

CSE Teacher

Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusa dolore mque laudantium totam rem aperiam eaqipsa quae ab illo inventore veritatvolup tatem quia voluptas sit aspernatur aut odit aut fugit sed quia conseque.

Nathaniel Bustos

Manager

Latanya Kinard

Web Designer

Where Our Alumini Works