Oracle SQL Topics Overview

Abstract: PL/SQL Topics

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension
to SQL, allowing users to write complex business logic within the database using procedural
constructs such as loops, conditions, and exception handling. This abstract provides an
overview of essential PL/SQL topics for mastering Oracle database programming and
interview preparation.

Key Topics in PL/SQL

1. Introduction to PL/SQL

What is PL/SQL?

Features and Benefits of PL/SQL

Difference Between SQL and PL/SQL

PL/SQL Architecture

2. PL/SQL Blocks and Variables

Anonymous Blocks

Named Blocks: Procedures, Functions, Packages

Data Types in PL/SQL ( %TYPE , %ROWTYPE )

Constants and Bind Variables

3. Control Structures in PL/SQL

Conditional Statements: IF-THEN , IF-THEN-ELSE , CASE

Loops in PL/SQL: FOR , WHILE , LOOP , CONTINUE

Nested Loops and Conditional Loops

4. Cursors in PL/SQL

Implicit Cursors

Explicit Cursors

1/3
 Cursor FOR LOOP

Parameterized Cursors

REF Cursors (Dynamic Cursors)

5. Exception Handling

Predefined Exceptions ( NO_DATA_FOUND , TOO_MANY_ROWS , ZERO_DIVIDE )

User-Defined Exceptions ( RAISE_APPLICATION_ERROR )

Exception Propagation

6. Procedures and Functions

Creating and Executing Procedures

Creating and Executing Functions

Parameter Modes: IN , OUT , IN OUT

Difference Between Procedures and Functions

7. Triggers in PL/SQL

Types of Triggers: BEFORE , AFTER , INSTEAD OF

Row-Level vs. Statement-Level Triggers

DML, DDL, and Database Event Triggers

Mutating Table Errors and Solutions

8. PL/SQL Packages

Components of a Package: Specification & Body

Public and Private Procedures in Packages

Package Initialization and Overloading

Package State Management

9. Collections in PL/SQL

Types of Collections: Associative Arrays, Nested Tables, VARRAYs

Collection Methods ( EXTEND , DELETE , COUNT , FIRST , LAST )

Bulk Collect ( FORALL , BULK COLLECT ) for Performance Tuning

10. Dynamic SQL in PL/SQL

2/3
 EXECUTE IMMEDIATE Statement

DBMS_SQL Package for Dynamic Queries

11. Advanced PL/SQL Features

Autonomous Transactions

Using PL/SQL with Object Types

Using PL/SQL with XML and JSON

Calling Java Code from PL/SQL

12. Performance Tuning and Best Practices

PL/SQL Optimization Techniques

Reducing Context Switching Between SQL and PL/SQL

Using BULK COLLECT and FORALL for Performance

Indexing Strategies for PL/SQL Queries

13. Real-World Applications of PL/SQL

Data Validation and Business Rule Enforcement

Automating Database Tasks with PL/SQL Jobs

Developing Secure Applications with PL/SQL

PL/SQL in Oracle Forms and Reports

These topics form a strong foundation for mastering PL/SQL, making it easier to build robust
database applications, automate tasks, and improve performance.

3/3
Oracle SQL Topics Overview

Introduction to PL/SQL
1. What is PL/SQL?
PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension
of SQL, allowing users to write procedural logic (loops, conditions, error handling) along
with SQL operations.

Key Characteristics
Block-Structured: Code is organized into blocks (DECLARE, BEGIN, EXCEPTION, END).

Procedural Features: Supports loops ( FOR , WHILE ), conditions ( IF-ELSE ), and error
handling ( EXCEPTION ).

Supports SQL: PL/SQL integrates seamlessly with SQL for database operations.

Improves Performance: Reduces multiple calls between SQL and the database, making
execution faster.

Error Handling: Built-in exception handling mechanisms improve robustness.

Basic PL/SQL Block Structure

sql

DECLARE
v_message VARCHAR2(50) := 'Hello, PL/SQL!';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

Output:

sql

Hello, PL/SQL!

1/5
2. Features and Benefits of PL/SQL
2.1. Features
✅ Block-Oriented Execution:
PL/SQL code is grouped into blocks (anonymous or named).

✅ Exception Handling:
Built-in error handling mechanisms using EXCEPTION .

✅ Procedural Constructs:
Supports loops ( FOR , WHILE ), conditions ( IF-ELSE ).

✅ Code Reusability:
Supports stored procedures, functions, and triggers.

✅ Integration with SQL:

Allows direct use of SQL inside PL/SQL.

✅ Performance Optimization:
Supports bulk processing (BULK COLLECT, FORALL) to handle large data efficiently.

✅ Security & Data Integrity:

Grants permissions using roles to restrict access.

3. Difference Between SQL and PL/SQL

Feature SQL PL/SQL

Definition Structured Query Language for data Procedural extension of SQL for
retrieval and manipulation. procedural logic and programming.

Execution Executes one statement at a time. Executes multiple statements within a

block.

Supports ❌ No ✅ Yes (IF, LOOP, EXCEPTION)

Procedural Logic?

Performance Requires multiple queries for logic. Combines logic and SQL, reducing
network traffic.

Error Handling ❌ Not built-in. ✅ Uses EXCEPTION for error

handling.

2/5
 Feature SQL PL/SQL

Use Case Retrieving, inserting, updating, and Automating tasks, complex logic, and
deleting data. batch processing.

Example

SQL Query (Single Statement Execution)

sql

SELECT emp_name, salary FROM employees WHERE department = 'IT';

PL/SQL Block (Procedural Execution)

sql

DECLARE
v_name employees.emp_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT emp_name, salary INTO v_name, v_salary FROM employees WHERE department =
'IT' AND ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ', Salary: ' || v_salary);
END;
/

4. PL/SQL Architecture
PL/SQL follows a three-tier architecture:

4.1. Components of PL/SQL Architecture

1️⃣ PL/SQL Block (Client Side)
The PL/SQL engine parses the PL/SQL block.

Syntax checking is done.

2️⃣ PL/SQL Engine (Database Side)

Executes procedural code.

3/5
 Sends SQL statements to the SQL engine.

3️⃣ SQL Engine (Database Side)

Executes SQL queries (SELECT, INSERT, UPDATE, DELETE).

Returns data/results.

4.2. Execution Flow

1. Client sends PL/SQL block → PL/SQL engine parses it.

2. Procedural statements are executed directly by the PL/SQL engine.

3. SQL statements are processed by the SQL engine.

4. Results are sent back to the PL/SQL engine.

4.3. Diagram of PL/SQL Execution Architecture

pgsql

+------------------+ +------------------+
| PL/SQL Block | ----> | PL/SQL Engine |
+------------------+ +------------------+
|
Procedural Statements Executed
|
SQL Statements Sent to
|
+------------------+ +------------------+
| SQL Engine | ----> | Database |
+------------------+ +------------------+
SQL Executed & Data Retrieved

5. Conclusion
PL/SQL is Oracle's procedural extension of SQL used for writing complex logic in blocks.

It supports procedural constructs like loops, conditions, and error handling.

It integrates with SQL seamlessly, improving performance and security.

4/5
 PL/SQL architecture ensures optimized execution by handling procedural and SQL
logic separately.

🚀 Next Steps: Want to explore PL/SQL Blocks, Variables, or Exception Handling in depth?

5/5
Oracle SQL Topics Overview

PL/SQL Blocks and Variables

PL/SQL programs are written in blocks, which are the basic units of PL/SQL code. A block is a
logical grouping of statements that perform a specific task. PL/SQL supports anonymous
blocks (not stored in the database) and named blocks (stored procedures, functions, and
packages).

1. PL/SQL Block Structure

Every PL/SQL block consists of the following four sections:

1. DECLARE (Optional) → Declare variables, constants, and cursors.

2. BEGIN (Mandatory) → Contains executable SQL and PL/SQL statements.

3. EXCEPTION (Optional) → Handles runtime errors.

4. END (Mandatory) → Marks the end of the block.

Example of a PL/SQL Block

sql

DECLARE
v_message VARCHAR2(50) := 'Hello, PL/SQL!';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_message);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;
/

Output:

sql

Hello, PL/SQL!

1/8
2. Types of PL/SQL Blocks
2.1. Anonymous Blocks
Do not have a name.

Are not stored in the database.

Used for one-time execution.

Example:

sql

BEGIN
DBMS_OUTPUT.PUT_LINE('This is an Anonymous Block.');
END;
/

Output:

mathematica

This is an Anonymous Block.

2.2. Named Blocks

Named blocks are stored in the database for reuse. They include:

1. Procedures

2. Functions

3. Packages

2.2.1. Stored Procedure

A named block that performs a specific task.

Does not return a value (unlike a function).

2/8
Example:

sql

CREATE OR REPLACE PROCEDURE greet_user IS

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from a Stored Procedure!');
END;
/

To execute the procedure:

sql

BEGIN
greet_user;
END;
/

Output:

css

Hello from a Stored Procedure!

2.2.2. Functions

A named block that returns a value.

Can be used in SELECT statements.

Example:

sql

CREATE OR REPLACE FUNCTION get_welcome_message RETURN VARCHAR2 IS

BEGIN
RETURN 'Welcome to PL/SQL Functions!';
END;
/

3/8
To execute the function:

sql

DECLARE
v_msg VARCHAR2(100);
BEGIN
v_msg := get_welcome_message;
DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/

Output:

pgsql

Welcome to PL/SQL Functions!

2.2.3. Packages

A collection of related procedures and functions.

Provides modularization and encapsulation.

Example:

sql

CREATE OR REPLACE PACKAGE my_package IS

PROCEDURE greet;
END my_package;
/

sql

CREATE OR REPLACE PACKAGE BODY my_package IS

PROCEDURE greet IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from a Package!');
END greet;

4/8
 END my_package;
/

To execute the package procedure:

sql

BEGIN
my_package.greet;
END;
/

Output:

css

Hello from a Package!

3. Data Types in PL/SQL

PL/SQL supports various data types for handling different kinds of data.

3.1. Scalar Data Types

Data Type Description Example

VARCHAR2(size) Variable-length string VARCHAR2(50)

NUMBER(p,s) Numeric values with precision NUMBER(10,2)

DATE Stores date and time SYSDATE

BOOLEAN Stores TRUE/FALSE TRUE

CLOB Stores large text CLOB

3.2. %TYPE (Attribute Data Type)

Used to inherit the data type of an existing column.

Ensures data type consistency.

Example:

5/8
 sql

DECLARE
v_emp_name employees.emp_name%TYPE; -- Inherits data type of emp_name column
BEGIN
SELECT emp_name INTO v_emp_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END;
/

3.3. %ROWTYPE (Row Data Type)

Used to store an entire row of a table into a record variable.

Prevents mismatches in column definitions.

Example:

sql

DECLARE
v_emp_record employees%ROWTYPE; -- Stores an entire row from employees table
BEGIN
SELECT * INTO v_emp_record FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_record.emp_name || ', Salary: ' ||
v_emp_record.salary);
END;
/

4. Constants and Bind Variables

4.1. Constants
Value cannot be changed after initialization.

Declared using CONSTANT keyword.

6/8
Example:

sql

DECLARE
c_tax_rate CONSTANT NUMBER := 0.18; -- Constant tax rate
v_total NUMBER := 1000;
BEGIN
DBMS_OUTPUT.PUT_LINE('Tax Amount: ' || (v_total * c_tax_rate));
END;
/

Output:

yaml

Tax Amount: 180

4.2. Bind Variables

Used to pass values between PL/SQL and SQL queries.

Declared outside PL/SQL blocks.

Example in SQL*Plus:

sql

VARIABLE v_salary NUMBER;

EXEC :v_salary := 50000;

BEGIN
DBMS_OUTPUT.PUT_LINE('The salary is: ' || :v_salary);
END;
/

Output:

csharp

7/8
 The salary is: 50000

5. Summary
PL/SQL Blocks can be Anonymous (temporary) or Named (Stored procedures,
Functions, Packages).

%TYPE inherits a column’s data type, while %ROWTYPE inherits an entire row
structure.

Constants hold unchangeable values, while Bind Variables help in parameterized

execution.

🚀 Next Steps: Want to learn about PL/SQL Control Structures (IF, LOOPS) or Exception
Handling?

8/8
Oracle SQL Topics Overview

Control Structures in PL/SQL

Control structures in PL/SQL allow you to control the flow of execution using conditional
statements and loops.

1. Conditional Statements in PL/SQL

Conditional statements allow you to execute different blocks of code based on certain
conditions.

1.1. IF-THEN Statement

Executes a block of statements if the condition is TRUE.

If the condition is FALSE or NULL, the block is skipped.

Syntax:

sql

IF condition THEN
-- Statements to execute if condition is true
END IF;

Example:

sql

DECLARE
v_salary NUMBER := 50000;
BEGIN
IF v_salary > 40000 THEN
DBMS_OUTPUT.PUT_LINE('High Salary');
END IF;

1/10
 END;
/

Output:

nginx

High Salary

1.2. IF-THEN-ELSE Statement

Executes one block of code if the condition is TRUE, and another if it is FALSE.

Syntax:
sql

IF condition THEN
-- Statements to execute if condition is true
ELSE
-- Statements to execute if condition is false
END IF;

Example:

sql

DECLARE
v_salary NUMBER := 35000;
BEGIN
IF v_salary > 40000 THEN
DBMS_OUTPUT.PUT_LINE('High Salary');
ELSE
DBMS_OUTPUT.PUT_LINE('Low Salary');
END IF;
END;
/

Output:

2/10
 nginx

Low Salary

1.3. IF-THEN-ELSIF Statement

Allows multiple conditions to be checked in sequence.

If one condition is TRUE, the subsequent conditions are skipped.

Syntax:

sql

IF condition1 THEN
-- Execute if condition1 is true
ELSIF condition2 THEN
-- Execute if condition2 is true
ELSIF condition3 THEN
-- Execute if condition3 is true
ELSE
-- Execute if no condition is true
END IF;

Example:

sql

DECLARE
v_marks NUMBER := 85;
BEGIN
IF v_marks >= 90 THEN
DBMS_OUTPUT.PUT_LINE('Grade: A');
ELSIF v_marks >= 80 THEN
DBMS_OUTPUT.PUT_LINE('Grade: B');
ELSIF v_marks >= 70 THEN
DBMS_OUTPUT.PUT_LINE('Grade: C');
ELSE
DBMS_OUTPUT.PUT_LINE('Grade: Fail');

3/10
 END IF;
END;
/

Output:

makefile

Grade: B

1.4. CASE Statement

Used when multiple conditions need to be checked, similar to a switch-case in other
programming languages.

1.4.1. Simple CASE Statement

Compares a single value with multiple conditions.

Syntax:

sql

CASE variable
WHEN value1 THEN statement1
WHEN value2 THEN statement2
ELSE default_statement
END;

Example:

sql

DECLARE
v_dept_no NUMBER := 10;
BEGIN
CASE v_dept_no
WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('HR Department');
WHEN 20 THEN DBMS_OUTPUT.PUT_LINE('Finance Department');

4/10
 WHEN 30 THEN DBMS_OUTPUT.PUT_LINE('IT Department');
ELSE DBMS_OUTPUT.PUT_LINE('Unknown Department');
END CASE;
END;
/

Output:

nginx

HR Department

1.4.2. Searched CASE Statement

Evaluates multiple Boolean expressions.

Syntax:

sql

CASE
WHEN condition1 THEN statement1
WHEN condition2 THEN statement2
ELSE default_statement
END;

Example:

sql

DECLARE
v_salary NUMBER := 70000;
BEGIN
CASE
WHEN v_salary > 80000 THEN DBMS_OUTPUT.PUT_LINE('Very High Salary');
WHEN v_salary > 60000 THEN DBMS_OUTPUT.PUT_LINE('High Salary');
ELSE DBMS_OUTPUT.PUT_LINE('Average Salary');
END CASE;

5/10
 END;
/

Output:

nginx

High Salary

2. Loops in PL/SQL
Loops are used to execute a block of code multiple times.

2.1. LOOP (Basic Loop)

Executes indefinitely until explicitly terminated using EXIT.

Syntax:

sql

LOOP
-- Statements
EXIT WHEN condition;
END LOOP;

Example:

sql

DECLARE
v_counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 5;

6/10
 END LOOP;
END;
/

Output:

makefile

Iteration: 1
Iteration: 2
Iteration: 3
Iteration: 4
Iteration: 5

2.2. WHILE Loop

Executes as long as the condition is TRUE.

Syntax:

sql

WHILE condition LOOP

-- Statements
END LOOP;

Example:
sql

DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('WHILE Loop Iteration: ' || v_counter);
v_counter := v_counter + 1;
END LOOP;

7/10
 END;
/

Output:

vbnet

WHILE Loop Iteration: 1

WHILE Loop Iteration: 2
WHILE Loop Iteration: 3
WHILE Loop Iteration: 4
WHILE Loop Iteration: 5

2.3. FOR Loop

Runs from a start value to an end value automatically.

Syntax:

sql

FOR counter_variable IN start_value..end_value LOOP

-- Statements
END LOOP;

Example:
sql

BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('FOR Loop Iteration: ' || i);
END LOOP;
END;
/

Output:

vbnet

8/10
 FOR Loop Iteration: 1
FOR Loop Iteration: 2
FOR Loop Iteration: 3
FOR Loop Iteration: 4
FOR Loop Iteration: 5

2.4. CONTINUE Statement

Skips the current iteration and moves to the next.

Example:
sql

BEGIN
FOR i IN 1..5 LOOP
IF i = 3 THEN
CONTINUE; -- Skip iteration 3
END IF;
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;
END;
/

Output:

makefile

Iteration: 1
Iteration: 2
Iteration: 4
Iteration: 5

3. Nested Loops & Conditional Loops

9/10
 Loops can be nested inside each other.

Example:

sql

BEGIN
FOR i IN 1..3 LOOP
FOR j IN 1..2 LOOP
DBMS_OUTPUT.PUT_LINE('Outer Loop: ' || i || ', Inner Loop: ' || j);
END LOOP;
END LOOP;
END;
/

Output:

pgsql

Outer Loop: 1, Inner Loop: 1

Outer Loop: 1, Inner Loop: 2
Outer Loop: 2, Inner Loop: 1
Outer Loop: 2, Inner Loop: 2
Outer Loop: 3, Inner Loop: 1
Outer Loop: 3, Inner Loop: 2

Summary
Conditional Statements: IF-THEN , IF-THEN-ELSE , CASE

Loops: LOOP , WHILE , FOR

CONTINUE Statement: Skips an iteration.

Nested Loops: Used for complex logic.

🚀 Next Steps: Do you want to learn about PL/SQL Exception Handling or Cursors?

10/10
Oracle SQL Topics Overview

Cursors in PL/SQL
A cursor in PL/SQL is a pointer to a result set of a query. Cursors allow you to fetch and
process rows one at a time.

Types of Cursors in PL/SQL

1. Implicit Cursors – Automatically created by Oracle for SELECT INTO, INSERT, UPDATE,
DELETE operations.

2. Explicit Cursors – Defined by the user to process multiple rows returned by a query.

3. Cursor FOR LOOP – A shorthand for using explicit cursors.

4. Parameterized Cursors – Cursors that accept parameters.

5. REF Cursors (Dynamic Cursors) – Used for handling dynamic queries.

1. Implicit Cursors
Automatically created by Oracle for DML statements and SELECT INTO queries.

Attributes:

%FOUND → Returns TRUE if a row is affected.

%NOTFOUND → Returns TRUE if no rows are affected.

%ROWCOUNT → Returns the number of rows affected.

%ISOPEN → Always FALSE (implicit cursors close automatically).

Example: Checking the number of records updated

sql

DECLARE
v_emp_id NUMBER := 101;

1/6
 BEGIN
UPDATE employees SET salary = salary + 5000 WHERE employee_id = v_emp_id;

IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee salary updated');
ELSE
DBMS_OUTPUT.PUT_LINE('No employee found with ID ' || v_emp_id);
END IF;

DBMS_OUTPUT.PUT_LINE('Rows Affected: ' || SQL%ROWCOUNT);

END;
/

2. Explicit Cursors
Used when a SELECT statement returns multiple rows.

Requires the following steps:

1. Declare the cursor.

2. Open the cursor.

3. Fetch the data from the cursor.

4. Close the cursor.

Example: Using an Explicit Cursor

sql

DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, salary FROM employees WHERE department_id =
10;

v_emp_id employees.employee_id%TYPE;
v_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN

2/6
 OPEN emp_cursor; -- Open the cursor

LOOP
FETCH emp_cursor INTO v_emp_id, v_name, v_salary; -- Fetch row
EXIT WHEN emp_cursor%NOTFOUND; -- Exit loop when no more rows

DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_name || ',

Salary: ' || v_salary);
END LOOP;

CLOSE emp_cursor; -- Close the cursor

END;
/

3. Cursor FOR LOOP

Simplifies explicit cursors by automatically handling OPEN, FETCH, and CLOSE
operations.

Example: Using Cursor FOR LOOP

sql

DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, salary FROM employees WHERE department_id =
10;
BEGIN
FOR emp_rec IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ', Name: ' ||
emp_rec.first_name || ', Salary: ' || emp_rec.salary);
END LOOP;
END;
/

✔ Advantage: No need to explicitly open, fetch, or close the cursor.

3/6
4. Parameterized Cursors
Cursors that accept parameters to filter data dynamically.

Example: Parameterized Cursor

sql

DECLARE
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT employee_id, first_name, salary FROM employees WHERE department_id =
p_dept_id;
BEGIN
FOR emp_rec IN emp_cursor(20) LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ', Name: ' ||
emp_rec.first_name || ', Salary: ' || emp_rec.salary);
END LOOP;
END;
/

✔ Advantage: Makes the cursor reusable with different input values.

5. REF Cursors (Dynamic Cursors)

REF CURSOR is a pointer to a query result set, used for dynamic SQL.

Can be opened for different queries at runtime.

Types of REF Cursors:

Strong REF Cursor – Tied to a specific structure.

Weak REF Cursor – Can return different structures.

Example: Using a REF CURSOR

sql

4/6
 DECLARE
TYPE emp_ref_cursor IS REF CURSOR; -- Define REF CURSOR type
v_emp_cursor emp_ref_cursor; -- Declare cursor variable
v_emp_id employees.employee_id%TYPE;
v_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN v_emp_cursor FOR SELECT employee_id, first_name, salary FROM employees
WHERE department_id = 30;

LOOP
FETCH v_emp_cursor INTO v_emp_id, v_name, v_salary;
EXIT WHEN v_emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_name || ',

Salary: ' || v_salary);
END LOOP;

CLOSE v_emp_cursor;
END;
/

✔ Advantage: REF cursors are flexible and can be passed to procedures/functions.

Summary
Cursor Type Description Use Case

Implicit Cursor Automatically created by Oracle for DML Simple queries

statements

Explicit Cursor Manually declared for handling multiple rows Fetching multiple
records

Cursor FOR LOOP Simplifies explicit cursors Read-only queries

Parameterized Cursors with input parameters Filtered queries

Cursor

REF Cursor Dynamic cursors for handling dynamic SQL Complex queries

5/6
✅ Next Steps: Do you want to learn about PL/SQL Exception Handling or Stored
Procedures & Functions? 🚀

6/6
Oracle SQL Topics Overview

PL/SQL Exception Handling

Exception Handling in PL/SQL allows us to handle runtime errors gracefully. It ensures that
when an error occurs, the program does not terminate abruptly but instead handles the
error in a controlled manner.

Types of Exceptions in PL/SQL

1. Predefined Exceptions – Built-in exceptions automatically raised by Oracle.

2. User-Defined Exceptions – Custom exceptions defined using EXCEPTION and raised

using RAISE or RAISE_APPLICATION_ERROR .

3. Exception Propagation – The way exceptions travel from inner blocks to outer blocks.

1. Predefined Exceptions in PL/SQL

Oracle provides several built-in exceptions that are automatically raised when specific errors
occur.

Exception Name Cause

NO_DATA_FOUND Query did not return any data.

TOO_MANY_ROWS Query returned more than one row in SELECT INTO .

ZERO_DIVIDE Division by zero error.

VALUE_ERROR Invalid data type or value error.

CURSOR_ALREADY_OPEN Tried to open an already open cursor.

1/6
Example: Handling NO_DATA_FOUND and TOO_MANY_ROWS
sql

DECLARE
v_emp_name employees.first_name%TYPE;
BEGIN
-- Selecting a single employee, but it might return no rows or multiple rows
SELECT first_name INTO v_emp_name FROM employees WHERE department_id = 999;

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: No employee found in this department.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Error: Query returned multiple employees. Use a
cursor.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
/

✔ Handling NO_DATA_FOUND prevents the program from terminating if no data is found.

✔ Handling TOO_MANY_ROWS ensures that we don’t accidentally fetch multiple rows into a
single variable.

Example: Handling ZERO_DIVIDE

sql

DECLARE
v_result NUMBER;
BEGIN
v_result := 10 / 0; -- This will cause a division by zero error

EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero.');

2/6
 END;
/

✔ Prevents division by zero errors from crashing the program.

2. User-Defined Exceptions
Sometimes, predefined exceptions are not enough, and we need to define our own custom
exceptions.

Steps to Handle User-Defined Exceptions

1. Declare the exception using EXCEPTION .

2. Raise the exception using RAISE .

3. Handle the exception in the EXCEPTION block.

Example: User-Defined Exception ( RAISE )

sql

DECLARE
v_salary employees.salary%TYPE;
e_low_salary EXCEPTION; -- Declare custom exception
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;

-- Check if salary is too low

IF v_salary < 5000 THEN
RAISE e_low_salary; -- Raise the custom exception
END IF;

EXCEPTION
WHEN e_low_salary THEN
DBMS_OUTPUT.PUT_LINE('Error: Salary is below minimum threshold.');

3/6
 END;
/

✔ If the employee's salary is below 5000, we raise a custom exception.

Example: Using RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR allows us to return a custom error message with an Oracle error
number (-20000 to -20999).

sql

DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 102;

IF v_salary < 5000 THEN

RAISE_APPLICATION_ERROR(-20001, 'Salary is below minimum allowed!');
END IF;

END;
/

✔ Throws an error like: ORA-20001: Salary is below minimum allowed!

3. Exception Propagation
If an exception occurs inside a nested block and is not handled there, it is propagated to
the outer block.

If the exception is not handled anywhere, it terminates the program.

4/6
Example: Exception Propagation

sql

DECLARE
v_salary NUMBER;
BEGIN
DECLARE
e_low_salary EXCEPTION;
BEGIN
-- Inner Block
SELECT salary INTO v_salary FROM employees WHERE employee_id = 103;

IF v_salary < 5000 THEN

RAISE e_low_salary; -- Raising the exception
END IF;

EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Inner Block: Too many employees found.');
-- No handler for e_low_salary here, so it will propagate to the outer block
END;

EXCEPTION
WHEN e_low_salary THEN
DBMS_OUTPUT.PUT_LINE('Outer Block: Salary is too low.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Outer Block: Unexpected error.');
END;
/

✔ If e_low_salary is raised in the inner block, and it is not handled there, it is passed to
the outer block.

Summary

5/6
 Feature Description

Predefined Exceptions Automatically raised errors like NO_DATA_FOUND , TOO_MANY_ROWS ,

ZERO_DIVIDE .

User-Defined Exceptions Custom exceptions declared using EXCEPTION and raised using
RAISE .

RAISE_APPLICATION_ERROR Allows defining custom error messages with error codes (-20000 to
-20999).

Exception Propagation If an exception is not handled in the inner block, it moves to the outer
block.

✅ Next Steps: Do you want to learn about Stored Procedures & Functions or Triggers in
PL/SQL? 🚀

6/6
Oracle SQL Topics Overview

PL/SQL Procedures and Functions

Procedures and Functions in PL/SQL are named PL/SQL blocks that encapsulate a sequence
of statements to perform a specific task. They enhance code reusability, modularity, and
maintainability.

1. Procedures in PL/SQL
A Procedure is a stored program that performs a specific task but does not return a value
directly.

Syntax: Creating a Procedure

sql

CREATE OR REPLACE PROCEDURE procedure_name (

parameter1 DATATYPE,
parameter2 DATATYPE
)
IS
-- Declarations
BEGIN
-- Executable Statements
EXCEPTION
-- Exception Handling
END procedure_name;
/

Example: Creating and Executing a Procedure

1/7
Problem Statement: Create a procedure that increases an employee's
salary by a given percentage.

Step 1: Create the Procedure

sql

CREATE OR REPLACE PROCEDURE increase_salary (

p_emp_id IN employees.employee_id%TYPE,
p_percentage IN NUMBER
)
IS
BEGIN
-- Updating salary
UPDATE employees
SET salary = salary + (salary * p_percentage / 100)
WHERE employee_id = p_emp_id;

-- Commit the transaction

COMMIT;
END increase_salary;
/

✔ This procedure updates the employee’s salary by a given percentage.

Step 2: Execute the Procedure

sql

BEGIN
increase_salary(101, 10); -- Increases salary by 10% for employee 101
END;
/

✔ The procedure is executed using an anonymous block.

✔ If an error occurs, the procedure can be modified with exception handling.

2/7
2. Functions in PL/SQL
A Function is similar to a procedure but returns a value. It is used when we need a
computed result.

Syntax: Creating a Function

sql

CREATE OR REPLACE FUNCTION function_name (

parameter1 DATATYPE
) RETURN return_type
IS
-- Declarations
BEGIN
-- Computation
RETURN value;
EXCEPTION
-- Exception Handling
END function_name;
/

Example: Creating and Executing a Function

Problem Statement: Create a function that returns the total salary of
an employee.

Step 1: Create the Function

sql

CREATE OR REPLACE FUNCTION get_total_salary (

p_emp_id IN employees.employee_id%TYPE
) RETURN NUMBER
IS
v_total_salary NUMBER;
BEGIN

3/7
 -- Fetching total salary
SELECT salary INTO v_total_salary
FROM employees
WHERE employee_id = p_emp_id;

RETURN v_total_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL; -- If employee not found, return NULL
END get_total_salary;
/

✔ This function retrieves and returns the salary of an employee.

Step 2: Execute the Function

sql

DECLARE
v_salary NUMBER;
BEGIN
v_salary := get_total_salary(101); -- Fetch salary for employee 101
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_salary);
END;
/

✔ A function must always return a value and is used within SELECT , PL/SQL blocks, or
RETURN statements.

3. Parameter Modes in Procedures and

Functions
PL/SQL supports three types of parameters:

4/7
 Parameter Mode Description

IN Used to pass input values to the procedure/function. Default mode.

OUT Used to return output values from a procedure.

IN OUT Used to pass input values and return modified values.

Example: Using IN, OUT, and IN OUT Parameters

sql

CREATE OR REPLACE PROCEDURE employee_bonus (

p_emp_id IN employees.employee_id%TYPE,
p_bonus OUT NUMBER,
p_salary IN OUT NUMBER
)
IS
BEGIN
-- Calculate Bonus as 10% of salary
p_bonus := p_salary * 0.10;

-- Increase Salary by 5%
p_salary := p_salary * 1.05;
END employee_bonus;
/

✔ IN : p_emp_id is passed as input.

✔ OUT : p_bonus is computed and returned.
✔ IN OUT : p_salary is modified and returned.

Executing the Procedure

sql

DECLARE
v_salary NUMBER := 50000;
v_bonus NUMBER;
BEGIN
employee_bonus(101, v_bonus, v_salary);

5/7
 DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
DBMS_OUTPUT.PUT_LINE('Updated Salary: ' || v_salary);
END;
/

✔ The salary is modified inside the procedure and reflected in the calling block.

4. Difference Between Procedures and

Functions
Feature Procedure Function

Purpose Performs an action (e.g., insert, update, Computes and returns a value
delete)

Return Value Does not return a value Must return a value

Usage in SQL Cannot be used in SELECT Can be used in SELECT

Queries

Parameter Modes Supports IN , OUT , IN OUT Supports only IN

Commit/Rollback Can include COMMIT , ROLLBACK Cannot include COMMIT ,

ROLLBACK

Conclusion
✔ Procedures are used when we need to perform an action (e.g., update salaries, insert
records).
✔ Functions are used when we need to compute and return a value (e.g., get total salary).
✔ Parameter modes ( IN , OUT , IN OUT ) allow flexibility in passing values.

6/7
✅ Next Steps: Do you want to cover PL/SQL Triggers or PL/SQL Packages? 🚀

7/7
Oracle SQL Topics Overview

PL/SQL Triggers
What is a Trigger?
A trigger is a PL/SQL program that is automatically executed when a specific event occurs in
the database. It is mainly used to enforce business rules, automate actions, and maintain
data integrity.

1. Types of Triggers in PL/SQL

A. Based on Execution Timing
Trigger Type Description

BEFORE Trigger Executes before an INSERT, UPDATE, or DELETE operation.

AFTER Trigger Executes after an INSERT, UPDATE, or DELETE operation.

INSTEAD OF Trigger Used on views to handle modifications that are not directly possible.

B. Based on Trigger Scope

Trigger Type Description

Row-Level Trigger Executes once for each row affected by the DML statement.

Statement-Level Executes once per SQL statement, regardless of how many rows are
Trigger affected.

C. Based on Event Type

1/7
 Trigger Type Description

DML Triggers Fire on INSERT , UPDATE , or DELETE operations.

DDL Triggers Fire on CREATE , ALTER , DROP statements.

Database Event Triggers Fire on system events like LOGON , LOGOFF , STARTUP , SHUTDOWN .

2. Creating Different Types of Triggers

A. BEFORE & AFTER Triggers (DML Triggers)
DML triggers are fired before or after an INSERT , UPDATE , or DELETE operation.

Example: BEFORE INSERT Trigger

sql

CREATE OR REPLACE TRIGGER before_insert_employee

BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
-- Assign a default department if not provided
IF :NEW.department_id IS NULL THEN
:NEW.department_id := 10; -- Default department
END IF;
END;
/

✔ This trigger ensures that every new employee gets a default department_id = 10 if it's not
provided.

Example: AFTER UPDATE Trigger

sql

2/7
 CREATE OR REPLACE TRIGGER after_update_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary updated for Employee ID: ' || :NEW.employee_id);
END;
/

✔ This trigger prints a message after updating an employee's salary.

B. INSTEAD OF Trigger (For Views)

Since INSERT , UPDATE , and DELETE cannot be performed directly on some views, we use
INSTEAD OF triggers.

Example: INSTEAD OF Trigger on a View

sql

CREATE OR REPLACE VIEW employee_view AS

SELECT employee_id, first_name, last_name, department_id FROM employees;

CREATE OR REPLACE TRIGGER instead_of_update_view

INSTEAD OF UPDATE ON employee_view
FOR EACH ROW
BEGIN
UPDATE employees
SET first_name = :NEW.first_name,
last_name = :NEW.last_name
WHERE employee_id = :OLD.employee_id;
END;
/

✔ This allows updating employee_view while updating the employees table.

3/7
C. Row-Level vs. Statement-Level Triggers
Row-Level Trigger: Executes once per affected row. ( FOR EACH ROW must be used.)

Statement-Level Trigger: Executes once per SQL statement, regardless of how many
rows are affected.

Example: Row-Level AFTER DELETE Trigger

sql

CREATE OR REPLACE TRIGGER row_level_delete_trigger

AFTER DELETE ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Deleted Employee ID: ' || :OLD.employee_id);
END;
/

✔ If 3 rows are deleted, this trigger fires 3 times.

Example: Statement-Level AFTER DELETE Trigger

sql

CREATE OR REPLACE TRIGGER statement_level_delete_trigger

AFTER DELETE ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Delete operation performed on employees table.');
END;
/

✔ If 3 rows are deleted, this trigger fires only once.

D. DDL Triggers (Triggers on CREATE, ALTER, DROP)

4/7
DDL triggers are useful for security and auditing purposes.

Example: Preventing Table Drops

sql

CREATE OR REPLACE TRIGGER prevent_table_drop

BEFORE DROP ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Dropping tables is not allowed!');
END;
/

✔ This prevents users from dropping tables in the schema.

E. Database Event Triggers

These triggers fire when specific system events occur, like user login/logout.

Example: Tracking User Logins

sql

CREATE OR REPLACE TRIGGER log_user_login

AFTER LOGON ON DATABASE
BEGIN
INSERT INTO user_logins(user_name, login_time)
VALUES (USER, SYSDATE);
END;
/

✔ Logs every user login in the user_logins table.

3. Mutating Table Errors and Solutions

5/7
What is a Mutating Table Error?
A mutating table error occurs when a trigger tries to read/write from the same table that
triggered it.

Example: Trigger Causing a Mutating Table Error

sql

CREATE OR REPLACE TRIGGER check_salary

AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
v_avg_salary NUMBER;
BEGIN
-- Fetching average salary (Causes error!)
SELECT AVG(salary) INTO v_avg_salary FROM employees;
END;
/

✔ This will fail because the employees table is being modified while being queried.

Solution: Using an Autonomous Transaction

An autonomous transaction allows the trigger to execute a separate transaction.

sql

CREATE OR REPLACE TRIGGER check_salary_fixed

AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_avg_salary NUMBER;
BEGIN
-- Running query in a separate transaction
SELECT AVG(salary) INTO v_avg_salary FROM employees;
COMMIT;

6/7
 END;
/

✔ This prevents the mutating table error by isolating the transaction.

4. Summary of Triggers
Trigger Type Usage

BEFORE Trigger Used to validate or modify data before a DML operation.

AFTER Trigger Used to log or audit changes after a DML operation.

INSTEAD OF Trigger Used on views to allow modifications.

Row-Level Trigger Fires once per row affected by a statement.

Statement-Level Trigger Fires once per statement execution.

DDL Trigger Fires on CREATE , DROP , or ALTER commands.

Database Event Trigger Fires on events like user login/logout.

✅ Next Steps: Do you want to cover PL/SQL Packages or any other topic? 🚀

7/7
Oracle SQL Topics Overview

PL/SQL Packages
1. What is a Package in PL/SQL?
A package is a collection of related procedures, functions, variables, cursors, and
exceptions grouped together under a common name. It allows for modular programming,
code reusability, and better performance by pre-compiling the stored procedures.

🔹 Benefits of Using Packages

✔ Encapsulation: Hides implementation details and exposes only necessary
procedures/functions.
✔ Modularity: Groups related procedures and functions together.
✔ Improved Performance: Caching allows faster execution of package elements.
✔ Better Code Organization: Enhances readability and maintainability.

2. Components of a Package
A package consists of two parts:

1. Package Specification

Acts as a header file that defines procedures, functions, cursors, and variables.

It does not contain implementation, only declarations.

2. Package Body

Contains the actual implementation of procedures and functions declared in the

specification.

Can also contain private procedures and variables that are not accessible outside
the package.

1/7
3. Creating a PL/SQL Package
A. Package Specification (Declaration)

sql

CREATE OR REPLACE PACKAGE emp_package AS

-- Public Procedure
PROCEDURE insert_employee(p_id NUMBER, p_name VARCHAR2, p_salary NUMBER);

-- Public Function
FUNCTION get_employee_salary(p_id NUMBER) RETURN NUMBER;
END emp_package;
/

✔ This declares a procedure and a function but does not implement them.

B. Package Body (Implementation)

sql

CREATE OR REPLACE PACKAGE BODY emp_package AS

-- Procedure Implementation
PROCEDURE insert_employee(p_id NUMBER, p_name VARCHAR2, p_salary NUMBER) IS
BEGIN
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (p_id, p_name, p_salary);
COMMIT;
END insert_employee;

-- Function Implementation
FUNCTION get_employee_salary(p_id NUMBER) RETURN NUMBER IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_id;
RETURN v_salary;
END get_employee_salary;

2/7
 END emp_package;
/

✔ The package body contains the actual implementation of the procedures and functions.

4. Using a Package
A. Calling a Procedure from the Package

sql

BEGIN
emp_package.insert_employee(101, 'John Doe', 50000);
END;
/

✔ This calls the insert_employee procedure to insert a new record.

B. Calling a Function from the Package

sql

DECLARE
v_salary NUMBER;
BEGIN
v_salary := emp_package.get_employee_salary(101);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

✔ This calls the get_employee_salary function to fetch and print an employee's salary.

5. Public and Private Procedures in Packages

3/7
🔹 Public vs. Private Procedures
Public Procedures/Functions: Declared in the package specification and can be
accessed outside the package.

Private Procedures/Functions: Defined only in the package body and cannot be

accessed externally.

Example: Private Procedure

sql

CREATE OR REPLACE PACKAGE BODY emp_package AS

-- Private Procedure (Only accessible inside the package)

PROCEDURE log_activity(p_message VARCHAR2) IS
BEGIN
INSERT INTO activity_log (log_message, log_date) VALUES (p_message,
SYSDATE);
COMMIT;
END log_activity;

PROCEDURE insert_employee(p_id NUMBER, p_name VARCHAR2, p_salary NUMBER) IS

BEGIN
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (p_id, p_name, p_salary);
log_activity('Inserted Employee: ' || p_name);
COMMIT;
END insert_employee;

END emp_package;
/

✔ The log_activity procedure cannot be accessed outside the package.

6. Package Initialization and Overloading

🔹 Package Initialization
A package can initialize variables and perform actions when it is first referenced.

4/7
Example: Initializing a Package

sql

CREATE OR REPLACE PACKAGE BODY emp_package AS

-- Global Variable
g_counter NUMBER := 0;

-- Package Initialization Block (Executes Once per Session)

BEGIN
SELECT COUNT(*) INTO g_counter FROM employees;
END emp_package;
/

✔ The g_counter variable is initialized when the package is first used.

🔹 Overloading Procedures and Functions

PL/SQL allows multiple procedures or functions with the same name but different
parameter types.

Example: Overloading a Procedure

sql

CREATE OR REPLACE PACKAGE emp_package AS

PROCEDURE insert_employee(p_id NUMBER, p_name VARCHAR2, p_salary NUMBER);
PROCEDURE insert_employee(p_id NUMBER, p_name VARCHAR2, p_salary NUMBER, p_dept
NUMBER);
END emp_package;
/

✔ The correct procedure version is called based on the number of arguments.

7. Package State Management

5/7
🔹 Stateful vs. Stateless Packages
Stateless: Does not maintain variable values between calls.

Stateful: Maintains global variables across multiple calls within a session.

Example: Using a Package State

sql

CREATE OR REPLACE PACKAGE BODY emp_package AS

-- Global Counter
g_call_count NUMBER := 0;

PROCEDURE insert_employee(p_id NUMBER, p_name VARCHAR2, p_salary NUMBER) IS

BEGIN
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (p_id, p_name, p_salary);

-- Increment counter
g_call_count := g_call_count + 1;
END insert_employee;

FUNCTION get_call_count RETURN NUMBER IS

BEGIN
RETURN g_call_count;
END get_call_count;

END emp_package;
/

✔ g_call_count maintains its value across multiple procedure calls.

8. Summary of PL/SQL Packages

Feature Description

Encapsulation Hides implementation details, only exposes necessary procedures.

Public & Private Public procedures are declared in the specification, private procedures are
Procedures defined only in the body.

6/7
 Feature Description

Initialization Runs once when the package is first referenced.

Overloading Allows multiple procedures/functions with the same name but different
parameters.

State Management Can maintain state across multiple calls using global variables.

✅ Next Steps: Do you want to explore PL/SQL Collections (Records,

VARRAYs, Nested Tables, Associative Arrays) next? 🚀

7/7
Oracle SQL Topics Overview

PL/SQL Collections
PL/SQL collections allow us to store and manipulate multiple elements as a single unit,
similar to arrays in other programming languages. Collections improve performance and
simplify handling of large datasets in procedures, functions, and loops.

1. Types of Collections in PL/SQL

PL/SQL supports three types of collections:

Collection Type Key Features

Associative Arrays (Index-By Key-value pairs, indexed by numbers or strings (like a hash
Tables) table).

Nested Tables Unordered collection, stored in the database, expandable

dynamically.

VARRAYs (Variable-Size Arrays) Fixed-size array, stored in the database, maintains order.

2. Associative Arrays (Index-By Tables)

Associative Arrays store key-value pairs and can be indexed using either integers or strings.

Example: Using Associative Arrays

sql

DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
employees emp_table;
BEGIN
-- Assign values using index
employees(1) := 'John';

1/6
 employees(2) := 'Alice';
employees(3) := 'Bob';

-- Access and print values

DBMS_OUTPUT.PUT_LINE('Employee 1: ' || employees(1));
DBMS_OUTPUT.PUT_LINE('Employee 2: ' || employees(2));
END;
/

✔ Associative Arrays do not require initialization and are memory-efficient.

3. Nested Tables
A Nested Table is a collection that can be stored in a database column and modified
dynamically.

Example: Using Nested Tables

sql

DECLARE
TYPE num_table IS TABLE OF NUMBER;
numbers num_table := num_table(10, 20, 30);
BEGIN
-- Extend and add values dynamically
numbers.EXTEND;
numbers(4) := 40;

-- Access and print values

DBMS_OUTPUT.PUT_LINE('First Element: ' || numbers(1));
DBMS_OUTPUT.PUT_LINE('Last Element: ' || numbers(numbers.LAST));
END;
/

✔ Nested Tables are flexible, support bulk operations, and can be stored in the database.

2/6
4. VARRAYs (Variable-Size Arrays)
A VARRAY is a fixed-size array stored as a whole in the database.

Example: Using VARRAYs

sql

DECLARE
TYPE emp_varray IS VARRAY(5) OF VARCHAR2(50);
employees emp_varray := emp_varray('John', 'Alice', 'Bob');
BEGIN
-- Access and print values
DBMS_OUTPUT.PUT_LINE('First Employee: ' || employees(1));
DBMS_OUTPUT.PUT_LINE('Last Employee: ' || employees(employees.LAST));
END;
/

✔ VARRAYs are ordered collections and store data as a single object in the database.

5. Collection Methods in PL/SQL

PL/SQL provides methods to manipulate collections.

Method Description

EXTEND Adds elements dynamically (only for Nested Tables and VARRAYs).

DELETE Removes elements from the collection.

COUNT Returns the total number of elements.

FIRST Returns the first index of the collection.

LAST Returns the last index of the collection.

Example: Using Collection Methods

sql

DECLARE
TYPE num_table IS TABLE OF NUMBER;
numbers num_table := num_table(10, 20, 30);

3/6
 BEGIN
-- Adding new elements dynamically
numbers.EXTEND;
numbers(4) := 40;

-- Deleting an element
numbers.DELETE(2); -- Deletes second element

-- Display collection details

DBMS_OUTPUT.PUT_LINE('Total Count: ' || numbers.COUNT);
DBMS_OUTPUT.PUT_LINE('First Element: ' || numbers(numbers.FIRST));
DBMS_OUTPUT.PUT_LINE('Last Element: ' || numbers(numbers.LAST));
END;
/

✔ DELETE removes an element but does not shift indices.

6. Bulk Collect & FORALL (Performance Optimization)

PL/SQL Bulk Collect and FORALL optimize data retrieval and modification by reducing
context switches between SQL and PL/SQL engines.

🔹 BULK COLLECT: Fetching Multiple Rows Efficiently

Instead of retrieving rows one by one, BULK COLLECT retrieves all rows at once, improving
performance.

sql

DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emp_list emp_table;
BEGIN
-- Fetch all employees at once
SELECT * BULK COLLECT INTO emp_list FROM employees;

-- Loop through the collection

FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_list(i).employee_name);
END LOOP;

4/6
 END;
/

✔ Faster than fetching rows one by one using a loop.

🔹 FORALL: Efficient Bulk DML Operations

Instead of executing multiple SQL statements in a loop, FORALL sends all statements at
once, improving performance.

sql

DECLARE
TYPE num_table IS TABLE OF NUMBER;
emp_ids num_table := num_table(101, 102, 103);
BEGIN
-- Bulk delete operation
FORALL i IN emp_ids.FIRST .. emp_ids.LAST
DELETE FROM employees WHERE employee_id = emp_ids(i);

COMMIT;
END;
/

✔ FORALL reduces the number of context switches, making DML operations 10x faster.

7. Summary of PL/SQL Collections

Feature Associative Arrays Nested Tables VARRAYs

Index Type Integer/String Sequential Numbers Sequential Numbers

Storage Memory Only Memory + Database Memory + Database

Size Limit Unlimited Unlimited Fixed Size

Dynamic? Yes Yes No (Fixed Size)

Database Storage? No Yes Yes

5/6
 Feature Associative Arrays Nested Tables VARRAYs

Best Use Case Key-value lookups Dynamic lists Fixed-length lists

8. When to Use Which Collection?

Use Associative Arrays for fast in-memory lookups (like a HashMap).

Use Nested Tables for storing dynamic data that needs to be modified frequently.

Use VARRAYs when the size is fixed and order matters.

✅ Next Steps: Do you want to explore PL/SQL Records (Composite

Data Types) next? 🚀

6/6
Oracle SQL Topics Overview

Dynamic SQL in PL/SQL

Dynamic SQL allows us to construct and execute SQL statements at runtime instead of
writing static SQL statements in PL/SQL. It is useful when the structure of the SQL statement
is not known until execution, such as dynamically building queries based on user input.

1. Why Use Dynamic SQL?

When table names, column names, or conditions are determined at runtime.

When performing DDL operations ( CREATE , DROP , ALTER ).

When executing dynamic SELECT , INSERT , UPDATE , or DELETE queries.

When working with privileges that require execution in a different schema.

2. Methods for Writing Dynamic SQL in PL/SQL

PL/SQL provides two main ways to execute dynamic SQL:

1. EXECUTE IMMEDIATE – Used for simple dynamic queries.

2. DBMS_SQL Package – Used for complex queries with dynamic bindings.

3. Using EXECUTE IMMEDIATE

EXECUTE IMMEDIATE is the simplest and most common method for running dynamic SQL.

🔹 Syntax:
sql

1/7
 EXECUTE IMMEDIATE 'SQL_STATEMENT';

🔹 Example 1: Executing a DDL Statement

sql

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE temp_table (id NUMBER, name VARCHAR2(50))';
DBMS_OUTPUT.PUT_LINE('Table Created Successfully');
END;
/

✔ Used when performing DDL operations dynamically.

🔹 Example 2: Executing a Dynamic SELECT Query

sql

DECLARE
v_emp_name employees.employee_name%TYPE;
v_sql VARCHAR2(200);
BEGIN
-- Construct the query dynamically
v_sql := 'SELECT employee_name FROM employees WHERE employee_id = 101';

-- Execute and fetch the result

EXECUTE IMMEDIATE v_sql INTO v_emp_name;

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);

END;
/

✔ EXECUTE IMMEDIATE ... INTO is used when fetching a single value.

🔹 Example 3: Using USING for Dynamic SQL with Bind Variables

2/7
Using USING prevents SQL Injection and improves performance.

sql

DECLARE
v_emp_name employees.employee_name%TYPE;
BEGIN
EXECUTE IMMEDIATE
'SELECT employee_name FROM employees WHERE employee_id = :1'
INTO v_emp_name
USING 101; -- Binding Parameter

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);

END;
/

✔ Benefits of Bind Variables:

Improves performance (SQL is parsed only once).

Prevents SQL Injection attacks.

🔹 Example 4: Executing INSERT , UPDATE , and DELETE Dynamically

sql

BEGIN
-- Dynamic INSERT
EXECUTE IMMEDIATE 'INSERT INTO employees (employee_id, employee_name) VALUES
(105, ''John'')';

-- Dynamic UPDATE
EXECUTE IMMEDIATE 'UPDATE employees SET employee_name = ''Alice'' WHERE
employee_id = 105';

-- Dynamic DELETE
EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id = 105';

COMMIT;

3/7
 END;
/

✔ Used when table names or column names change dynamically.

4. Using DBMS_SQL Package for Complex Queries

The DBMS_SQL package is useful when:

The number of columns or types is unknown at compile-time.

A query requires dynamic column bindings.

🔹 Steps for Using DBMS_SQL

1. Create a cursor using DBMS_SQL.OPEN_CURSOR .

2. Parse the SQL statement using DBMS_SQL.PARSE .

3. Bind variables (if needed) using DBMS_SQL.BIND_VARIABLE .

4. Execute the SQL statement using DBMS_SQL.EXECUTE .

5. Fetch results (for queries) using DBMS_SQL.FETCH_ROWS .

6. Close the cursor using DBMS_SQL.CLOSE_CURSOR .

🔹 Example: Using DBMS_SQL to Handle Dynamic Queries

sql

DECLARE
v_cursor NUMBER;
v_sql VARCHAR2(200);
v_emp_name VARCHAR2(100);
v_emp_id NUMBER := 101;
BEGIN
-- Open a cursor for execution
v_cursor := DBMS_SQL.OPEN_CURSOR;

4/7
 -- Define the SQL statement
v_sql := 'SELECT employee_name FROM employees WHERE employee_id = :1';

-- Parse the SQL statement

DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

-- Bind the variable

DBMS_SQL.BIND_VARIABLE(v_cursor, ':1', v_emp_id);

-- Define the return variable

DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_emp_name, 100);

-- Execute the SQL

DBMS_SQL.EXECUTE(v_cursor);

-- Fetch the result

IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_emp_name);
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END IF;

-- Close the cursor

DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(v_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor);
END IF;
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

✔ DBMS_SQL is useful for handling unknown query structures dynamically.

5. Key Differences Between EXECUTE IMMEDIATE and

DBMS_SQL

5/7
 Feature EXECUTE IMMEDIATE DBMS_SQL

Simplicity Simple, easy to use Complex, requires multiple steps

Performance Faster, recommended for most use Slightly slower due to additional
cases parsing

Handling Dynamic Best for known structures with few Useful for completely dynamic
Queries columns queries

Bind Variables Supports bind variables directly Requires explicit BIND_VARIABLE

Cursor Management Implicitly handled Explicit cursor handling needed

6. When to Use Dynamic SQL?

✅ Use EXECUTE IMMEDIATE when:

Query structure is known at runtime.

Performing DDL operations dynamically ( CREATE , DROP ).

Executing simple queries with few dynamic parameters.

✅ Use DBMS_SQL when:

Query structure is not known until runtime.

You need fully dynamic query execution (variable columns).

You require dynamic binding of multiple columns.

7. Best Practices for Using Dynamic SQL

✔ Use Bind Variables ( USING ) instead of string concatenation to prevent SQL Injection.
✔ Prefer EXECUTE IMMEDIATE over DBMS_SQL for simple queries.
✔ Use DBMS_SQL only for complex dynamic queries where column count or types are
unknown.
✔ Always close cursors when using DBMS_SQL to prevent memory leaks.

6/7
✅ Next Steps: Do you want to explore PL/SQL Records & Composite
Data Types next? 🚀

7/7
Oracle SQL Topics Overview

Advanced PL/SQL Features

PL/SQL provides several advanced features that enhance its capabilities for handling
transactions, object-oriented programming, integration with XML/JSON, and interoperability
with Java. Below are some of the key advanced topics in PL/SQL.

1. Autonomous Transactions in PL/SQL

🔹 What is an Autonomous Transaction?
An autonomous transaction is an independent transaction that runs separately from the
main (parent) transaction. It can commit or roll back changes without affecting the parent
transaction.

✅ Use Cases:
Logging information into an audit table without affecting the main transaction.

Committing changes inside a trigger, even if the main transaction rolls back.

Exception handling to store error details separately.

🔹 How to Define an Autonomous Transaction?

Use the PRAGMA AUTONOMOUS_TRANSACTION directive inside the block.

sql

CREATE OR REPLACE PROCEDURE log_error(p_error_msg VARCHAR2)

IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log (error_message, log_time)
VALUES (p_error_msg, SYSTIMESTAMP);

COMMIT; -- Required in an autonomous transaction

1/7
 END;
/

✔ This logs an error message in error_log without affecting the main transaction.

🔹 Example: Using Autonomous Transactions in a Trigger

sql

CREATE OR REPLACE TRIGGER trg_audit_employee

AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_table (employee_id, action_time, action_type)
VALUES (:NEW.employee_id, SYSTIMESTAMP, 'INSERT/UPDATE');

COMMIT; -- Commit in an autonomous transaction

END;
/

✔ The trigger logs audit details independently of the main transaction.

2. Using PL/SQL with Object Types

PL/SQL supports object-oriented programming (OOP) with user-defined object types.

🔹 Defining an Object Type

sql

CREATE OR REPLACE TYPE emp_obj AS OBJECT (

emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER,
MEMBER FUNCTION get_salary RETURN NUMBER
);
/

2/7
✔ This creates an object type emp_obj with a function get_salary .

🔹 Implementing Member Functions

sql

CREATE OR REPLACE TYPE BODY emp_obj AS

MEMBER FUNCTION get_salary RETURN NUMBER IS
BEGIN
RETURN salary;
END;
END;
/

✔ This implements the function inside the object.

🔹 Using Objects in PL/SQL

sql

DECLARE
emp emp_obj := emp_obj(101, 'John', 50000);
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary: ' || emp.get_salary());
END;
/

✔ Objects help in modeling real-world entities like Employees, Orders, Products, etc.

3. Using PL/SQL with XML and JSON

PL/SQL supports working with XML and JSON data, which is crucial for modern applications.

🔹 Handling XML in PL/SQL

PL/SQL provides the DBMS_XMLGEN and DBMS_XMLPARSER packages to generate and parse
XML.

Converting SQL Query Results to XML

sql

3/7
 SELECT XMLELEMENT("Employee",
XMLFOREST(employee_id AS "ID", employee_name AS "Name")
) AS xml_output
FROM employees;

✔ Generates XML output like:

xml

<Employee>
<ID>101</ID>
<Name>John</Name>
</Employee>

🔹 Handling JSON in PL/SQL

PL/SQL supports JSON manipulation using JSON_OBJECT , JSON_ARRAYAGG , and JSON_QUERY .

Generating JSON from SQL Data

sql

SELECT JSON_OBJECT(
'Employee_ID' VALUE employee_id,
'Employee_Name' VALUE employee_name
) AS json_output
FROM employees;

✔ Generates JSON output like:

json

{"Employee_ID":101, "Employee_Name":"John"}

Parsing JSON Data in PL/SQL

sql

4/7
 DECLARE
v_json CLOB := '{"Employee_ID":101, "Employee_Name":"John"}';
v_emp_id NUMBER;
v_emp_name VARCHAR2(100);
BEGIN
SELECT JSON_VALUE(v_json, '$.Employee_ID'),
JSON_VALUE(v_json, '$.Employee_Name')
INTO v_emp_id, v_emp_name
FROM dual;

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id);

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END;
/

✔ Extracts JSON values into PL/SQL variables.

4. Calling Java Code from PL/SQL

PL/SQL allows calling Java methods inside stored procedures and functions.

✅ Use Cases:
Performing complex calculations using Java.

Interacting with external APIs from PL/SQL.

Using Java libraries inside Oracle Database.

🔹 Steps to Call Java from PL/SQL

1. Write Java Code inside Oracle.

2. Load Java Code into the Database.

3. Create a PL/SQL Wrapper to call Java methods.

🔹 Step 1: Create a Java Class

5/7
 java

public class MathOperations {

public static int square(int num) {
return num * num;
}
}

✔ This Java class defines a method square() .

🔹 Step 2: Load Java Code into Oracle

Use LOADJAVA to store the Java class inside the database:

sh

loadjava -user scott/tiger@orcl -resolve MathOperations.java

🔹 Step 3: Create a PL/SQL Wrapper for Java

sql

CREATE OR REPLACE FUNCTION get_square(n NUMBER) RETURN NUMBER

AS LANGUAGE JAVA
NAME 'MathOperations.square(int) return int';
/

✔ This maps the Java method to a PL/SQL function.

🔹 Step 4: Call the Java Function in PL/SQL

sql

6/7
 DECLARE
v_result NUMBER;
BEGIN
v_result := get_square(5);
DBMS_OUTPUT.PUT_LINE('Square: ' || v_result);
END;
/

✔ The PL/SQL function calls Java, and prints:

makefile

Square: 25

🚀 Summary
Feature Use Case Key Benefit

Autonomous Logging, audit tables, exception Runs independent transactions

Transactions handling inside PL/SQL

PL/SQL with Object Modeling real-world entities Supports OOP concepts inside
Types PL/SQL

PL/SQL with Working with APIs, data Supports structured and semi-
XML/JSON exchange structured data

Calling Java from Complex computations, external Uses Java's powerful libraries in
PL/SQL API calls PL/SQL

✅ Next Steps: Do you want to explore PL/SQL Performance Tuning

next? 🚀

7/7
Oracle SQL Topics Overview

🚀
PL/SQL Performance Tuning and Best
Practices
Performance tuning is essential for optimizing PL/SQL execution speed and reducing
resource consumption. Below are key techniques and best practices for enhancing PL/SQL
performance.

1. PL/SQL Optimization Techniques

Optimizing PL/SQL involves improving SQL execution, reducing loop iterations, and
minimizing context switches.

✅ Key Optimization Strategies:

1. Use Efficient Queries – Avoid unnecessary joins and subqueries.

2. Use Bulk Processing – Use BULK COLLECT and FORALL for large data sets.

3. Use Proper Indexing – Ensure the use of B-tree or bitmap indexes.

4. Minimize PL/SQL to SQL Context Switching – Avoid calling SQL statements in loops.

5. Use Pipelined Table Functions – Improve performance for large datasets.

6. Use Proper Data Types – Match PL/SQL and SQL data types to avoid implicit
conversions.

2. Reducing Context Switching Between SQL and PL/SQL

✅ What is Context Switching?
PL/SQL and SQL are separate engines.

Frequent switching between SQL and PL/SQL (like running a loop for inserts) reduces
performance.

1/6
🔹 Example of Bad Context Switching (Slow Performance)
sql

DECLARE
v_name employees.employee_name%TYPE;
BEGIN
FOR rec IN (SELECT employee_name FROM employees) LOOP
v_name := rec.employee_name;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
END;
/

🔻 Why is this bad?

Fetches one row at a time → too many SQL-to-PL/SQL context switches.

Inefficient for large data sets.

✅ Solution: Use BULK COLLECT to Reduce Context Switching

🚀 BULK COLLECT improves performance by fetching multiple rows at once!
sql

DECLARE
TYPE emp_name_tbl IS TABLE OF employees.employee_name%TYPE;
v_names emp_name_tbl;
BEGIN
SELECT employee_name BULK COLLECT INTO v_names FROM employees;

FOR i IN 1..v_names.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
/

✔ Reduces context switching by fetching all rows in a single operation.

2/6
3. Using BULK COLLECT and FORALL for Performance
🔹 BULK COLLECT – Faster Data Retrieval
✅ How does BULK COLLECT work?
Instead of retrieving one row at a time, BULK COLLECT retrieves all rows at once into a
collection.

sql

DECLARE
TYPE emp_tbl IS TABLE OF employees%ROWTYPE;
v_employees emp_tbl;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
DBMS_OUTPUT.PUT_LINE('Total Employees Fetched: ' || v_employees.COUNT);
END;
/

✔ Retrieves entire result set in one go, reducing context switches.

🔹 FORALL – Faster Bulk DML Operations

✅ How does FORALL work?
FORALL executes DML operations (INSERT, UPDATE, DELETE) in bulk, rather than one at
a time.

sql

DECLARE
TYPE emp_id_tbl IS TABLE OF employees.employee_id%TYPE;
v_emp_ids emp_id_tbl;
BEGIN
-- Collect all Employee IDs
SELECT employee_id BULK COLLECT INTO v_emp_ids FROM employees WHERE
department_id = 10;

-- Update salaries in bulk

3/6
 FORALL i IN v_emp_ids.FIRST .. v_emp_ids.LAST
UPDATE employees SET salary = salary * 1.10 WHERE employee_id =
v_emp_ids(i);

COMMIT;
END;
/

✔ Runs all updates in one batch, reducing SQL-to-PL/SQL context switches.

4. Indexing Strategies for PL/SQL Queries

✅ Why are Indexes Important?
Indexes speed up query performance by reducing full table scans.

Choose the right index type based on query patterns.

🔹 Types of Indexes and When to Use Them

Index Type Use Case

B-Tree Index (Default) Best for queries retrieving a few records

Bitmap Index Useful for columns with low cardinality (e.g., Gender: M/F)

Function-Based Index Use when querying on a computed expression

Composite Index Best for queries filtering on multiple columns

🔹 Example: Using a B-Tree Index for Fast Query Performance

sql

CREATE INDEX idx_employee_name ON employees(employee_name);

4/6
✔ Improves performance for queries filtering by employee_name .

🔹 Example: Function-Based Index for Faster Searches

sql

CREATE INDEX idx_upper_emp_name ON employees(UPPER(employee_name));

✅ Speeds up queries that use UPPER():

sql

SELECT * FROM employees WHERE UPPER(employee_name) = 'JOHN';

🔹 Example: Bitmap Index for Low-Cardinality Columns

sql

CREATE BITMAP INDEX idx_gender ON employees(gender);

✔ Useful for queries filtering on few distinct values (e.g., M/F, YES/NO).

🚀 Summary of Performance Tuning Best Practices

Optimization Benefit

Minimize Context Switching Reduces overhead from frequent SQL-to-PL/SQL calls

Use BULK COLLECT Retrieves multiple rows in a single fetch

Use FORALL for Bulk DML Performs bulk inserts, updates, deletes efficiently

Use Proper Indexing Speeds up queries and reduces full table scans

Use Function-Based Indexes Speeds up searches on computed values

5/6
 Optimization Benefit

Use Bitmap Indexes Best for columns with low distinct values

🚀 Next Steps:
Would you like a hands-on case study demonstrating these optimizations? 🎯

6/6
Oracle SQL Topics Overview

Real-World Applications of PL/SQL 🚀

PL/SQL is widely used in enterprise applications for data validation, automation, security,
and reporting. Here are key real-world use cases where PL/SQL plays a critical role:

1. Data Validation and Business Rule Enforcement

✅ Why Use PL/SQL for Data Validation?
Ensures data integrity before inserting/updating records.

Enforces business rules at the database level to maintain consistency.

Reduces dependency on application-level validation.

🔹 Example: Enforcing Business Rules Using PL/SQL Triggers

✔ Scenario: Prevent employees from having a salary less than $30,000.

sql

CREATE OR REPLACE TRIGGER trg_salary_check

BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 30000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary must be at least $30,000');
END IF;
END;
/

✔ Ensures all salary values are validated before insertion/update.

1/6
🔹 Example: Validating Customer Email Format Using PL/SQL
Function
sql

CREATE OR REPLACE FUNCTION is_valid_email(p_email VARCHAR2) RETURN BOOLEAN IS

BEGIN
RETURN REGEXP_LIKE(p_email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
END;
/

sql

SELECT is_valid_email('test@example.com') FROM dual; -- Returns TRUE

SELECT is_valid_email('invalid-email') FROM dual; -- Returns FALSE

✔ Ensures valid email formats before storing customer details.

2. Automating Database Tasks with PL/SQL Jobs

✅ Why Use PL/SQL for Automation?
Automates repetitive database tasks like backups, cleanup, and report generation.

Reduces manual intervention and human errors.

Uses DBMS_SCHEDULER or DBMS_JOB to run PL/SQL scripts at scheduled intervals.

🔹 Example: Automating Employee Salary Updates Using PL/SQL Job

✔ Scenario: Every 1st of the month, give a 5% salary hike to all employees.

sql

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_SALARY_UPDATE',
job_type => 'PLSQL_BLOCK',

2/6
 job_action => 'BEGIN
UPDATE employees SET salary = salary * 1.05;
COMMIT;
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1',
enabled => TRUE
);
END;
/

✔ Automatically runs every month without manual intervention.

3. Developing Secure Applications with PL/SQL

✅ Why Use PL/SQL for Security?
Prevents SQL injection attacks by using bind variables.

Controls user access and privileges.

Encrypts sensitive data for secure storage.

🔹 Example: Using Bind Variables to Prevent SQL Injection

✔ Scenario: Secure user authentication with PL/SQL procedure.

sql

CREATE OR REPLACE PROCEDURE authenticate_user(p_username VARCHAR2, p_password

VARCHAR2) IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM users
WHERE username = p_username
AND password = p_password;

3/6
 IF v_count = 1 THEN
DBMS_OUTPUT.PUT_LINE('Login Successful');
ELSE
DBMS_OUTPUT.PUT_LINE('Invalid Credentials');
END IF;
END;
/

✔ Prevents SQL injection by avoiding direct concatenation in SQL queries.

🔹 Example: Encrypting and Decrypting Data in PL/SQL

✔ Scenario: Encrypt employee salaries before storing in the database.

sql

CREATE OR REPLACE FUNCTION encrypt_salary(p_salary NUMBER) RETURN RAW IS

BEGIN
RETURN DBMS_CRYPTO.ENCRYPT(
src => UTL_RAW.CAST_TO_RAW(p_salary),
typ => DBMS_CRYPTO.ENCRYPT_AES256,
key => UTL_RAW.CAST_TO_RAW('MySecretKey')
);
END;
/

✔ Ensures salaries are encrypted before storage, enhancing security.

4. PL/SQL in Oracle Forms and Reports

✅ Why Use PL/SQL in Oracle Forms & Reports?
Enhances user interactions by processing form inputs using PL/SQL.

Generates dynamic reports by fetching and formatting database data.

Reduces network traffic by processing data inside the database.

4/6
🔹 Example: Using PL/SQL to Validate Form Data Before Submission
✔ Scenario: Ensure that Customer Age is at least 18 before submission.

sql

BEGIN
IF :CUSTOMER.AGE < 18 THEN
MESSAGE('Customer must be at least 18 years old.');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
/

✔ Prevents invalid data submission in Oracle Forms.

🔹 Example: Generating Reports with PL/SQL in Oracle Reports

✔ Scenario: Generate a monthly sales report using PL/SQL Procedure.

sql

CREATE OR REPLACE PROCEDURE generate_sales_report IS

BEGIN
FOR rec IN (SELECT product_name, SUM(sales_amount) AS total_sales
FROM sales
WHERE sales_date BETWEEN ADD_MONTHS(SYSDATE, -1) AND SYSDATE
GROUP BY product_name) LOOP
DBMS_OUTPUT.PUT_LINE(rec.product_name || ': $' || rec.total_sales);
END LOOP;
END;
/

✔ Fetches and formats sales data dynamically for reports.

5/6
🚀 Summary: Real-World Uses of PL/SQL
Use Case PL/SQL Feature Used

Data Validation & Business Rules Triggers, Functions, Procedures

Automating Tasks DBMS_SCHEDULER, DBMS_JOB

Security in Applications Bind Variables, Encryption (DBMS_CRYPTO)

PL/SQL in Oracle Forms & Reports Form Validations, Report Generation

🚀 Next Steps:
Would you like a hands-on project demonstrating these PL/SQL real-world applications? 🎯

6/6