# Generate Word, Excel and PowerPoint from Oracle SQLcl

I previously posted a [video](https://youtu.be/ZNublsu8DU8) showing how to do this, but I thought it would make a good blog post (if only for my future self!).

## SQLcl and JavaScript

SQLcl has a lovely, relatively underused (IMHO) feature: the `script` command. Give it some JavaScript and it runs it, right there inside SQLcl, with the database connection accessible to your JavaScript code. It also makes it possible to add your own commands to the SQL prompt.

Try it on a recent SQLcl, though, and here's what you get. This is the stock 26.1.2 version I used

```plaintext
SQL> script js1.js
!ScriptCommand.1!
```

Not even a proper error message. Just the message key, which tells you something's gone wrong, but not exactly what.

So in this post I'll get `script` working again, add a custom command to SQLcl with it, and use that command for something I think every Oracle developer might find useful, run one command at the SQL prompt and generate a PowerPoint deck documenting every table in my schema.

The command uses [ETCH](https://get-etch.app), the document engine I've been building at [ETCH Labs](https://etch-labs.com), which generates Word, Excel and PowerPoint documents from your data using flexible templates.

## Why doesn't the JavaScript work?

As I understand it, SQLcl used to use the JavaScript engine that came with Java, called Nashorn. As Jeff Smith explains in [his post on running JavaScript in SQLcl](https://www.thatjeffsmith.com/archive/2022/04/running-javascript-in-oracle-sqlcl-22-1/), Nashorn was deprecated in Java 11 and removed completely in Java 15. Modern SQLcl needs a modern Java, so the engine `script` relied on simply isn't there any more.

For a while the answer was to run SQLcl on GraalVM with its JavaScript plugin, which is what Jeff's post and [Philipp Hartenfeller's post for macOS](https://hartenfeller.dev/blog/sqlcl-js-graalvm-macos) describe. But as Philipp's update notes, newer GraalVM releases stopped shipping that plugin, so that route has been getting harder to get working.

Philipp Salvisberg has a great blog post [The Return of SQLcl's SCRIPT Command](https://www.salvis.com/blog/2026/07/25/the-return-of-sqlcls-script-command/). The trick is that GraalJS, the JavaScript engine from the GraalVM project, is also published as ordinary jar files on Maven Central. If you put the right jars where SQLcl looks for extra libraries, then the `script` command is able to load the JavaScript library and is able to run your JavaScript. Philipp has a script that does it for you, [sqlcl-js-patch](https://github.com/PhilippSalvisberg/sqlcl-js-patch), and I'd start there.

For the example in this post I've used the same approach, with a small script `get-graaljs.sh` which downloads the nine GraalJS 25.1.3 jars from Maven Central, checks each one against a SHA-256 written in the script, and copies them into SQLcl's `lib/ext` folder only if all nine match.

```plaintext
[jes@mbp ~]$ ./get-graaljs.sh ~/tools/sqlcl
```

And now:

```plaintext
SQL> script js1.js
JavaScript works
```

Two things worth knowing before you rely on it. First, **a SQLcl upgrade replaces** `lib/ext`, so you'll need to run it again after every upgrade. Second, this isn't something Oracle ships or supports. It's adding jars to SQLcl and could be version fragile. For a custom command, that's a trade I'm happy to make.

## The catch

There's a catch, though. SQLcl runs `script` files with GraalJS's Nashorn compatibility switched on, and that seems to keep you to using ECMAScript 5. So for example this

```javascript
var f = () => 1;
```

gets you:

```plaintext
SyntaxError: <eval>:1:9 Expected an operand but found )
var f = () => 1;
         ^
```

No arrow functions, no classes, no promises, no `async`. That's fine for a quick script. It's not fine if you want to use a modern JavaScript library, which is exactly what I wanted to do.

The way round it turned out to be rather neat. GraalJS is right there on the classpath, so the ES5 script can build a *second* JavaScript context with its own settings, and hand the real work to that:

```javascript
var Context = Java.type("org.graalvm.polyglot.Context");
var c = Context.newBuilder("js")
  .allowAllAccess(true)
  .option("js.ecmascript-version", "2022")
  .build();
```

Everything evaluated in the new context `c` gets evaluated by ES2022.\
\
**Note: if there's a better way to do this or I'm misunderstanding it let me know!**

## Adding a command to SQLcl

SQLcl lets a script register a listener that sees every statement you type, and claim the ones it wants to execute. Here's the registration for our new command -

```javascript
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener");

var EtchCommand = Java.extend(CommandListener, {
  handleEvent: function (conn, ctx, cmd) {
    var sql = String(cmd.getSql()).trim();
    if (!/^etch(\s|$)/i.test(sql)) return false;   // not ours, let SQLcl carry on
    etchCore()(conn, ctx, sql.substring(4));        // the ES2022 half does the work
    return true;
  },
  beginEvent: function (conn, ctx, cmd) {},
  endEvent: function (conn, ctx, cmd) {}
});
new EtchCommand();
CommandRegistry.addForAllStmtsListener(EtchCommand["class"]);
```

Notice that `handleEvent` gets SQLcl's own connection, `conn`. So the command queries whatever you're connected to, with no passwords or connect strings of its own.

Put one line in your `login.sql` and the command is there every time SQLcl starts:

```sql
script /path/to/integrations/sqlcl/etch-command.js
```

(One small gotcha I found, SQLcl won't let a `login.sql` in the current folder touch the database. It tells you so — "login.sql found in the CWD. DB access is restricted for login.sql." — so keep yours in a folder listed in `SQLPATH`.)

## Using the etch command

The command is called `etch`, after [ETCH](https://get-etch.app). You give it a Word, Excel or PowerPoint template, a file to write, and a query:

```plaintext
SQL> etch <template> <output> '<query>'
SQL> etch <template> <output> @file.sql
```

It runs the query using SQLcl's connection, sends the rows and the template to ETCH with the ETCH JavaScript client, and writes the document. (The client was written for Node and browsers, so there's also a small file of shims that gives it `fetch`, `FormData` and others, built on Java's HTTP classes.)

The simplest case is a flat query like this

```plaintext
SQL> etch templates/orders.xlsx orders.xlsx 'select * from orders order by order_id'
etch: 40 rows -> orders.xlsx (16514 bytes, 246 ms)
```

Here it is, open in Excel: all forty orders, with data bars on the totals and the overdue ones shaded.

![The orders workbook open in Excel: forty DEMO orders with customer, email, product, quantity, date, status and total, data bars on the totals, and the overdue orders shaded red](https://cdn.hashnode.com/uploads/covers/6ab92eb45048a85288a71c45/09a11b5e-b655-40db-92e0-178ee38ffc76.png align="center")

The template has a second sheet too, and that one's worked out by Excel itself from the rows: one line per status with its orders and revenue, and a chart that grows with the list.

![The Summary sheet in Excel: orders and revenue for Delivered, Overdue, Processing and Shipped, with a Revenue by status bar chart](https://cdn.hashnode.com/uploads/covers/6ab92eb45048a85288a71c45/0aecb15e-3b67-4eac-8d5b-40f458cd66cf.png align="center")

But the one I really wanted was this.

## Documenting a schema with one command

Recent Oracle releases include `DBMS_DEVELOPER`, and the `GET_METADATA` function hands back a table's columns, constraints, indexes and statistics as JSON. So one query can describe a whole schema:

```sql
select json_serialize(
         json_object(
           'schema'    value user,
           'generated' value to_char(sysdate, 'YYYY-MM-DD'),
           'tables'    value json_arrayagg(
                          json_object(
                            'comment'  value c.comments,
                            'metadata' value dbms_developer.get_metadata(name => t.table_name, level => 'ALL')
                            returning clob)
                          order by t.table_name returning clob)
           returning clob)
         returning clob) as doc
from user_tables t
left join user_tab_comments c on c.table_name = t.table_name
where t.table_name not like 'DBTOOLS$%'
```

When a query returns a single JSON document like this, the `etch` command can use the JSON as the data, so it can be any shape you wish. Notice the `returning clob` everywhere since without it we'd default to `varchar2` and might hit size limits.

The ETCH template is a PowerPoint deck built by hand with a title slide, a summary table, and one slide that repeats for every table. Then:

![SQLcl: sql -s -name ETCH_TUTORIAL, then the etch command for schema.pptx reporting 1 JSON document, and the etch command for orders.xlsx reporting 40 rows](https://cdn.hashnode.com/uploads/covers/6ab92eb45048a85288a71c45/9e1a7c68-368e-4f43-b16a-b86613792da3.png align="center")

A few seconds later, there's the deck, open in PowerPoint: a title slide, an overview, and a slide for every table in the schema.

![The schema deck in PowerPoint's Slide Sorter: a title slide, The tables at a glance, then one slide each for CATEGORIES, CUSTOMERS, ETCH_LOG, INVENTORY, INVOICE_FILES, ORDERS, PRODUCTS, PRODUCT_SUPPLIERS, SHIPMENTS, SUPPLIERS and WAREHOUSES](https://cdn.hashnode.com/uploads/covers/6ab92eb45048a85288a71c45/2203883b-394e-4ae4-8121-5cbf52337cb5.png align="center")

The overview has every table with its row count, columns, indexes and when it was last analysed:

![The tables at a glance: eleven tables, each with its rows, columns, indexes and last-analysed date](https://cdn.hashnode.com/uploads/covers/6ab92eb45048a85288a71c45/e9508165-c719-43a6-9797-bd79307bb4be.png align="center")

And then each table gets its own slide, with the table comment, the columns and their types, the keys, the constraints (with the foreign keys pointing at the tables they reference), the indexes and the statistics:

![The INVENTORY slide: five columns with types, nulls and keys, statistics, the check, primary and foreign key constraints, and two indexes](https://cdn.hashnode.com/uploads/covers/6ab92eb45048a85288a71c45/fe63838b-5cb6-46c5-befa-cbcaa64246bf.png align="center")

Note because you can design the template however you wish in ETCH you could make it look however you want to and include any schema level information you wish.

And because it's one simple command, it's never out of date. Re-run it whenever you wish, automate it with cron etc and you always get an up to date document of your schema.

## Nested JSON Data

The JSON trick works for nested data too. This query builds a list of customers, each with their own orders inside, using `json_arrayagg`:

```plaintext
SQL> etch templates/per-customer.xlsx per-customer.xlsx @sql/per-customer.sql
etch: 1 JSON document -> per-customer.xlsx (25334 bytes, 378 ms)
```

The template has a single sheet that repeats once per customer, so that's a workbook with twenty tabs, each named after a customer and holding just their orders:

![The per-customer workbook in Excel: Amara Mockup's sheet with her two orders, and a row of tabs named after each customer](https://cdn.hashnode.com/uploads/covers/6ab92eb45048a85288a71c45/54f2a34c-2ffe-4ff5-bb7e-3cd573ef16c8.png align="center")

A BLOB column works as well. In the example there's a Word product list where each product's photo comes straight out of the database, sent to ETCH as a binary attachment rather than as base64 text:

![The products document in Word: a table of products, each with its photo from the database, its category and its price](https://cdn.hashnode.com/uploads/covers/6ab92eb45048a85288a71c45/95f4b7c0-e1b9-45bf-9592-3380f13da303.png align="center")

## Try it yourself

Everything is in the [etch-client-examples repository](https://github.com/etch-labs/etch-client-examples/tree/main/integrations/sqlcl) on GitHub: the command, the GraalJS script (and a PowerShell version for Windows), the templates, the queries, and a `setup.sql` that creates the sample tables if you'd like to play with those first. You'll need SQLcl 26.3, and an [ETCH](https://get-etch.app) account (the free plan is fine) for the API key. For the schema deck, check you've got `DBMS_DEVELOPER` with `desc dbms_developer`; I ran it on 26ai Free, 23.26.1.

And if you'd rather watch than read, here's the video again: [Generate Word, Excel and PowerPoint from Oracle SQLcl using ETCH](https://youtu.be/ZNublsu8DU8). There's more on what ETCH can do at [get-etch.app](https://get-etch.app).
