Built-in Packages (DBMS_OUTPUT, UTL_FILE, DBMS_SCHEDULER)

Oracle ships hundreds of built-in packages. This topic tours the most essential ones with working examples using the Oracle HR schema.

DBMS_OUTPUT — Debug Output

The workhorse for console output during development and debugging:

SET SERVEROUTPUT ON SIZE UNLIMITED;

BEGIN
    DBMS_OUTPUT.ENABLE(1000000);          -- set buffer size (bytes)
    DBMS_OUTPUT.PUT('Part 1 ');           -- no newline
    DBMS_OUTPUT.PUT('Part 2 ');           -- no newline
    DBMS_OUTPUT.NEW_LINE;                 -- newline only
    DBMS_OUTPUT.PUT_LINE('Full line');    -- put + newline

    -- Output from a query loop
    FOR r IN (SELECT first_name, salary FROM employees
              WHERE department_id = 90 ORDER BY salary DESC) LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(r.first_name, 20) || '$' || r.salary);
    END LOOP;
END;
/

Procedure	Description
PUT_LINE(msg)	Write message + newline to buffer
PUT(msg)	Write message without newline
NEW_LINE	Write a newline
ENABLE(size)	Enable output with given buffer size
DISABLE	Disable output

UTL_FILE — Read and Write OS Files

Read and write text (and binary) files in Oracle Directory objects:

-- First: create a directory object pointing to an OS path (DBA task)
-- CREATE OR REPLACE DIRECTORY data_dir AS '/oracle/data/exports';
-- GRANT READ, WRITE ON DIRECTORY data_dir TO hr;

CREATE OR REPLACE PROCEDURE export_dept_salaries(p_dept_id IN NUMBER) IS
    v_file      UTL_FILE.FILE_TYPE;
    v_filename  VARCHAR2(100);
BEGIN
    v_filename := 'dept_' || p_dept_id || '_salaries.csv';

    -- Open file for writing
    v_file := UTL_FILE.FOPEN('DATA_DIR', v_filename, 'w', 32767);

    -- Write header
    UTL_FILE.PUT_LINE(v_file, 'employee_id,full_name,salary,hire_date');

    -- Write data rows
    FOR r IN (SELECT employee_id,
                     first_name || ' ' || last_name AS full_name,
                     salary,
                     TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date
              FROM   employees
              WHERE  department_id = p_dept_id
              ORDER BY last_name) LOOP
        UTL_FILE.PUT_LINE(v_file,
            r.employee_id || ',' ||
            r.full_name   || ',' ||
            r.salary      || ',' ||
            r.hire_date
        );
    END LOOP;

    UTL_FILE.FCLOSE(v_file);
    DBMS_OUTPUT.PUT_LINE('Exported to ' || v_filename);

EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
        RAISE_APPLICATION_ERROR(-20501, 'Invalid directory: DATA_DIR');
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_file) THEN UTL_FILE.FCLOSE(v_file); END IF;
        RAISE;
END export_dept_salaries;
/

Reading a File

DECLARE
    v_file  UTL_FILE.FILE_TYPE;
    v_line  VARCHAR2(32767);
    v_count NUMBER := 0;
BEGIN
    v_file := UTL_FILE.FOPEN('DATA_DIR', 'dept_80_salaries.csv', 'r');

    LOOP
        BEGIN
            UTL_FILE.GET_LINE(v_file, v_line);
            v_count := v_count + 1;
            DBMS_OUTPUT.PUT_LINE(v_count || ': ' || v_line);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;  -- end of file
        END;
    END LOOP;

    UTL_FILE.FCLOSE(v_file);
    DBMS_OUTPUT.PUT_LINE('Read ' || v_count || ' lines.');
END;
/

DBMS_SCHEDULER — Job Scheduling

Schedule PL/SQL code to run on a cron-like schedule:

-- Create a scheduled job
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'MONTHLY_SALARY_REPORT',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN emp_mgr.generate_monthly_report; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=6; BYMINUTE=0',
        enabled         => TRUE,
        comments        => 'Runs salary report on the 1st of each month at 6 AM'
    );
    DBMS_OUTPUT.PUT_LINE('Job created.');
END;
/

-- Run a job immediately
BEGIN
    DBMS_SCHEDULER.RUN_JOB('MONTHLY_SALARY_REPORT');
END;
/

-- Check job status
SELECT job_name, state, last_start_date, last_run_duration, next_run_date
FROM   user_scheduler_jobs;

-- View run history and errors
SELECT job_name, status, actual_start_date, run_duration, error#
FROM   user_scheduler_job_run_details
WHERE  job_name = 'MONTHLY_SALARY_REPORT'
ORDER BY actual_start_date DESC;

-- Disable / enable / drop
BEGIN
    DBMS_SCHEDULER.DISABLE('MONTHLY_SALARY_REPORT');
    -- DBMS_SCHEDULER.ENABLE('MONTHLY_SALARY_REPORT');
    -- DBMS_SCHEDULER.DROP_JOB('MONTHLY_SALARY_REPORT');
END;
/

DBMS_LOB — LOB Manipulation

Work with CLOB and BLOB data:

DECLARE
    v_clob  CLOB;
    v_len   NUMBER;
BEGIN
    -- Create a temporary CLOB
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

    -- Append text
    DBMS_LOB.APPEND(v_clob, 'Employee Report' || CHR(10));
    DBMS_LOB.APPEND(v_clob, '===============' || CHR(10));

    FOR r IN (SELECT first_name, last_name, salary
              FROM   employees WHERE department_id = 90) LOOP
        DBMS_LOB.APPEND(v_clob, r.first_name || ' ' || r.last_name ||
                                 ': $' || r.salary || CHR(10));
    END LOOP;

    v_len := DBMS_LOB.GETLENGTH(v_clob);
    DBMS_OUTPUT.PUT_LINE('Report size: ' || v_len || ' chars');

    -- Read a substring
    DBMS_OUTPUT.PUT_LINE('First 15 chars: ' || DBMS_LOB.SUBSTR(v_clob, 15, 1));

    DBMS_LOB.FREETEMPORARY(v_clob);
END;
/

DBMS_RANDOM — Random Values

DECLARE
    v_int     NUMBER;
    v_float   NUMBER;
    v_string  VARCHAR2(20);
BEGIN
    -- Random integer between 1 and 100
    v_int    := ROUND(DBMS_RANDOM.VALUE(1, 100));

    -- Random float between 0 and 1
    v_float  := DBMS_RANDOM.VALUE;

    -- Random string: 'U'=uppercase, 'L'=lowercase, 'A'=mixed, 'X'=alphanumeric, 'P'=printable
    v_string := DBMS_RANDOM.STRING('U', 10);

    DBMS_OUTPUT.PUT_LINE('Int:    ' || v_int);
    DBMS_OUTPUT.PUT_LINE('Float:  ' || ROUND(v_float, 4));
    DBMS_OUTPUT.PUT_LINE('String: ' || v_string);
END;
/

-- Generate test data
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
SELECT 300 + ROWNUM,
       DBMS_RANDOM.STRING('U', 5),
       DBMS_RANDOM.STRING('U', 8),
       DBMS_RANDOM.STRING('U', 6) || '@test.com',
       SYSDATE - ROUND(DBMS_RANDOM.VALUE(1, 3650)),
       'IT_PROG',
       ROUND(DBMS_RANDOM.VALUE(4000, 15000), 2)
FROM   dual CONNECT BY LEVEL <= 10;
ROLLBACK;  -- clean up test data

UTL_HTTP — HTTP Requests

Call REST APIs or web services from PL/SQL:

DECLARE
    v_req    UTL_HTTP.REQ;
    v_resp   UTL_HTTP.RESP;
    v_buffer VARCHAR2(32767);
    v_body   CLOB := EMPTY_CLOB();
BEGIN
    -- Configure wallet (for HTTPS) — usually set at DB level
    -- UTL_HTTP.SET_WALLET('file:/oracle/wallets/acme', 'wallet_password');

    v_req := UTL_HTTP.BEGIN_REQUEST(
        url    => 'https://api.example.com/employees/100',
        method => 'GET'
    );
    UTL_HTTP.SET_HEADER(v_req, 'Accept', 'application/json');

    v_resp := UTL_HTTP.GET_RESPONSE(v_req);
    DBMS_OUTPUT.PUT_LINE('HTTP Status: ' || v_resp.status_code);

    -- Read response body
    DBMS_LOB.CREATETEMPORARY(v_body, TRUE);
    BEGIN
        LOOP
            UTL_HTTP.READ_TEXT(v_resp, v_buffer, 32767);
            DBMS_LOB.APPEND(v_body, v_buffer);
        END LOOP;
    EXCEPTION
        WHEN UTL_HTTP.END_OF_BODY THEN NULL;
    END;

    UTL_HTTP.END_RESPONSE(v_resp);
    DBMS_OUTPUT.PUT_LINE('Response length: ' || DBMS_LOB.GETLENGTH(v_body));
    DBMS_LOB.FREETEMPORARY(v_body);

EXCEPTION
    WHEN OTHERS THEN
        IF v_resp.private_hndl IS NOT NULL THEN
            UTL_HTTP.END_RESPONSE(v_resp);
        END IF;
        RAISE;
END;
/

DBMS_UTILITY — Utility Functions

BEGIN
    -- Format error stack (better than SQLERRM for nested errors)
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);

    -- Format call stack (who called whom)
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);

    -- Format error backtrace (which line raised the exception)
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

    -- Recompile all invalid objects in a schema
    DBMS_UTILITY.COMPILE_SCHEMA(schema => 'HR', compile_all => FALSE);

    -- Get the current user's schema
    DBMS_OUTPUT.PUT_LINE('Schema: ' || DBMS_UTILITY.CURRENT_SCHEMA);
END;
/

DBMS_LOCK.SLEEP — Pause Execution

BEGIN
    DBMS_OUTPUT.PUT_LINE('Sleeping for 2 seconds...');
    DBMS_SESSION.SLEEP(2);   -- Oracle 18c+
    -- DBMS_LOCK.SLEEP(2);   -- older alternative (requires EXECUTE on DBMS_LOCK)
    DBMS_OUTPUT.PUT_LINE('Done.');
END;
/

DBMS_STATS — Object Statistics

BEGIN
    -- Gather stats on the employees table
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname    => 'HR',
        tabname    => 'EMPLOYEES',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt => 'FOR ALL COLUMNS SIZE AUTO',
        cascade    => TRUE   -- include indexes
    );
    DBMS_OUTPUT.PUT_LINE('Statistics gathered.');

    -- Check when stats were last gathered
    SELECT table_name, last_analyzed, num_rows
    FROM   user_tables
    WHERE  table_name = 'EMPLOYEES';

END;
/

Quick Reference

Package	Key Use
DBMS_OUTPUT	Console output for debugging
UTL_FILE	Read/write OS files via Directory objects
DBMS_SCHEDULER	Cron-style job scheduling
DBMS_LOB	Create, read, write, append CLOB/BLOB data
DBMS_RANDOM	Random numbers, floats, strings
UTL_HTTP	HTTP/HTTPS requests to web services
DBMS_UTILITY	Error stacks, schema recompile, metadata
DBMS_SESSION / DBMS_LOCK	Session control, sleep/pause
DBMS_STATS	Gather and manage optimizer statistics
DBMS_SQL	Advanced dynamic SQL (see Dynamic SQL topic)
DBMS_CRYPTO	Encryption, hashing, MACs
DBMS_PIPE	Inter-session communication via named pipes
DBMS_ALERT	Event notification between sessions

Summary

• DBMS_OUTPUT.PUT_LINE is your first debugging tool — enable with SET SERVEROUTPUT ON.
• UTL_FILE reads/writes OS files through Oracle Directory objects — always close files in exception handlers.
• DBMS_SCHEDULER schedules PL/SQL jobs with cron-like repeat intervals — inspect via user_scheduler_job_run_details.
• DBMS_LOB handles CLOBs and BLOBs: create temporary LOBs, append, read substrings, free when done.
• DBMS_RANDOM generates test data — useful for populating demo tables.
• UTL_HTTP calls REST APIs — configure wallet for HTTPS; always call END_RESPONSE in exception handlers.
• DBMS_STATS.GATHER_TABLE_STATS keeps optimizer statistics current — stale stats cause bad query plans.