One of your favorite SQL Developer ‘tricks’ is the ability to pre-format query output. So instead of getting standard output back, maybe you want query results to come back as CSV.
But using that requires you to add code to your existing SQLs. Maybe instead it would be cool to set the overall script output format?
Now that we have our own SQL*Plus command line interface (AKA SQLcl), the commands that are available there are now also available in SQL Developer proper.
For example: instead of hacking up your individual statements to get your query results to be formatted to CSV, HTML, XML, etc., you can use the SET SQLFORMAT command to set the desired script output format for your SQL queries.
Instead of running:
SELECT /*csv*/ * from HR.EMPLOYEES;
I can run
SET SQLFORMAT csv
SELECT * FROM HR.EMPLOYEES;
When I’m done with getting the output in that format, I can ‘UNSET’ it…and get the standard output back.
In addition to the normal formats, we now have a new one, ANSICONSOLE. One of the benefits, we bring the results back all at once, and check the column widths, and then resize the output such that it’s easier to read. No need to set column widths with various SQL*Plus formatting commands.
- csv – COMMA separated values
- html – html table plus some js to provide a search box/highlighting
- ansiconsole – ‘smart’ formats the output to best fit the screen based on width of data per page of results and the width of your output panel
- insert – INSERT statements
- loader – sql*loader
- delimited – defaults to ‘,’ but can be set via SET SQLFORMAT DELIMITED delimiter left-enclosure right-enclosure
SET SQLFORMAT DELIMITED ; ‘ ‘ – will give you semicolon delimited fields with strings single quoted.
- New for version 19.2: json-formatted – same as json, but ‘pretty printed’ to be easier to read for humans