ThatJeffSmith

A Quick 4.1 Trick: SET SQLFORMAT

Tell Others About This Story:

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.

For example.

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.

No need to hack up your queries anymore with the format you want - you can now set it as a default for ALL of your script output.

No need to hack up your queries anymore with the format you want – you can now set it as a default for ALL of your script output.

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.

we try to make the query output as 'pretty' as possible

we try to make the query output as ‘pretty’ as possible

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Similar Posts by Content Area: , , ,