Your SQL Developer & SQLcl Command Cheat Sheet

When I first got started with UNIX and vi/emacs, I lived and died by a nice, printed cheat sheet. And after awhile, I didn’t need it anymore. I’m pretty sure I still have it over in my garage…squirrel (I’m easily distracted)!

Anyways, I got a request from @manivel_j for something similar, but for all of the client based commands in SQL Developer.

What do we mean, SQL Developer based commands? These are things the CLIENT (SQLDev) is responsible for, versus the SERVER (the Oracle Database). One of the most well known of these is DESC(ribe).

If you run that directly against a Database, it won’t work. But used in SQLPlus, SQLcl, SQL Developer, or any other tool that has decided to implement a version of that command – you’re good to go.

There’s an easy way to see these list of commands, simply type and run ‘help’ in either SQLcl or SQL Developer.

And if you say ‘help <command>’ you can get the help for that specific command.

Cheat-sheet for non-SQL*Plus commands

SQL*Plus is the granddaddy of Oracle clients. It originated many of the commands we know, and mostly love, today. I’m going to talk about the ones that we have added. You might also want to see this post on SQLcl commands being ran in SQL Developer.

So what’s new?

In SQLcl, if you run HELP, the commands we’ve added are underlined (OS X) or highlighted (Windows).

Looking for a cheat-sheet for keyboard shortcuts?

In alphabetical order…

ALIAS – you can build your own commands, with :binds, to make tedious SQL or PL/SQL to type, easy to run.

APEX – Get a list of APEX applications in your schema, or import and export them, to and from files.

BRIDGE – Let’s you easily move objects and data from one database to another, w/o DB_LINKs. This one is fairly old, going back as far as 2010.

CD – Change the current working directory. As in, tell the client where you want to SPOOL files to, or where you want to look for files to @execute. Crazy idea, right?

CTAS – Create TABLE As SELECT. We’ll help you build a usuable, CTAS command for a given table or view.

DDL – Generate DDL for an object. Uses DBMS_METADATA, but you don’t have to write that code. Also comes with settings, via SET DDL.

FIND – can you find my file on the $SQLPath?

FORMAT – invoke the SQL Developer formatter on the contents of your command buffer, a file, or an entire directory.

HISTORY – all the queries you’ve ever ran in SQLcl.

INFORMATION – INFO or INFO+…a better, much better version of DEScribe. Like, what’s my primary key, do I have any stats, comments, foreign keys, etc? DESC still works of course.

Liquibase (LB) – version control your Oracle Database Schema – either do an UPDATE, ROLLBACK, or we’ll generate changesets for you using GENSCHEMA or GENOBJECT.

LOAD – Take CSV and shove it into a table. Useful when you don’t have SQL*Loader handy.

OERR – Oracle Errror Message Lookups, for ORA and PLS errors. Because Google take too many clicks.

REPEAT – run a command, repeatedly, for a set amount of times, at a certain delay…refresh the screen each run. Tail the alert log?

REST – ORDS stuff, get your ORDS modules, enabled schemas, privileges, etc.

SCRIPT – invoke the javascript engine to do stuff on the client, often in coordination with the things you’re doing in your Oracle connection. Like say...take all these files and load them as BLOBs to my table!

SODA – take your {json} file and create a new collection (table) in your Oracle Database. It’s much more than that, but you get the idea.

SSHTUNNEL – we’ll build a SSH Tunnel you can use to connect to databases not directly accessible via the Listener port.

TNSPING – can we find your database, and, how far away is it?

VAULT – using Hashicorp to securely store your Oracle database credentials.

WHICH – similar to find, goes through your $SQLPath and tells you exactly what file will be executed when you @file

SET and SHOW

These aren’t new, but there are MANY new things you can SET or SHOW for SQLcl and SQL Developer.

SHOW ALL and SHOW ALL+ will show you, a lot. SHOW TNS is fun. So is SHOW CONNECTION.

For SET, don’t miss out on SET DDL and SET SQLFORMAT.

Some of these work in SQLDev Web, too!

It’s common code across all of our tools, whether you’re in a desktop GUI, CLI, or your browser. There are exceptions of course, but INFO in one tool will show the same output in our other tools.