1.

PL/SQL Block
Structure
PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension to
SQL. It combines SQL capabilities with procedural programming features such as loops,
conditions, and exception handling. Every PL/SQL program is written in the form of blocks,
which are the fundamental units of execution.

Structure of a PL/SQL Block

DECLARE
-- Declarations
BEGIN
-- Executable statements
EXCEPTION
-- Exception handling
END;
/
Sections of a Block

1. DECLARE Section (Optional)

 Used to declare variables, constants, cursors, records, and user-defined types.

 Variables must be declared before usage.
 Constants are declared using the keyword CONSTANT.

Example:

v_salary NUMBER(10,2);

2. BEGIN Section (Mandatory)

 Contains executable statements.

 Includes SQL statements (INSERT, UPDATE, DELETE, SELECT).
 Includes procedural statements such as loops and conditions.

3. EXCEPTION Section (Optional)

 Handles runtime errors.

 Prevents abnormal program termination.
 Contains exception handlers using WHEN.

Types of PL/SQL Blocks

1. Anonymous Blocks
2. Named Blocks
 3. Subprograms (Functions & Procedures)
4. Packages
5. Triggers

Features
 Supports nested blocks.
 Provides modular programming.
 Improves security and performance.
 Integrates tightly with SQL.

Thus, PL/SQL block structure ensures structured programming, better error handling, and
efficient database interaction.

2. Behavior of
Variables in Blocks
Variables in PL/SQL are used to store temporary data during execution. They follow specific
scope and lifetime rules.

Declaration Syntax
variable_name datatype [NOT NULL] [:= initial_value];

Example:

v_name VARCHAR2(50) := 'Oracle';

Characteristics of Variables

1. Scope

 The scope of a variable is the region where it can be accessed.

 Variables declared in the outer block are accessible to inner blocks.
 Variables declared in inner blocks are not accessible outside.

2. Lifetime

 Variables exist only during block execution.

 Destroyed once the block finishes execution.

3. Initialization

 Default value is NULL.

 Can be initialized during declaration.
4. Constants

Declared using:

pi CONSTANT NUMBER := 3.14;

Value cannot be changed.

5. Anchored Declarations

 %TYPE → Uses datatype of a column or variable.

 %ROWTYPE → Uses structure of entire row.

Example:

v_empname [Link]%TYPE;

Anchored declarations improve maintainability and reduce datatype mismatch errors.

3. Basic Scalar Data

Types
Scalar data types store single values.

1. Numeric Types
 NUMBER(p,s)
 PLS_INTEGER
 BINARY_INTEGER

Used for arithmetic calculations.

2. Character Types
 VARCHAR2(size)
 CHAR(size)
 LONG

Used for storing text.

3. Date and Time Types

 DATE (Stores date and time)

 TIMESTAMP
 4. Boolean Type
 TRUE, FALSE, NULL
 Used only in PL/SQL (not allowed in SQL statements).

5. Large Object Types (LOB)

 BLOB
 CLOB

Attributes
 %TYPE
 %ROWTYPE

Scalar data types are essential for data manipulation, condition checking, and arithmetic
operations.

4. Composite Data
Types
Composite types store multiple related values.

1. Records

A record is a collection of related fields.

TYPE emp_record IS RECORD (

empno NUMBER,
ename VARCHAR2(50),
salary NUMBER
);

Advantages:

 Groups related data

 Similar to structures in C

2. %ROWTYPE

Represents entire row of a table.

emp_rec emp%ROWTYPE;
3. Collections
Collections store multiple elements.

Types:

1. Associative Arrays
2. Nested Tables
3. VARRAYs

Used for:

 Bulk operations
 Temporary storage
 Data manipulation

Composite data types enhance structured programming and improve performance.

5. Control Structures
Control structures determine execution flow.

1. Conditional Statements

IF Statement
IF condition THEN
statements;
END IF;

IF-ELSE
IF condition THEN
statements;
ELSE
statements;
END IF;

CASE Statement

Simplifies multiple conditions.

2. Iterative Statements

LOOP

Basic infinite loop.

WHILE LOOP

Executes while condition is TRUE.

FOR LOOP

Executes fixed number of times.

Control structures enable decision-making and repetition in PL/SQL programs.

6. Exceptions
Exceptions are runtime errors that occur during execution.

Types of Exceptions

1. Predefined Exceptions

 NO_DATA_FOUND
 TOO_MANY_ROWS
 ZERO_DIVIDE
 VALUE_ERROR

2. User-Defined Exceptions

Declared explicitly.

invalid_salary EXCEPTION;

3. Non-Predefined Exceptions

Linked using:

PRAGMA EXCEPTION_INIT(exception_name, error_number);

Handling Exceptions
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record found');

Benefits:

 Ensures program reliability

 Prevents abrupt termination
 Enables graceful error recovery
 7. Bulk Operations
Bulk operations improve performance when handling large datasets.

1. BULK COLLECT

Fetches multiple rows at once into collections.

SELECT * BULK COLLECT INTO emp_tab FROM emp;

2. FORALL

Executes DML operations in bulk.

FORALL i IN 1..emp_tab.COUNT
INSERT INTO emp VALUES emp_tab(i);

Advantages:

 Reduces context switching

 Improves performance
 Suitable for large data processing

8. Functions
A function is a named PL/SQL block that returns a single value.

Syntax
CREATE OR REPLACE FUNCTION calc_bonus
RETURN NUMBER IS
BEGIN
RETURN 1000;
END;
Characteristics

 Must return a value.

 Can accept parameters.
 Can be used in SQL queries.
 Stored in database.

Functions are used for calculations and reusable logic.

9. Procedures
A procedure is a named PL/SQL block that performs an action.

Syntax
CREATE OR REPLACE PROCEDURE update_salary IS
BEGIN
UPDATE emp SET salary = salary + 1000;
END;
Features

 Supports IN, OUT, IN OUT parameters.

 Does not necessarily return a value.
 Improves modularity and reusability.

Procedures are mainly used for business logic and database operations.

10. Packages
A package is a schema object that groups logically related PL/SQL elements.

Components

Package Specification

Declares public elements.

Package Body

Contains implementation.

Advantages:

 Encapsulation
 Better performance
 Improved security
 Code reusability
 Overloading support

Packages reduce compilation overhead and organize large applications.

11. Transaction
Scope
A transaction is a logical unit of work.
 Transaction Control Statements
 COMMIT
 ROLLBACK
 SAVEPOINT

Properties (ACID)

 Atomicity
 Consistency
 Isolation
 Durability

Transaction in PL/SQL

 Begins with first DML statement.

 Ends with COMMIT or ROLLBACK.
 Procedures share same transaction unless defined as autonomous.

Transaction scope ensures data integrity and consistency in database operations.

1. Lexical Units in
PL/SQL
Lexical units are the smallest meaningful elements of a PL/SQL program. They form the basic
building blocks of the PL/SQL language. A PL/SQL program is composed of various lexical
units arranged according to syntax rules.

Types of Lexical Units

1. Identifiers

Identifiers are names given to PL/SQL elements such as variables, constants, cursors,
procedures, functions, triggers, packages, and labels.

Rules for Identifiers:

 Must begin with a letter.

 Can contain letters, digits, underscore (_), dollar sign ($), and hash (#).
 Maximum length: 30 characters.
 Cannot be reserved words.
 Case-insensitive.
Example:

v_total_salary NUMBER;

Identifiers improve readability and allow programmers to reference data and program elements
easily.

2. Delimiters

Delimiters are symbols that have special meaning in PL/SQL.

Examples:

 Arithmetic Operators: +, -, *, /
 Relational Operators: =, <, >, <=, >=, <>
 Logical Operators: AND, OR, NOT
 Assignment Operator: :=
 Statement Terminator: ;
 Concatenation Operator: ||

Delimiters define relationships and operations between values.

3. Literals

Literals are fixed values directly written in the code.

Types:

 Numeric literals → 100, 45.67

 Character literals → 'Oracle'
 Boolean literals → TRUE, FALSE
 Date literals → '01-JAN-2024'

Literals provide constant input to programs.

4. Comments

Comments improve code readability and documentation.

Single-line:
-- This is a comment

Multi-line:

/* This is
a multi-line comment */

Comments are ignored during compilation.

2. Variables and Data

Types
Variables are named memory locations used to store data temporarily during execution of a
PL/SQL block.

Declaration Syntax:
variable_name datatype [NOT NULL] [:= initial_value];

Example:

v_salary NUMBER(10,2) := 5000;

Categories of Data Types

1. Scalar Data Types

Store single values.

 NUMBER
 VARCHAR2
 CHAR
 DATE
 BOOLEAN
 TIMESTAMP

Scalar types are used for arithmetic operations and string manipulation.

2. Composite Data Types

Store multiple values.

 Records
 Collections
Used for structured data handling.

3. Reference Data Types

 REF CURSOR
 Object types

4. Large Object (LOB) Data Types

 BLOB (Binary Large Object)

 CLOB (Character Large Object)

Used to store large multimedia or text data.

Anchored Declarations

PL/SQL provides dynamic typing features:

%TYPE

Declares a variable with the same datatype as a table column.

v_empname [Link]%TYPE;

%ROWTYPE

Declares a record with same structure as table row.

emp_record emp%ROWTYPE;

Anchored declarations increase maintainability and reduce datatype mismatch errors.

Scope and Lifetime

 Scope: Region where variable is accessible.

 Lifetime: Duration for which variable exists.
 Variables are local to their block and destroyed after execution.

3. Conditional
 Statements
Conditional statements control program flow based on logical conditions.

1. IF Statement

Used when only one condition is checked.

IF condition THEN
statements;
END IF;

2. IF-ELSE Statement

Used when alternative action is required.

IF condition THEN
statements;
ELSE
statements;
END IF;

3. IF-ELSIF-ELSE

Used for multiple conditions.

IF condition1 THEN
statements;
ELSIF condition2 THEN
statements;
ELSE
statements;
END IF;

4. CASE Statement

Simplifies multiple conditions.

Simple CASE
CASE variable
WHEN value1 THEN statements;
WHEN value2 THEN statements;
END CASE;

Searched CASE
CASE
 WHEN condition1 THEN statements;
WHEN condition2 THEN statements;
END CASE;

Advantages:

 Improves readability.
 Avoids complex nested IF statements.
 Enhances logical clarity.

4. Iterative
Statements
Iterative statements allow repeated execution of code.

1. Basic LOOP

Executes repeatedly until EXIT condition is satisfied.

LOOP
statements;
EXIT WHEN condition;
END LOOP;

2. WHILE LOOP

Executes while condition remains TRUE.

WHILE condition LOOP

statements;
END LOOP;

3. FOR LOOP

Used when number of iterations is known.

FOR i IN 1..10 LOOP

statements;
END LOOP;

Automatically declares loop counter.

4. REVERSE FOR LOOP

FOR i IN REVERSE 1..10 LOOP
statements;
END LOOP;

Used for descending order.

Iterative statements reduce redundancy and automate repetitive operations.

5. Cursor Structures
A cursor is a pointer to the result set of a SQL query.

Types of Cursors

1. Implicit Cursor

Automatically created by Oracle for DML operations.

Attributes:

 SQL%FOUND
 SQL%NOTFOUND
 SQL%ROWCOUNT
 SQL%ISOPEN

2. Explicit Cursor

Declared by programmer for SELECT statements returning multiple rows.

Steps:

1. Declare
2. Open
3. Fetch
4. Close

Example:

CURSOR emp_cur IS SELECT empno, ename FROM emp;

OPEN emp_cur;
FETCH emp_cur INTO v_empno, v_ename;
CLOSE emp_cur;
Cursor FOR LOOP

Automatically handles open, fetch, close.

Cursors allow row-by-row processing.

6. Bulk Statements
Bulk statements improve performance by reducing context switching between SQL and PL/SQL
engines.

BULK COLLECT

Fetches multiple rows into collections.

SELECT empno BULK COLLECT INTO emp_ids FROM emp;

FORALL

Executes DML operations in bulk.

FORALL i IN 1..emp_ids.COUNT
DELETE FROM emp WHERE empno = emp_ids(i);

Advantages:

 High performance
 Efficient large data processing
 Reduces network overhead

7. Introduction to
Collections
Collections are PL/SQL data types used to store multiple values of the same datatype.

Types:
1. VARRAY
 2. Nested Table
3. Associative Array

Collections allow efficient bulk data manipulation.

8. Object Types:
VARRAY and Table
Collections
VARRAY
 Fixed maximum size.
 Ordered collection.
 Stored inline with table.

TYPE phone_list IS VARRAY(5) OF VARCHAR2(15);

Advantages:

 Maintains order.
 Suitable for small fixed-size lists.

Nested Table

 No fixed size.
 Stored separately.
 Can grow dynamically.

TYPE num_table IS TABLE OF NUMBER;

Advantages:

 Flexible size.
 Efficient for large datasets.

9. Associative Arrays
Also called Index-by tables.

 Indexed by PLS_INTEGER or VARCHAR2.

 Stored in memory only.
 Dynamic size.
TYPE emp_tab IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;

Used for temporary processing and high-speed lookup.

10. Oracle Collection

API
Oracle provides built-in methods for manipulating collections.

Important Methods
 COUNT
 FIRST
 LAST
 NEXT
 PRIOR
 EXTEND
 DELETE
 EXISTS
 TRIM

Example:

[Link];
[Link](2);

These methods provide dynamic management of collection elements.

⃣ Function and
Procedure
Architecture (10
Marks)
Introduction

Functions and Procedures are PL/SQL subprograms used to modularize application logic. They
help in reusability, maintainability, security, and structured programming.

A subprogram consists of:

1. Header (Name, parameters, return type for function)

2. Declaration section
 3. Execution section
4. Exception section

Function Architecture

A function must return a value using RETURN statement.

Syntax
CREATE OR REPLACE FUNCTION calc_bonus
(p_salary IN NUMBER)
RETURN NUMBER
IS
v_bonus NUMBER;
BEGIN
v_bonus := p_salary * 0.10;
RETURN v_bonus;

EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END calc_bonus;
/

Calling Function
DECLARE
v_result NUMBER;
BEGIN
v_result := calc_bonus(50000);
DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_result);
END;
/

Procedure Architecture

A procedure performs an action but does not necessarily return a value.

Syntax
CREATE OR REPLACE PROCEDURE update_salary
(p_empid IN NUMBER, p_increment IN NUMBER)
IS
BEGIN
UPDATE emp
SET salary = salary + p_increment
WHERE empno = p_empid;

COMMIT;

EXCEPTION
 WHEN OTHERS THEN
ROLLBACK;
END update_salary;
/

Calling Procedure
BEGIN
update_salary(101, 2000);
END;
/

Key Difference
Function Procedure
Must return value No compulsory return
Used in SQL Cannot be used in SQL
Used for calculations Used for operations

⃣ Transaction
Scope (10 Marks)
Definition

A transaction is a logical unit of work consisting of one or more SQL statements.

Transaction begins with first DML statement and ends with:

 COMMIT
 ROLLBACK

Transaction Control Statements

 COMMIT → Makes changes permanent

 ROLLBACK → Undoes changes
 SAVEPOINT → Intermediate point

Example
BEGIN
UPDATE emp SET salary = salary + 1000 WHERE empno = 101;

SAVEPOINT before_delete;

DELETE FROM emp WHERE empno = 200;

ROLLBACK TO before_delete;

COMMIT;
END;
/

Autonomous Transaction
CREATE OR REPLACE PROCEDURE log_action
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log_table VALUES('Updated record');
COMMIT;
END;
/

Important Points

 Subprogram shares caller’s transaction

 COMMIT inside procedure affects entire session
 ACID properties maintained

⃣ Calling
Subroutines (10
Marks)
Subroutines can be called from:

 Anonymous blocks
 Other procedures
 Functions
 SQL statements (functions only)

Example
CREATE OR REPLACE PROCEDURE greet_user
(p_name VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello ' || p_name);
END;
/

Calling:

BEGIN
greet_user('Oracle');
END;
/
 ⃣ Positional
Notation (10 Marks)
Parameters passed in the same order as declared.

CREATE OR REPLACE PROCEDURE add_employee

(p_id NUMBER, p_name VARCHAR2)
IS
BEGIN
INSERT INTO emp(empno, ename)
VALUES(p_id, p_name);
END;
/

Calling:

BEGIN
add_employee(101, 'John');
END;
/

✔ Order must match

✔ Simple but error-prone

⃣ Named Notation
(10 Marks)
Parameters passed using parameter names.

BEGIN
add_employee(p_name => 'John',
p_id => 101);
END;
/

✔ Order does not matter

✔ More readable
✔ Safer

⃣ Mixed Notation
(10 Marks)
Combination of positional and named.
BEGIN
add_employee(101,
p_name => 'John');
END;
/

Rule:

 Positional arguments must come first.

⃣ Exclusionary
Notation (10 Marks)
Used when parameters have default values.

CREATE OR REPLACE PROCEDURE display_info

(p_id NUMBER,
p_name VARCHAR2 DEFAULT 'Unknown')
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(p_id || ' ' || p_name);
END;
/

Calling:

BEGIN
display_info(101);
END;
/

✔ Optional parameters
✔ Flexible procedure calls

⃣ SQL Call
Notation (10 Marks)
Functions can be used inside SQL queries.

CREATE OR REPLACE FUNCTION double_salary

(p_salary NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_salary * 2;
END;
/
Using in SQL:

SELECT empno,
double_salary(salary)
FROM emp;

Restrictions:

 Cannot perform DML (unless autonomous)

 Must return valid datatype

⃣ Function Model
Choices (10 Marks)
1. Standalone Function
CREATE FUNCTION simple_func
RETURN NUMBER
IS
BEGIN
RETURN 1;
END;
/

2. Package Function
CREATE PACKAGE math_pkg AS
FUNCTION square(p NUMBER) RETURN NUMBER;
END;
/

CREATE PACKAGE BODY math_pkg AS

FUNCTION square(p NUMBER) RETURN NUMBER IS
BEGIN
RETURN p*p;
END;
END;
/

3. Deterministic Function
CREATE FUNCTION square_det(p NUMBER)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN p*p;
END;
/

Creation Options
(10 Marks)
Common options:
  OR REPLACE
 AUTHID CURRENT_USER
 DETERMINISTIC
 PARALLEL_ENABLE

Example:

CREATE OR REPLACE FUNCTION auth_example

RETURN NUMBER
AUTHID CURRENT_USER
IS
BEGIN
RETURN 100;
END;
/

⃣ ⃣ Pass-by-Value
Functions (10 Marks)
Default behavior for IN parameters.

CREATE FUNCTION add_ten

(p_num IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_num + 10;
END;
/

Original variable remains unchanged.

⃣ ⃣ Pass-by-
Reference Functions
(10 Marks)
Using IN OUT.

CREATE OR REPLACE FUNCTION modify_value

(p_num IN OUT NUMBER)
RETURN NUMBER
IS
BEGIN
p_num := p_num + 5;
RETURN p_num;
END;
/
Original value modified.

⃣ ⃣ Procedures
(Pass-by-Value &
Reference) (10
Marks)
Pass-by-Value (IN)
CREATE PROCEDURE test_value
(p_num IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(p_num);
END;
/

Pass-by-Reference (OUT / IN OUT)

CREATE PROCEDURE test_reference
(p_num IN OUT NUMBER)
IS
BEGIN
p_num := p_num + 20;
END;
/

Calling:

DECLARE
v_num NUMBER := 10;
BEGIN
test_reference(v_num);
DBMS_OUTPUT.PUT_LINE(v_num);
END;
/

⃣ ⃣ Supporting
Scripts (10 Marks)
Supporting scripts help in maintenance.

Drop Script
DROP FUNCTION calc_bonus;
DROP PROCEDURE update_salary;
Grant Script
GRANT EXECUTE ON calc_bonus TO user1;
Testing Script
BEGIN
DBMS_OUTPUT.PUT_LINE(calc_bonus(50000));
END;
/

Packages in PL/SQL
(10 Marks – With
Explanation + Code)
1. Package Architecture

A Package is a schema object that groups related procedures, functions, variables, cursors, and
types into a single unit.

It has two parts:

1. Package Specification (Specification)

2. Package Body

Architecture Diagram (Conceptual)

PACKAGE
├── Specification (Public)
└── Body (Private + Implementation)

Advantages:

 Modularity
 Encapsulation
 Better performance
 Easier maintenance
 Security control

2. Package Specification

The specification declares public components accessible outside the package.

Example:
CREATE OR REPLACE PACKAGE emp_pkg AS
-- Public variable
bonus_rate NUMBER := 0.10;

-- Public procedure
PROCEDURE raise_salary(p_empid NUMBER, p_amount NUMBER);

-- Public function
FUNCTION get_salary(p_empid NUMBER) RETURN NUMBER;
END emp_pkg;
/

Explanation:

 bonus_rate → Public variable

 raise_salary → Procedure prototype
 get_salary → Function prototype

3. Package Body

Contains implementation of procedures and functions declared in specification.

CREATE OR REPLACE PACKAGE BODY emp_pkg AS

PROCEDURE raise_salary(p_empid NUMBER, p_amount NUMBER) IS

BEGIN
UPDATE employees
SET salary = salary + p_amount
WHERE employee_id = p_empid;
END raise_salary;

FUNCTION get_salary(p_empid NUMBER) RETURN NUMBER IS

v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = p_empid;

RETURN v_salary;
END get_salary;

END emp_pkg;
/

4. Prototype Features

Prototype means declaration without implementation in specification.

Example:
FUNCTION calculate_bonus(p_salary NUMBER) RETURN NUMBER;

Implementation is written in package body:

FUNCTION calculate_bonus(p_salary NUMBER) RETURN NUMBER IS

BEGIN
RETURN p_salary * bonus_rate;
END;
Importance:

 Enables modular programming

 Allows overloading
 Improves readability

5. Serially Reusable Precompiler Directive

Used to reduce memory usage for large packages.

Syntax:
CREATE OR REPLACE PACKAGE large_pkg
IS
PRAGMA SERIALLY_REUSABLE;
...
END;
/

Purpose:

 Memory allocated only during call

 Released after execution
 Useful in high-concurrency systems

6. Variables and Types in Package

Public Variable Example:

bonus_rate NUMBER := 0.15;

Private Variable (Inside Body Only):

v_total NUMBER;

Custom Type Example:

TYPE emp_rec IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(50)
);

7. Components: Functions and Procedures

Procedure Example:
PROCEDURE add_employee(p_name VARCHAR2, p_salary NUMBER) IS
BEGIN
INSERT INTO employees(name, salary)
VALUES (p_name, p_salary);
END;

Function Example:
FUNCTION total_employees RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
RETURN v_count;
END;

8. Definer vs Invoker Rights

Controls execution privileges.

Definer Rights (Default)

Executes with privileges of package owner.

CREATE OR REPLACE PACKAGE emp_pkg AUTHID DEFINER AS

Invoker Rights

Executes with privileges of user who calls it.

CREATE OR REPLACE PACKAGE emp_pkg AUTHID CURRENT_USER AS

Difference:

Definer Rights Invoker Rights

Uses owner privileges Uses caller privileges
More secure internally Flexible access control

9. Managing Packages in Database Catalog

View Package Details

SELECT object_name, status
FROM user_objects
WHERE object_type = 'PACKAGE';

Compile Package
ALTER PACKAGE emp_pkg COMPILE;
Drop Package
DROP PACKAGE emp_pkg;

10. Finding, Validating, and Describing Packages

Describe Package
DESC emp_pkg;

Check Errors
SHOW ERRORS;

Validate Package
ALTER PACKAGE emp_pkg COMPILE BODY;

11. Checking Dependencies

To check dependent objects:

SELECT *
FROM user_dependencies
WHERE name = 'EMP_PKG';

Shows:

 Tables used
 Other procedures/functions used

12. Timestamp vs Signature Validation

Oracle checks validity when dependent objects change.

1. Timestamp Method

 Compares last modification time

 Simple but less reliable

2. Signature Method

 Compares parameter types and structure

 More accurate
 Used in modern Oracle versions
Comparison:

Timestamp Signature
Based on time Based on structure
Less accurate More accurate
Faster Safer

Conclusion
PL/SQL Packages provide:

 Modular programming
 Encapsulation
 Performance improvement
 Better security through Definer/Invoker rights
 Efficient dependency management

They are essential for large-scale enterprise database applications.

⃣ Package
Architecture
Concept

A Package is a schema object that encapsulates logically related PL/SQL elements (procedures,
functions, variables, types, cursors).

Structure:

 Specification (Interface – Public Layer)

 Body (Implementation – Private Layer)

Architecture View
Client → Package Specification → Package Body → Database Objects

Features:

 Encapsulation
 Session persistence
 Performance (Loaded once per session)
 Security control
 ⃣ Package
Specification
Definition

The specification defines public declarations accessible outside the package.

Code Example
CREATE OR REPLACE PACKAGE finance_pkg AS

-- Public variable
tax_rate NUMBER := 0.18;

-- Public type
TYPE emp_rec IS RECORD(
emp_id NUMBER,
emp_salary NUMBER
);

-- Procedure prototype
PROCEDURE update_salary(p_id NUMBER, p_amt NUMBER);

-- Function prototype
FUNCTION calculate_tax(p_salary NUMBER) RETURN NUMBER;

END finance_pkg;
/

Key Points:

 Only declarations
 No implementation
 Acts as interface

⃣ Prototype
Features
Concept

Prototype = Declaration without body.

Enables:

 Forward referencing
 Overloading
 Better modularity
Example

Specification:

FUNCTION bonus(p_salary NUMBER) RETURN NUMBER;

Body:

FUNCTION bonus(p_salary NUMBER) RETURN NUMBER IS

BEGIN
RETURN p_salary * 0.10;
END;

⃣ Serially Reusable
Precompiler
Directive
Concept

Reduces memory usage by releasing package state after execution.

Syntax
CREATE OR REPLACE PACKAGE temp_pkg
IS
PRAGMA SERIALLY_REUSABLE;

PROCEDURE display;
END;
/

Body:
CREATE OR REPLACE PACKAGE BODY temp_pkg
IS
PRAGMA SERIALLY_REUSABLE;

PROCEDURE display IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Temporary execution');
END;
END;
/

Use Case:

 High-concurrency environments
 Large global variables
 ⃣ Variables in
Packages
Public Variable

Accessible outside package.

bonus_rate NUMBER := 0.12;

Private Variable

Declared only in body.

v_counter NUMBER := 0;

Persistent Nature:

Values remain throughout session.

⃣ Types in Packages
Packages allow creation of reusable types.

Record Type
TYPE dept_rec IS RECORD(
dept_id NUMBER,
dept_name VARCHAR2(50)
);

Collection Type
TYPE emp_list IS TABLE OF NUMBER;

⃣ Components:
Functions and
Procedures
Procedure Example
PROCEDURE insert_emp(p_id NUMBER, p_name VARCHAR2) IS
BEGIN
INSERT INTO employees(employee_id, first_name)
VALUES (p_id, p_name);
END;
Function Example
FUNCTION total_emp RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
RETURN v_count;
END;

⃣ Package Body
Definition

Contains implementation of specification elements.

Example
CREATE OR REPLACE PACKAGE BODY finance_pkg AS

PROCEDURE update_salary(p_id NUMBER, p_amt NUMBER) IS

BEGIN
UPDATE employees
SET salary = salary + p_amt
WHERE employee_id = p_id;
END;

FUNCTION calculate_tax(p_salary NUMBER) RETURN NUMBER IS

BEGIN
RETURN p_salary * tax_rate;
END;

END finance_pkg;
/

Key Features:

 Can contain private variables

 Cannot define new public elements

⃣ Definer vs
Invoker Rights
Mechanics
Definer Rights (Default)

Executes using privileges of package owner.

CREATE OR REPLACE PACKAGE finance_pkg

AUTHID DEFINER AS
 Invoker Rights

Executes using caller’s privileges.

CREATE OR REPLACE PACKAGE finance_pkg

AUTHID CURRENT_USER AS

Difference Table:

Feature Definer Invoker

Privilege Used Owner Caller
Security More controlled Flexible
Default Yes No

Managing
Packages in Database
Catalog
View Packages
SELECT object_name, status
FROM user_objects
WHERE object_type = 'PACKAGE';
Recompile
ALTER PACKAGE finance_pkg COMPILE;
Drop
DROP PACKAGE finance_pkg;

⃣ Finding,
Validating &
Describing Packages
Describe Package
DESC finance_pkg;
Show Errors
SHOW ERRORS PACKAGE finance_pkg;
Validate
ALTER PACKAGE finance_pkg COMPILE BODY;

⃣ Checking
Dependencies
Concept

Dependencies show objects used by package.

Query
SELECT name, type, referenced_name
FROM user_dependencies
WHERE name = 'FINANCE_PKG';

Purpose:

 Identify affected objects

 Prevent runtime errors

⃣ Timestamp vs
Signature Validation
Oracle invalidates packages when dependent objects change.

Timestamp Method
 Checks last modified time
 If changed → invalid

Limitation:

Even small change invalidates package.

Signature Method

 Compares structure (parameters, datatype)

 More precise
 Used in modern Oracle

Comparison Table

Timestamp Signature
Based on time Based on structure
Less accurate Highly accurate
More recompilations Fewer recompilations

Conclusion (For 10
Marks Ending)
PL/SQL Packages provide modular, secure, and high-performance database programming.
They support:

 Encapsulation
 Persistent variables
 Reusable types
 Controlled access via Definer/Invoker rights
 Dependency tracking and validation

They are essential for enterprise-level [Link] database systems.

⃣ Introduction to
Triggers
Information

A Trigger is a stored PL/SQL block that automatically executes when a specified event occurs
in the database.

Triggering Events:

 DML (INSERT, UPDATE, DELETE)

 DDL (CREATE, DROP, ALTER)
 Database/System Events (LOGON, STARTUP)

Basic Syntax
CREATE OR REPLACE TRIGGER trigger_name
BEFORE | AFTER | INSTEAD OF
INSERT OR UPDATE OR DELETE
ON table_name
[FOR EACH ROW]
BEGIN
-- Trigger logic
END;
/

⃣ Database Trigger
Architecture
Information

When a triggering event occurs:

User SQL → SQL Engine → Trigger Fired → PL/SQL Engine → Execution → Database
Updated
Components:

 Trigger Event
 Timing (BEFORE/AFTER)
 Target Object
 Trigger Body

⃣ DDL Triggers
Information

DDL triggers fire on:

 CREATE
 ALTER
 DROP
 TRUNCATE

Example: Audit DDL Activity

CREATE OR REPLACE TRIGGER ddl_audit
AFTER CREATE OR DROP ON SCHEMA
BEGIN
INSERT INTO ddl_log(event_name, object_name, user_name)
VALUES (ORA_SYSEVENT,
ORA_DICT_OBJ_NAME,
ORA_LOGIN_USER);
END;
/

⃣ Event Attribute
Functions
Used inside DDL triggers.

Function Purpose
ORA_SYSEVENT Returns event name
ORA_DICT_OBJ_NAME Object name
ORA_DICT_OBJ_TYPE Object type
ORA_LOGIN_USER User name

Example
BEGIN
DBMS_OUTPUT.PUT_LINE('Event: ' || ORA_SYSEVENT);
END;
 ⃣ Building DDL
Triggers
Example: Restrict Table Drop
CREATE OR REPLACE TRIGGER restrict_drop
BEFORE DROP ON SCHEMA
BEGIN
IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
RAISE_APPLICATION_ERROR(-20001, 'Dropping tables not allowed');
END IF;
END;
/

⃣ DML Triggers
Information

DML triggers execute on:

 INSERT
 UPDATE
 DELETE

⃣ Statement-Level
Triggers
Information

 Executes once per SQL statement

 No row reference allowed

Example
CREATE OR REPLACE TRIGGER stmt_trigger
AFTER INSERT ON employees
BEGIN
INSERT INTO audit_log(action_date, action_type)
VALUES (SYSDATE, 'INSERT STATEMENT');
END;
/

⃣ Row-Level
Triggers
Information
 Executes once per affected row
  Uses :NEW and :OLD

Example
CREATE OR REPLACE TRIGGER row_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :[Link] < :[Link] THEN
RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be reduced');
END IF;
END;
/

⃣ Compound
Triggers
Information

Contains multiple timing sections in one trigger.

Example
CREATE OR REPLACE TRIGGER compound_trigger
FOR UPDATE ON employees
COMPOUND TRIGGER

BEFORE STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Before update statement');
END BEFORE STATEMENT;

BEFORE EACH ROW IS

BEGIN
DBMS_OUTPUT.PUT_LINE('Updating row: ' || :OLD.employee_id);
END BEFORE EACH ROW;

AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('After update statement');
END AFTER STATEMENT;

END compound_trigger;
/

INSTEAD OF
Triggers
Information

Used on views to enable DML operations.

Example
CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id, first_name FROM employees;
CREATE OR REPLACE TRIGGER instead_of_trigger
INSTEAD OF INSERT ON emp_view
FOR EACH ROW
BEGIN
INSERT INTO employees(employee_id, first_name)
VALUES(:NEW.employee_id, :NEW.first_name);
END;
/

⃣ ⃣ System &
Database Event
Triggers
Information

Fire on system-level events:

 LOGON
 LOGOFF
 STARTUP
 SHUTDOWN

Example
CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO login_audit(user_name, login_time)
VALUES (USER, SYSDATE);
END;
/

⃣ ⃣ Trigger
Restrictions
Key Restrictions:

 No COMMIT or ROLLBACK inside trigger

 No DDL inside DML trigger
 Mutating table restriction
 Cannot modify LONG datatype

Example (Invalid)
COMMIT; -- Not allowed inside trigger
 ⃣ ⃣ Maximum
Trigger Size
 Maximum size: 32 KB
 For large logic → Call stored procedure

Example
CREATE OR REPLACE TRIGGER call_proc
AFTER INSERT ON employees
BEGIN
audit_procedure;
END;
/

⃣ ⃣ SQL
Statements in
Triggers
Allowed:

 SELECT INTO
 INSERT
 UPDATE
 DELETE

Example:

SELECT salary INTO v_sal

FROM employees
WHERE employee_id = :NEW.employee_id;

Not Allowed:

 Transaction control statements

⃣ ⃣ LONG and
LONG RAW Data
Types
Restrictions:
 Cannot reference LONG directly in trigger
  Cannot compare LONG values

Solution:

Use CLOB instead

ALTER TABLE documents

MODIFY doc_text CLOB;

Final Conclusion
([Link] Level)
Triggers provide:

 Automatic enforcement of business rules

 Data auditing and logging
 Security implementation
 DDL and system-level monitoring

However:

 Should be used carefully due to performance overhead

 Must avoid mutating table issues
 Should not contain heavy logic