SQLcl: Moving Beyond SQL*Plus for Oracle Automation

The problem with SQL*Plus in 2024

SQL*Plus has been the default Oracle command-line client for decades. It works, but it shows its age when you try to embed it in a CI/CD pipeline: no structured output, no scripting language beyond basic substitution variables, and no built-in schema versioning. Teams end up wrapping SQL*Plus in shell scripts, Python, or custom Java just to get JSON output or conditional logic.

SQLcl (SQL Developer Command Line) is Oracle's modern replacement. It ships with SQL Developer and as a standalone download. The big shift: a JavaScript/TypeScript engine, embedded Liquibase, and output formats that play nice with jq, GitHub Actions, and GitLab CI.

Scripting with JavaScript and TypeScript

SQLcl runs on the JVM. On Java 8–14 it uses Nashorn; on Java 11+ it can use GraalJS (recommended). That means you can write loops, call REST endpoints, and manipulate result sets in a real programming language instead of SQL*Plus WHENEVER and COLUMN commands.

Example: a script that connects, runs a parameterized query, and writes JSON for a downstream step.

// file: list_dept.js
// Run with: sql -script list_dept.js
var deptId = 10;
var sql = "select ename, job, sal from emp where deptno = ?";
var result = util.executeReturnList(sql, [deptId], ctx);

// util.executeReturnList returns a Java List of HashMaps
var output = [];
for (var i = 0; i < result.size(); i++) {
  output.push(result.get(i));
}
ctx.write(JSON.stringify(output, null, 2));

Run it:

sql -script list_dept.js

Output is clean JSON, ready for jq or a pipeline artifact. No SET HEADING OFF or SET FEEDBACK OFF boilerplate required.

Liquibase for schema deployments

Since version 19.4, SQLcl bundles Liquibase. You get lb update, lb generateChangeLog, and lb snapshot without installing a separate CLI. Changelogs live in Git; the lb command runs them against any Oracle target (on-prem, ATP, ADB).

Typical flow:

1. Store changelog XML/YAML in your repo.
2. In CI: sql /nolog <
3. Exit code surfaces ORA errors to the pipeline.

Caveat: the embedded Liquibase version lags the upstream release (e.g., 4.15 in SQLcl 23.1). If you need the latest changelog types or features, you'll still need the standalone Liquibase CLI.

Pipeline-friendly output and connections

SQLcl supports SET SQLFORMAT JSON (also XML, CSV, HTML, Markdown, INSERT, ANSICONSOLE). Combine with SPOOL to produce machine-readable artifacts.

SET SQLFORMAT JSON
SPOOL build/employees.json
SELECT employee_id, last_name, hire_date FROM employees WHERE department_id = 10;
SPOOL OFF

Connection management uses ALIAS to store named profiles (including wallet paths for Autonomous Database). In a pipeline you can do:

ALIAS CREATE prod_atp -wallet /opt/oracle/wallet -db 2026_high
sql prod_atp @deploy.sql

No TNS_ADMIN or ORACLE_HOME required at runtime. For long-running jobs, remember that wallet tokens don't auto-rotate; your script must handle refresh.

Trade-offs and limitations

• Java version dictates the JS engine. Nashorn is deprecated and removed in JDK 15. Use JDK 17+ LTS with GraalJS (flags --polyglot --jvm-arg=--enable-preview) for stability.
• Startup latency. Cold JVM start ~1–2 s. GraalVM native-image (experimental) drops to ~100 ms but isn't production-supported.
• Not a drop-in SQL*Plus clone. Obscure commands like COLUMN FORMAT and BREAK ON are missing or behave differently. Test existing scripts before swapping.
• No native Windows executable. Runs via sql.bat / sql shell script. Container image (container-registry.oracle.com/database/sqlcl) is ~400 MB but simplifies CI.
• No PL/SQL remote debugging. Use SQL Developer GUI or DBMS_DEBUG_JDWP with an IDE for that.

Try it this week

1. Download the latest SQLcl from sqlcl.oracle.com or pull the container image.
2. Run sql -version to confirm the build (e.g., SQLcl: Release 23.4 Production).
3. Test the JS engine: sql -script test.js with ctx.write('JS engine: ' + java.lang.System.getProperty('java.version'));.
4. Verify Liquibase: sql /nolog then lb version.
5. Replace one SQL*Plus wrapper script with a .js file and SET SQLFORMAT JSON. Measure the reduction in glue code.

SQLcl won't replace every SQL*Plus use case tomorrow, but for automation and schema deployments it removes layers of fragile shell scripting. Start with one pipeline job and expand from there.