PL/SQL Basics for DBAs

Oracle DBA Certification 14 min min read Updated: Nov 16, 2026 Beginner
PL/SQL Basics for DBAs
Beginner Topic 5 of 5

What is PL/SQL?

PL/SQL is Oracle's programming language that runs inside the database. It lets you combine SQL with variables, loops and decisions. DBAs use it for maintenance scripts, jobs and quick automation.

The Block Structure

sql SET SERVEROUTPUT ON DECLARE v_name VARCHAR2(30) := 'DBA'; BEGIN DBMS_OUTPUT.PUT_LINE('Hello ' || v_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); END; /
SectionPurpose
DECLAREVariables (optional)
BEGIN ... ENDThe code to run (required)
EXCEPTIONWhat to do when an error happens (optional)
/Tells SQL*Plus to run the block
Remember: Run SET SERVEROUTPUT ON once per session or you will not see DBMS_OUTPUT messages.

Variables and IF

sql DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM shop.orders; IF v_count > 5 THEN DBMS_OUTPUT.PUT_LINE('Busy shop: ' || v_count || ' orders'); ELSE DBMS_OUTPUT.PUT_LINE('Quiet shop'); END IF; END; /

Loops

sql BEGIN FOR i IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE('Round ' || i); END LOOP; END; / -- loop over query rows (a cursor FOR loop) BEGIN FOR r IN (SELECT full_name, city FROM shop.customers) LOOP DBMS_OUTPUT.PUT_LINE(r.full_name || ' - ' || NVL(r.city,'?')); END LOOP; END; /

Stored Procedure

sql CREATE OR REPLACE PROCEDURE shop.cancel_old_orders (p_days IN NUMBER) AS v_rows NUMBER; BEGIN UPDATE shop.orders SET status = 'CANCELLED' WHERE status = 'NEW' AND order_date < SYSDATE - p_days; v_rows := SQL%ROWCOUNT; COMMIT; DBMS_OUTPUT.PUT_LINE(v_rows || ' orders cancelled'); END; / EXEC shop.cancel_old_orders(30)
SQL%ROWCOUNT: After an INSERT, UPDATE or DELETE, SQL%ROWCOUNT tells you how many rows were affected. Great for logging.

Handling Errors

sql BEGIN INSERT INTO shop.customers (full_name, email) VALUES ('Dup', 'asha@example.com'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('That email already exists'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unexpected: ' || SQLERRM); RAISE; END; /

A DBA-Style Example

Find the biggest tables in a schema and print them:

sql BEGIN FOR r IN (SELECT segment_name, ROUND(bytes/1024/1024,2) AS mb FROM user_segments WHERE segment_type = 'TABLE' ORDER BY bytes DESC FETCH FIRST 5 ROWS ONLY) LOOP DBMS_OUTPUT.PUT_LINE(RPAD(r.segment_name,30) || r.mb || ' MB'); END LOOP; END; /

Conclusion

PL/SQL blocks, loops and procedures let you automate the boring parts of DBA work. Always handle errors and log what you changed.

Quick Summary: DECLARE, BEGIN, EXCEPTION, END plus a slash. Use SQL%ROWCOUNT and SET SERVEROUTPUT ON.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators