Beyond SQL*Plus: 7 Game-Changing Oracle SQLcl Features Every DBA & Developer Should Use

Executive Summary: Modernizing the Oracle Database CLI

or decades, SQL*Plus served as the default command-line interface for Oracle DBAs and developers. However, managing modern enterprise databases using SQL*Plus requires tedious formatting commands (SET LINESIZE 300, SET PAGESIZE 500, COLUMN format A20), verbose PL/SQL metadata extractions, and external utilities for basic data imports.

Oracle SQLcl (SQL Developer Command Line) is a free, lightweight Java-based CLI that combines full backward compatibility with SQL*Plus scripts alongside modern developer utilities.

This guide explores 7 hidden efficiency features in SQLcl that transform daily database administration and PL/SQL development.

---

1. Instant Multi-Format Output (SET SQLFORMAT)

In SQL*Plus, displaying wide table queries without wrapped, unreadable output requires writing extensive COLUMN formatting rules. In SQLcl, the SET SQLFORMAT command dynamically transforms query results into structured formats:

-- Format output as formatted JSON
SET SQLFORMAT json-pretty;
SELECT employee_id, first_name, salary, department_id FROM hr.employees WHERE department_id = 60;

-- Format output as GitHub Markdown table for documentation
SET SQLFORMAT md;
SELECT tablespace_name, bytes/1024/1024 AS size_mb FROM dba_data_files WHERE ROWNUM <= 3;

-- Format output as clean ANSI Console (Auto-fit columns without wrapping)
SET SQLFORMAT ansiconsole;
SELECT username, account_status, created FROM dba_users WHERE ROWNUM <= 5;

-- Reset to default
SET SQLFORMAT default;

Supported Formats:

ansiconsole | json | json-pretty | csv | html | xml | md | insert | loader

---

2. One-Word DDL Extraction (DDL)

Extracting object DDL in SQL*Plus required calling DBMS_METADATA.GET_DDL(...) with complex LONG chunking settings. SQLcl introduces the native DDL command:

-- Extract clean DDL for any table, view, index, or package
DDL hr.employees

-- Extract DDL for a PL/SQL package specification & body
DDL hr.emp_mgmt_pkg

💡 Tip: Combine DDL with spooling to quickly extract schema definitions into version-control repositories.

---

3. Built-in Schema Versioning with Liquibase (LIQUIBASE)

SQLcl includes a fully integrated engine for Liquibase, enabling automated, version-controlled database migrations directly from the SQL prompt without installing external tools:

-- Generate Liquibase XML/SQL changelog for an entire schema
LIQUIBASE generate-schema -split

-- Apply pending schema updates to Target Environment (STAGE / PROD)
LIQUIBASE update -changelog-file controller.xml

This replaces manual .sql deployment scripts with trackable, rollback-capable schema migrations.

---

4. Bulk CSV Data Loading Without Control Files (LOAD)

Setting up SQL*Loader control (.ctl) files for quick CSV imports is time-consuming. SQLcl’s native LOAD command parses CSV headers, maps columns automatically, and bulk inserts data:

-- Create target table structure
CREATE TABLE hr.stage_sales (
    sale_id NUMBER,
    region VARCHAR2(50),
    amount NUMBER
);

-- Load CSV data directly (auto-detects delimiter & data types)
LOAD hr.stage_sales data_export_2026.csv

---

5. Inline Query History & Search (HISTORY)

Unlike SQL*Plus, SQLcl maintains an interactive command history buffer across sessions:

-- Display recent command history
HISTORY

-- Display detailed execution history with timestamps
HISTORY FULL

-- Re-execute item #14 from history buffer
HISTORY 14

-- Clear current session history buffer
HISTORY CLEAR

---

6. Built-in Client-Side Scripting (SCRIPT)

SQLcl allows embedding JavaScript, Python, or Groovy scripts directly inside your SQL execution flow to manipulate data or perform logic before sending SQL to the database:

-- Execute client-side JavaScript logic to check database connectivity & session stats
SCRIPT
var ctx = module.getNamedObject('ctx');
var util = module.getNamedObject('util');

ctx.write("\n=== DBPros Automated Diagnostic Check ===\n");
util.execute("SELECT name, open_mode, log_mode FROM v$database;");
ctx.write("=========================================\n");
/

---

7. Alias Creation for Custom DBA Shortcuts (ALIAS)

Stop copying and pasting 50-line diagnostic queries for lock contention or tablespace usage. SQLcl allows creating persistent aliases:

-- Create a persistent custom DBA diagnostic shortcut
ALIAS tblspace=SELECT tablespace_name, round(used_percent,2) AS pct_used FROM dba_tablespace_usage_metrics;

-- Execute the alias anytime from the prompt
tblspace

📚 Official Documentation & Technical References

• Liquibase Official Documentation Home — Core documentation portal for database change management, changelogs, and deployment automation.
• ORACLE-BASE: Oracle SQLcl - Installation and First Impressions — Tim Hall’s guide to installing SQLcl, using SET SQLFORMAT, and scripting capabilities.
• ORACLE-BASE: Oracle SQLcl - Liquibase Integration — Practical guide to database change management and schema migration using SQLcl and Liquibase.

---

Need assistance modernizing your Oracle database deployment pipelines or developer tooling? Contact our Infrastructure Specialists or explore our Enterprise Health Audits.