Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

All of the Formatting Options for your Query Results

1 Min Read

This would be for both SQLcl and SQL Developer, but a few are reserved for SQLcl today.

You have two options for formatting your query output:

  • SET SQLFORMAT
  • Adding a comment to your query /*json-formatted*/
Setting it for the session or just for a specific query.

Here are some slides demonstrating all the cool format options you have available:

Some things you may have forgotten about…

Just run ‘help set sqlformat’ for all the details:

Kris talks about the new pattern matching/highlighting feature included with ANSICONSOLE now in version 19.1.

And, did you know you can create your own formatting options? Just write a bit of code (js, python, perl, whatever Nashorn supports!)

My JS is horrible so I just steal it off the internet.

SQL Developer Web, Too!

When we share code between our tools, everyone wins!

TL;DR – The Movie!

Skip to 1:52 for the demo.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

2 Comments

  1. Moussa Reply

    Hello, Jeff,
    I have the script below that I wrote using sqlcl. For some reason, after running it, the result comes with double quotes around all the fields and values. What should I do to remove double quotes?

    SET SQLFORMAT DELIMITED |
    SET SQLFORMAT DELIMITER
    SET SQLFORMAT QUOTE OFF
    SET HEADING OFF
    SET FEEDBACK OFF
    SET PAGESIZE 0
    SET LINESIZE 9999
    SET TRIMSPOOL ON
    SET TRIMOUT ON
    SET VERIFY OFF
    –Turns off the Terminal Output
    SET TERMOUT OFF
    SET SERVEROUTPUT OFF

    — Create a substitution variable for the current date
    COLUMN current_date NEW_VALUE today_date NOPRINT
    SELECT TO_CHAR(SYSDATE, ‘MMDDYYYY’) AS current_date FROM DUAL;

    — SPOOL the output to a file with the current date
    SPOOL C:\Test_file_&today_date..txt

    — SQL Query fetching data from RELIANCEMTX_OUTPUT_VIEW
    SELECT date_of_birth
    ,date_of_hire_most_recent
    ,date_of_hire_original
    ,ssn
    ,employee_id
    ,first_name
    ,middle_initial
    ,last_name
    ,home_address_1
    ,home_address_2
    ,home_city
    ,home_state
    ,home_zip
    ,marital_status
    ,gender
    ,work_state
    ,department_name
    ,position
    ,is_active
    ,is_fulltime
    ,is_exempt
    ,annual_salary
    ,hourly_rate
    ,subgroup_a
    ,subgroup_b
    ,subgroup_c
    ,subgroup_d
    ,subgroup_e
    ,subgroup_f
    ,is_std_eligible
    ,std_plan_effective
    ,is_ltd_eligible
    ,ltd_effective_date
    ,hours_worked
    ,key_employee_indicator
    ,emp_50_in_75_mile_indicator
    ,add_data_01
    ,add_data_02
    ,add_data_03
    ,contact_1_email_address
    ,contact_2_email_address
    ,termination_date
    ,home_mobile_phone

    FROM utput_view;
    — Turn off SPOOL
    SPOOL OFF

    • thatjeffsmith Post Author

      There’s no way from the CLI/Script engine, you’ll need to use the GUI export and set the quote characters to null.

Write A Comment