DML and DDL in PL/SQL – Examples, Syntax & Tips

Understanding the distinction between DML and DDL in PL/SQL is critical for database developers. PL/SQL supports Data Manipulation Language (DML) operations directly, while Data Definition Language (DDL) operations require special handling.

Let’s explore how each is used with proper syntax and working examples.

Understanding DML and DDL in PL/SQL

DML (Data Manipulation Language) includes:

INSERT
UPDATE
DELETE

These operations modify the data in tables and support transactions via COMMIT, ROLLBACK, and SAVEPOINT.

DML and DDL in PL/SQL – INSERT Example

DECLARE
  v_isbn BOOKS.ISBN%TYPE := '12345678';
  v_category BOOKS.CATEGORY%TYPE := 'Oracle Server';
  v_title BOOKS.TITLE%TYPE := 'Oracle Information Retrieval';
BEGIN
  INSERT INTO books (ISBN, CATEGORY, TITLE, NUM_PAGES, PRICE, COPYRIGHT, AUTHOR1)
  VALUES (v_isbn, v_category, v_title, 450, 39.95, 2005, 44);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ROLLBACK;
END;
/

Example – DML with UPDATE Using Variable

DECLARE
  v_num_pages BOOKS.NUM_PAGES%TYPE;
  v_isbn BOOKS.ISBN%TYPE := '72230665';
BEGIN
  SELECT num_pages INTO v_num_pages FROM books WHERE isbn = v_isbn;
  v_num_pages := v_num_pages + 200;
  UPDATE books SET num_pages = v_num_pages WHERE isbn = v_isbn;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ROLLBACK;
END;
/

UPDATE Using WHERE CURRENT OF Clause

DECLARE
  v_isbn INVENTORY.ISBN%TYPE;
  v_amount INVENTORY.AMOUNT%TYPE;
  CURSOR inventory_cur IS
    SELECT isbn, amount
    FROM inventory
    WHERE status = 'IN STOCK'
    AND isbn IN (SELECT isbn FROM books WHERE price > 40)
    FOR UPDATE OF amount;
BEGIN
  FOR y IN inventory_cur LOOP
    FETCH inventory_cur INTO v_isbn, v_amount;
    EXIT WHEN inventory_cur%NOTFOUND;
    v_amount := v_amount + 250;
    UPDATE inventory SET amount = v_amount WHERE CURRENT OF inventory_cur;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ROLLBACK;
END;
/

DML and DDL in PL/SQL – DDL Overview

DDL (Data Definition Language) includes:

CREATE
ALTER
DROP
GRANT
REVOKE

These statements define or change the structure of database objects and are not allowed directly in PL/SQL blocks.

Why DDL Fails in PL/SQL Blocks

BEGIN
  CREATE TABLE ddl_table (id NUMBER(10));
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

This results in:

ORA-06550: line 2, column 4:
PLS-00103: Encountered the symbol "CREATE"...

Why?

Because PL/SQL is precompiled, and DDL commands can change object dependencies at runtime, making compile-time checks impossible.

Workaround for DDL in PL/SQL

To execute DDL, use dynamic SQL with EXECUTE IMMEDIATE:

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE ddl_table (id NUMBER(10))';
END;
/

This works because the SQL is interpreted at runtime, not at compile time.

DML and DDL in PL/SQL – Side-by-Side Comparison

Feature	DML (INSERT, UPDATE, DELETE)	DDL (CREATE, DROP, ALTER)
Modifies Data	✅ Yes	❌ No (modifies structure)
Allowed in PL/SQL	✅ Directly supported	❌ Use `EXECUTE IMMEDIATE`
Transactional	✅ Yes (`COMMIT`, `ROLLBACK`)	❌ Auto-commit on execution
Affects Dependencies	❌ No	✅ Yes

Best Practices

Never update the DUAL table for real transactions — use it only to simulate transactional behavior.
Always wrap DML statements with proper exception handling and transaction control.
Use EXECUTE IMMEDIATE for DDL within PL/SQL when dynamic schema changes are required.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.