Oracle PL/SQL Complete Guide

Oracle PL/SQL is one of the most powerful procedural extensions to SQL ever built. It has shaped enterprise applications for decades and continues to be deeply integrated into Oracle databases, cloud offerings, and modern data engineering workloads.
This extensive guide covers everything you need to know about PL/SQL—from basic concepts to advanced features like dynamic SQL, triggers, external procedures, file handling, roles, mutating tables, debugging, and best coding practices.

---

1. Introduction to Oracle PL/SQL

What is PL/SQL?

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s extension to standard SQL. While SQL is declarative and designed for querying data, PL/SQL adds imperative programming capabilities such as:

• Variables & constants

• Conditional statements

• Loops

• Functions & procedures

• Packages

• Exception handling

• Object-oriented features (encapsulation, overloading, information hiding)

It enables developers to write complex, data-centric programs that run efficiently inside the Oracle Database.

Why was PL/SQL created?

SQL alone cannot handle procedural logic. PL/SQL was designed to:

• Improve performance by reducing network round-trips

• Encapsulate business logic in the database

• Support batch processing

• Provide robust error handling

• Enable modular programming

Organizations rely heavily on PL/SQL for triggers, stored procedures, packages, ETL operations, and enterprise application logic.

---

2. PL/SQL vs Java Inside the Oracle Database

Oracle supports both PL/SQL and Java stored procedures, leading to a common question:

Which is better—PL/SQL or Java—for writing stored procedures inside Oracle?

PL/SQL Advantages

• Purpose-built for database logic

• Tightly integrated with SQL

• Faster for DML-heavy operations

• Easier for SQL developers

• Native compilation supported (9i and above)

Java Advantages

• Platform independent

• Supports advanced OOP features

• Better for computationally intensive tasks

• Can be reused outside the database

When to use which?

Use PL/SQL When…	Use Java When…
Working mostly with SQL/DML statements	Performing heavy computations
You need procedural code inside database	Reusing Java libraries
You want faster data manipulation	Need cross-platform reusability

Oracle will never deprecate PL/SQL—it is tightly coupled with the database kernel and used by many Oracle applications.

---

3. Tracking Changes in PL/SQL Objects

How to detect if someone modified stored code?

Oracle stores metadata about all objects in USER_OBJECTS, including:

• Creation time

• Last modified time

• Status

Query:

SELECT object_name,
       TO_CHAR(created, 'DD-Mon-YY HH24:MI') AS create_time,
       TO_CHAR(last_ddl_time, 'DD-Mon-YY HH24:MI') AS mod_time,
       status
FROM user_objects
WHERE last_ddl_time > '&CHECK_FROM_DATE';

This helps track changes in triggers, views, procedures, and packages.

---

4. Searching Code in PL/SQL Programs

To find where a field, table, or keyword appears in PL/SQL code:

SELECT type, name, line
FROM user_source
WHERE UPPER(text) LIKE '%&KEYWORD%';

This is invaluable for debugging and large application maintenance.

---

5. Keeping a History of PL/SQL Source Code

Changes in production environments can be risky. One approach is to store code history using a schema-level AFTER CREATE trigger:

Step 1: Create history table

CREATE TABLE source_hist AS 
SELECT SYSDATE change_date, user_source.* 
FROM user_source WHERE 1=2;

Step 2: Create the trigger

CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON SCOTT.SCHEMA
DECLARE
BEGIN
  IF dictionary_obj_type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE') THEN
    INSERT INTO source_hist
    SELECT SYSDATE, user_source.* 
    FROM user_source
    WHERE type = dictionary_obj_type
      AND name = dictionary_obj_name;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20000, SQLERRM);
END;
/

This automatically saves the previous version of any object before it is overwritten.

---

6. Protecting PL/SQL Source Code (Wrapping)

Oracle provides a PL/SQL Wrapper to hide source code.

wrap iname=input_file.sql oname=output_file.plb

The .plb file contains encrypted code. There is no unwrap tool provided by Oracle, so always store the original source safely.

---

7. Printing Output from PL/SQL

Use DBMS_OUTPUT:

SET SERVEROUTPUT ON;
BEGIN
 DBMS_OUTPUT.put_line('Hello from PL/SQL!');
END;
/

To avoid buffer overflow:

SET SERVEROUTPUT ON SIZE 200000;

---

8. Reading & Writing Files in PL/SQL

Oracle provides UTL_FILE for file operations.

Example

DECLARE
  f UTL_FILE.FILE_TYPE;
BEGIN
  f := UTL_FILE.FOPEN('/tmp', 'myfile.txt', 'w');
  UTL_FILE.PUT_LINE(f, 'Writing to file...');
  UTL_FILE.FCLOSE(f);
END;
/

Note: Directory must be defined in UTL_FILE_DIR or created as an Oracle DIRECTORY object in newer versions.

---

9. Executing DDL/DML Dynamically

Using EXECUTE IMMEDIATE (Oracle 8i and above):

EXECUTE IMMEDIATE 'CREATE TABLE test (id NUMBER)';

With bind variables:

EXECUTE IMMEDIATE 
  'INSERT INTO dept VALUES (:1,:2,:3)'
USING dept_id, dept_name, location;

---

10. Using DBMS_SQL for Dynamic SQL (Older Oracle Versions)

Example:

DECLARE
  cur INTEGER;
  res INTEGER;
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cur, 'CREATE TABLE X(Y DATE)', DBMS_SQL.NATIVE);
  res := DBMS_SQL.EXECUTE(cur);
  DBMS_SQL.CLOSE_CURSOR(cur);
END;
/

---

11. %TYPE and %ROWTYPE in PL/SQL

%TYPE

Used to define a variable with the same datatype as a column.

v_empno emp.empno%TYPE;

%ROWTYPE

Used to define a record matching a table or cursor.

v_emp emp%ROWTYPE;

These features ensure datatype safety.

---

12. NULL Handling in PL/SQL

NULL comparisons always return NULL, not TRUE or FALSE.

IF a = b THEN ...

This will not work for NULL values. Use:

IF NVL(a,0) = NVL(b,0) THEN ...

---

13. Using Sequences in PL/SQL

Direct usage is not allowed:

i := seq.NEXTVAL;  -- Not allowed

Correct way:

SELECT seq.NEXTVAL INTO i FROM dual;

---

14. Executing Operating System Commands

Oracle 8 and above allow external procedures:

• Write a C/C++ function

• Compile into shared library

• Register using CREATE LIBRARY

• Call from PL/SQL

Not possible directly in Oracle 7.

---

15. Looping Through Tables in PL/SQL

Example of nested loops:

DECLARE
  CURSOR dept_cur IS SELECT deptno FROM dept;
  CURSOR emp_cur(dno NUMBER) IS SELECT ename FROM emp WHERE deptno = dno;
BEGIN
  FOR d IN dept_cur LOOP
     FOR e IN emp_cur(d.deptno) LOOP
       DBMS_OUTPUT.put_line(e.ename);
     END LOOP;
  END LOOP;
END;
/

---

16. Commit Strategy & ORA-1555 Errors

Commit less frequently!

Frequent commits cause rollback segments to reuse space too quickly, leading to snapshot-too-old errors.

Bad approach:

FOR rec IN c LOOP
  ...  
  COMMIT;
END LOOP;

Correct approach:

i := 0;
FOR rec IN c LOOP
  ...
  i := i + 1;
  IF MOD(i,10000)=0 THEN
     COMMIT;
  END IF;
END LOOP;

---

17. Why SQL Works in SQL*Plus but Not in PL/SQL

PL/SQL does not use privileges from roles.
You must grant direct privileges:

GRANT SELECT ON scott.emp TO john;

Or use invoker rights:

CREATE OR REPLACE PROCEDURE p AUTHID CURRENT_USER AS ...

---

18. Mutating and Constraining Tables

A mutating table is being modified and cannot be queried by a row-level trigger.

Examples that cause ORA-04091:

• Reading from the same table inside a row trigger

• Changing PK/FK values during trigger firing

Solutions:

• Use statement-level triggers + package variables

• Use compound triggers (Oracle 11g+)

• Avoid mixing RI constraints with triggers

---

19. Passing Tables or Objects to Remote Procedures

Only possible if both databases share:

1. Same object type definition

2. A valid database link

Example:

Database A:

CREATE OR REPLACE PROCEDURE pcalled(tabx DBMS_SQL.VARCHAR2S) IS ...

Database B:

PROCEDURE pcalling IS
  tabx DBMS_SQL.VARCHAR2S@link;
BEGIN
  pcalled@link(tabx);
END;
/

---

20. Triggers vs Procedures: Which Should Hold the Code?

Earlier Oracle versions compiled triggers dynamically—slower.
Today, both procedures and triggers compile to p-code and perform equally well.

Best Practice:
Put complex logic inside procedures, and call them inside triggers.

---

21. Does SQL*Plus Contain a PL/SQL Engine?

No.
All PL/SQL code is sent directly to the Oracle server for processing.

This makes SQL*Plus lightweight but powerful.

---

22. PL/SQL Block Size Limit

There is no fixed byte limit, but the limit depends on:

• Parse tree size

• Complexity

• Nesting depth

To check size:

SELECT * FROM dba_object_size WHERE name='PROCEDURE_NAME';

---

23. Best Resources for Learning PL/SQL

• Oracle PL/SQL Documentation

• RevealNet PL/SQL Pipeline

• PLNet.org

• Oracle FAQ sites

• Books by Steven Feuerstein (PL/SQL Guru)

---

Conclusion

Oracle PL/SQL remains one of the most robust, efficient, and enterprise-grade procedural languages for database systems. Whether you are building triggers, stored procedures, packages, or entire business applications, PL/SQL provides unmatched performance for data-heavy workloads.

This guide covered:

• Core PL/SQL concepts

• Dynamic SQL

• File operations

• Error handling

• Privilege issues

• Mutating tables

• Debugging

• System integration