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

Available Formats

  • csv – COMMA separated values
  • html – html table plus some js to provide a search box/highlighting
  • xml
  • json
  • 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
  • fixed
  • default
  • 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 

If you want more details, I show examples of each on here.

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.

76 Comments

  1. Hi Jeff,

    I’m not able to use TTITLE when i’m generating the output to html

    set sqlformat html
    spool E:\tesT1.html
    TITLE ‘PMODELN’ ON
    select * from xyz;
    spool off

    • sqlformat overides any SQLPlus formatting commands, so use one, or the other – but not both

  2. Hi, Jeff.

    Thanks for the tip.

    I would like to ask: In MS-SQL, when (for example), before executing a SELECT statement, the “Results tro Text” option is enabled and (once the SELECT statement is executed), the result is in plain text.

    Example:

    MTJ_NCODE MTJ_CNAME MTJ_DESCRIPTION
    ———– ————————————————– —————–
    1 NORMAL NORMAL
    2 ANOTHER DATA SAMPLE ANOTHER DATA SAMPLE

    You can notice here that the column headers are separated by a line (made by dashes).
    It is possible to add separators to column headers in SQL Developer as is shown in MS-SQL?

    Thanks 🙂

    • that’s how it works by default

      query: select * from locations
      execute: via F5
      output:

      LOCATION_ID STREET_ADDRESS                           POSTAL_CODE  CITY                           STATE_PROVINCE            CO
      ----------- ---------------------------------------- ------------ ------------------------------ ------------------------- --
             1000 1297 Via Cola di Rie                     00989        Roma                                                     IT
             1100 93091 Calle della Testa                  10934        Venice                                                   IT
             1200 2017 Shinjuku-ku                         1689         Tokyo                          Tokyo Prefecture          JP
             1300 9450 Kamiya-cho                          6823         Hiroshima                                                JP
             1400 2014 Jabberwocky Rd                      26192        Southlake                      Texas                     US
             1500 2011 Interiors Blvd                      99236        South San Francisco            California                US
             1600 2007 Zagora St                           50090        South Brunswick                New Jersey                US
             1700 2004 Charade Rd                          98199        Seattle                        Washington                US
             1800 147 Spadina Ave                          M5V 2L7      Toronto                        Ontario                   CA
             1900 6092 Boxwood St                          YSW 9T2      Whitehorse                     Yukon                     CA
             2000 40-5-12 Laogianggen                      190518       Beijing                                                  CN
    • Jeff, I couldn’t reply your comment (about the default behaviour by adding line separators to columm headers), but, in my version or SQL Developer, the column headers aren’t present.

      These are SQL Developer’s version I have installed:

      Version 4.1.3.20
      Intern Version MAIN-20.78

      Maybe there’s another preference to set or maybe an upgrade is needed?

      Thanks 🙂

  3. Hi Jeff,

    I ran the following in sqldeveloper:

    set sqlformat html;
    set termout off;
    set serveroutput on;
    @vl_collect_po_12.sql

    This produces spool po_12_5151.html file.
    When I open the file, the html formatted output has black headings.

    Can I set the heading color to something else?

    Thank you!

    Vanha

    PS. Pls let me know if you need a sample output to show this.

  4. Jean Remacle Reply

    Hello,

    Is it possible using the HTML format to pass on html tags within the select clause ?

    Thanks and regards,
    Jean

  5. Alex Vinall Reply

    Hi Jeff,

    I’ve started working with SQLcl recently, and have been impressed with its export functionality, particularly compared to SQL*Plus. I note however that the DELIMITED SQL format does not apply the delimiter to CLOBs. Is this planned for a future release?

    I am attempting to output CSVs that can be read by Excel, and some of the columns are CLOBs containing multi-line text. Without the delimiter, Excel will interpret the line break as a new row in the file. With the delimiter, Excel would correctly interpret the multi-line CLOB as a single cell value.

  6. Paul Paulson Reply

    s normal db user:

    SQL>desc dba_users

    hangs:-(

    SQL>desc v$session

    SP2-0749: Cannot resolve circular path of synonym “v$session”

    SQLcl: Release 4.2.0

    • yesterday’s update works for me, both cases

      I am HR ON orcl > SHOW version
      Oracle SQLDeveloper Command-Line (SQLcl) version: 4.2.0.16.355.0402
       
      I am HR ON orcl > DESC dba_users
      Name                        NULL?    TYPE
      --------------------------- -------- ---------------------------
      USERNAME                    NOT NULL VARCHAR2(128)
      USER_ID                     NOT NULL NUMBER
      PASSWORD                             VARCHAR2(4000)
      ACCOUNT_STATUS              NOT NULL VARCHAR2(32)
      LOCK_DATE                            DATE
      EXPIRY_DATE                          DATE
      DEFAULT_TABLESPACE          NOT NULL VARCHAR2(30)
      TEMPORARY_TABLESPACE        NOT NULL VARCHAR2(30)
      CREATED                     NOT NULL DATE
      PROFILE                     NOT NULL VARCHAR2(128)
      INITIAL_RSRC_CONSUMER_GROUP          VARCHAR2(128)
      EXTERNAL_NAME                        VARCHAR2(4000)
      PASSWORD_VERSIONS                    VARCHAR2(12)
      EDITIONS_ENABLED                     VARCHAR2(1)
      AUTHENTICATION_TYPE                  VARCHAR2(8)
      PROXY_ONLY_CONNECT                   VARCHAR2(1)
      COMMON                               VARCHAR2(3)
      LAST_LOGIN                           TIMESTAMP(9) WITH TIME ZONE
      ORACLE_MAINTAINED                    VARCHAR2(1)
      I am HR ON orcl > DESC v$session
      Name                          NULL? TYPE
      ----------------------------- ----- --------------
      SADDR                               RAW(8 BYTE)
      SID                                 NUMBER
      SERIAL#                             NUMBER
      AUDSID                              NUMBER
      PADDR                               RAW(8 BYTE)
      USER#                               NUMBER
      USERNAME                            VARCHAR2(30)
      COMMAND                             NUMBER
      OWNERID                             NUMBER
      TADDR                               VARCHAR2(16)
      LOCKWAIT                            VARCHAR2(16)
      STATUS                              VARCHAR2(8)
      SERVER                              VARCHAR2(9)
      SCHEMA#                             NUMBER
      SCHEMANAME                          VARCHAR2(30)
      OSUSER                              VARCHAR2(30)
      PROCESS                             VARCHAR2(24)
      MACHINE                             VARCHAR2(64)
      PORT                                NUMBER
      TERMINAL                            VARCHAR2(30)
      PROGRAM                             VARCHAR2(48)
      TYPE                                VARCHAR2(10)
      SQL_ADDRESS                         RAW(8 BYTE)
      SQL_HASH_VALUE                      NUMBER
      SQL_ID                              VARCHAR2(13)
      SQL_CHILD_NUMBER                    NUMBER
      SQL_EXEC_START                      DATE
      SQL_EXEC_ID                         NUMBER
      PREV_SQL_ADDR                       RAW(8 BYTE)
      PREV_HASH_VALUE                     NUMBER
      PREV_SQL_ID                         VARCHAR2(13)
      PREV_CHILD_NUMBER                   NUMBER
      PREV_EXEC_START                     DATE
      PREV_EXEC_ID                        NUMBER
      PLSQL_ENTRY_OBJECT_ID               NUMBER
      PLSQL_ENTRY_SUBPROGRAM_ID           NUMBER
      PLSQL_OBJECT_ID                     NUMBER
      PLSQL_SUBPROGRAM_ID                 NUMBER
      MODULE                              VARCHAR2(64)
      MODULE_HASH                         NUMBER
      ACTION                              VARCHAR2(64)
      ACTION_HASH                         NUMBER
      CLIENT_INFO                         VARCHAR2(64)
      FIXED_TABLE_SEQUENCE                NUMBER
      ROW_WAIT_OBJ#                       NUMBER
      ROW_WAIT_FILE#                      NUMBER
      ROW_WAIT_BLOCK#                     NUMBER
      ROW_WAIT_ROW#                       NUMBER
      TOP_LEVEL_CALL#                     NUMBER
      LOGON_TIME                          DATE
      LAST_CALL_ET                        NUMBER
      PDML_ENABLED                        VARCHAR2(3)
      FAILOVER_TYPE                       VARCHAR2(13)
      FAILOVER_METHOD                     VARCHAR2(10)
      FAILED_OVER                         VARCHAR2(3)
      RESOURCE_CONSUMER_GROUP             VARCHAR2(32)
      PDML_STATUS                         VARCHAR2(8)
      PDDL_STATUS                         VARCHAR2(8)
      PQ_STATUS                           VARCHAR2(8)
      CURRENT_QUEUE_DURATION              NUMBER
      CLIENT_IDENTIFIER                   VARCHAR2(64)
      BLOCKING_SESSION_STATUS             VARCHAR2(11)
      BLOCKING_INSTANCE                   NUMBER
      BLOCKING_SESSION                    NUMBER
      FINAL_BLOCKING_SESSION_STATUS       VARCHAR2(11)
      FINAL_BLOCKING_INSTANCE             NUMBER
      FINAL_BLOCKING_SESSION              NUMBER
      SEQ#                                NUMBER
      EVENT#                              NUMBER
      EVENT                               VARCHAR2(64)
      P1TEXT                              VARCHAR2(64)
      P1                                  NUMBER
      P1RAW                               RAW(8 BYTE)
      P2TEXT                              VARCHAR2(64)
      P2                                  NUMBER
      P2RAW                               RAW(8 BYTE)
      P3TEXT                              VARCHAR2(64)
      P3                                  NUMBER
      P3RAW                               RAW(8 BYTE)
      WAIT_CLASS_ID                       NUMBER
      WAIT_CLASS#                         NUMBER
      WAIT_CLASS                          VARCHAR2(64)
      WAIT_TIME                           NUMBER
      SECONDS_IN_WAIT                     NUMBER
      STATE                               VARCHAR2(19)
      WAIT_TIME_MICRO                     NUMBER
      TIME_REMAINING_MICRO                NUMBER
      TIME_SINCE_LAST_WAIT_MICRO          NUMBER
      SERVICE_NAME                        VARCHAR2(64)
      SQL_TRACE                           VARCHAR2(8)
      SQL_TRACE_WAITS                     VARCHAR2(5)
      SQL_TRACE_BINDS                     VARCHAR2(5)
      SQL_TRACE_PLAN_STATS                VARCHAR2(10)
      SESSION_EDITION_ID                  NUMBER
      CREATOR_ADDR                        RAW(8 BYTE)
      CREATOR_SERIAL#                     NUMBER
      ECID                                VARCHAR2(64)
      SQL_TRANSLATION_PROFILE_ID          NUMBER
      PGA_TUNABLE_MEM                     NUMBER
      CON_ID                              NUMBER
      EXTERNAL_NAME                       VARCHAR2(1024)
      I am HR ON orcl >
    • Paul Paulson

      Have you checked ‘select any dictionary’ privilege for user ‘hr’ ?

  7. Hi ,

    how do i view the error output that shows you which line has the error on
    sql developer?

    regards,
    Buntu

  8. andre moraes Reply

    I’m trying to avoid another app to create a script. so I’m using sqlcl to create a script and then using again to run the script

    problem is that if I use set SQLFORMAT TEXT or any other combination… it add ” or something worse

    just doing set feedback off
    almost get where I need but it does add 2 extra columns for ever 12 or so lines of output as text below… any ideas to just get a clean file?

    CMD
    ——————————————————-

    I need the file to be like:

    SET SQLFORMAT loader
    SPOOL C:\out\oracle_backup\tracker_views_2016_09_30.sql
    DDL VW_TRACKER_PSR;
    DDL VW_TRACKER_PROJECTS_SUMMARY_02;
    DDL VW_TRACKER_PROJECTS_SUMMARY_01;
    DDL VW_TRACKER_PROJECTS_SUMMARY;
    DDL VW_TRACKER_PM_TL_TASKS;
    DDL VW_TRACKER_PM_TL_PROJECTS;
    DDL VW_TRACKER_PM_TL_MONTHS;
    DDL VW_TRACKER_PM_TASK_RESOURCES;
    DDL VW_TRACKER_PM_TASKS;
    DDL VW_TRACKER_HSE_TOT_HOURS_SPEC;
    DDL VW_TRACKER_HSE_TOT_HOURS_SEED;
    DDL VW_TRACKER_HSE_TOT_HOURS;
    DDL VW_TRACKER_GBS_REV_REPORT_SEED;
    DDL VW_TRACKER_GBS_PRJ_BY_PHASE;
    DDL VW_TRACKER_GBS_PERIOD_PER_COA_;
    DDL VW_TRACKER_GBS_DELTA;
    DDL VW_TRACKER_EPVL_S;
    SPOOL OFF
    QUIT;

    • problem is that if I use set SQLFORMAT TEXT or any other combination… it add ” or something worse

      I can’t tell what your problem is – instead of just showing me your script, show me your script output, specifically the ‘bad’ part.

    • andre moraes

      when I use SQLFORMAT TEXT it adds ”

      sample_______________________________________________________________

      “CMD”
      “SET SQLFORMAT loader”
      “SPOOL C:\out\oracle_backup\tracker_views_2016_09_30.sql”
      “DDL VW_TRACKER_PSR;”
      “DDL VW_TRACKER_PROJECTS_SUMMARY_02;”
      “DDL VW_TRACKER_PROJECTS_SUMMARY_01;”
      “SPOOL OFF”
      “QUIT;”

    • if you’re just running DDL commands – SQLFORMAT won’t do anything anyway…glad you figured it out though

    • andre moraes

      thanks Jeff… doing SET PAGESIZE 50000
      did the trick – without using any SET SQLFORMAT

      very much appreciated

  9. Dave Keiper Reply

    Hi Jeff,

    I’ve been reading about – and playing with – the various sqlformat settings, such as ansiconsole. I am looking for a way to simply avoid truncating column headings in the Script Output because I do a lot of pasting and manipulating of data in text files. The ansiconsole does this, but it also adds unwanted formatting to the data. Is there an easy way to show the full column heading and leave the data in a default format?

    • Dave Keiper

      Thanks, Jeff. This is good to know for additional data formatting, but I am not seeing how this addresses my question. I am looking for full column headings in the Script Output.

      Thanks – I appreciate your responses.

    • Sorry, I just saw unwanted formatting of data..how exactly are the column headers being truncated? Can you share an example?

    • Dave Keiper

      Example – I run the following query:

      select * from T_RE_AID_ELIG_RSN
      where SAK_AID_ELIG = 674008026

      Which displays these results in the Script with sqlformat set to default:

      SAK_AID_ELIG CDE DTE_ADDED
      ————- — ———
      674008026 052 20121218
      674008026 404 20160706

      And these results with sqlformat set to ansiconsole:

      SAK_AID_ELIG CDE_AID_ELIG_REASON DTE_ADDED
      674,008,026 052 20,121,218
      674,008,026 404 20,160,706

      I would like to be able to display the full column names as the ansiconsole setting does, but with the unformatted data as the default setting does.

      Thanks,
      Dave


    • SQL> set sqlformat ansiconsole
      SQL> /
      SAK_AID_ELIG CDE_AID_ELIG_REASON DTE_ADDED
      674008026 052 21121218
      674008026 404 21160706

      SQL> col cde_aid_elig_reason format a20
      SQL> set sqlformat
      SQL Format Cleared
      SQL> /

      SAK_AID_ELIG CDE_AID_ELIG_REASON DTE_ADDED
      ------------ -------------------- ----------
      674008026 052 21121218
      674008026 404 21160706

      SQL>

      We changed the default number display for column headers in ansiconsole. You can refer to the link I shared previously if you want to at some point format them differently.

      In the above code sample, it would behave the exact same way in SQL*Plus.

  10. Rudolf Noe Reply

    Hi Jeff,
    nice feature but could it be that there is one bug.
    When I export with sqlformat insert the generated output is not executable as the table names are lower case enclosed with double quotes.
    Best regards
    Rudolf

    • What version are you running?

      Version 4.1.3 –

      SELECT /*insert*/ * FROM hr.employees
       
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (193,'Britney','Everett','BEVERETT','650.501.2876',to_date('03-MAR-97 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',4485,NULL,123,50,'{"comments": []}',NULL, EMPTY_CLOB());
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (194,'Samuel','McCain','SMCCAIN','650.501.3876',to_date('01-JUL-98 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',3680,NULL,123,50,'{"comments": []}',NULL, EMPTY_CLOB());
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (195,'Vance','Jones','VJONES','650.501.4876',to_date('17-MAR-99 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',3220,NULL,123,50,'{"comments": []}',NULL, EMPTY_CLOB());
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (196,'Alana','Walsh','AWALSH','650.507.9811',to_date('24-APR-98 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',3565,NULL,124,50,'{"comments": []}',NULL, EMPTY_CLOB());
      INSERT INTO hr.employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,REVIEW,EMP_DEVICE,CLOB_STUFF) VALUES (197,'Kevin','Feeney','KFEENEY','650.507.9822',to_date('23-MAY-98 12.00.00','DD-MON-RR HH.MI.SS'),'SH_CLERK',3450,NULL,124,50,'{"comments": []}',NULL, EMPTY_CLOB());
       
       106 ROWS selected
  11. Jocke Treugut Reply

    Wonderful tool!!!

    But I don’t get the colors to work as expected. After my expected output, I always get the “command”. I don’t want to see that. I only want to see “Hello World!” in colors.

    SQL> set sqlformat ansiconsole
    SQL> select ‘@|fg_green Hello|@ @|fg_red World!|@’ from dual;
    ‘@|FG_GREENHELLO|@@|FG_REDWORLD!|@’
    Hello World! @|fg_green Hello|@ @|fg_red World!|@

    SQL> select ‘@|fg_green Hello |@’, ‘@|fg_red World!|@’ from dual;
    ‘@|FG_GREENHELLO|@’ ‘@|FG_REDWORLD!|@’
    Hello @|fg_green Hello |@World! @|fg_red World!|@

    I downloaded this file today sqlcl-4.2.0.16.175.1027-no-jre.zip. Which I guess is the latest release.

    Please help /Jocke

    • OverlordBR

      Hi, Jeff!

      Same problem here.
      The ANSI expected output is ok but, after that the command appears either.

      I capture the “bug” to explain better (see on the above link):
      https://s17.postimg.io/g5q1wirr3/Cropper_Capture_4.png

      My sqlcl version is the latest ( sqlcl-4.2.0.16.175.1027 ).
      I’m running it inside ConEmu on an Window 7 machine.

      Sorry about my English!

    • Pertti Forsstrom

      I’m just getting started to use this very nice tool. Better late than never, right?

      Did you Jocke or Jeff ever get to solve this thing with colors not displaying in query result?
      In that case what was the trick?
      I’m on SQL Developer 18.1.1.0 and have the same problem Jocke had.
      select ‘@|fg_green Hello|@’ “@|fg_green World!|@” from dual;
      The column header is displayed in green but not the result/rows.

      Best regards
      /Pertti

    • SQL Developer’s script output panel isn’t a fully functional shell/cmd window, so I don’t think it’s possible to do exactly what you want. If you were using SQLcl on a Mac, I think this could work.

      Better late than never, right?
      Absolutely!

    • Scott Gamble

      Sorry there was not a reply button on the comment dated 6/27/2018.

      I am getting the same results as others with sqlcl 18.2. not SQL Developer like was mentioned in the previous comments.

      Kris’s examples http://krisrice.io/2014-12-11-what-is-sdsql/ show this works in sqlcl with no restrictions such as only for a MAC etc.

      Are these color tags supposed to work in the results of the query text? If they are any thought why they are not working.. something in my environment?

      sqlcl 18.2 using a bash shell on Red Hat linux the following colors the column header but does not color the results like the examples I have seen.

      select ‘@|fg_green Hello|@’ “@|fg_green World!|@” from dual;

  12. Manu Koshy Reply

    Is there an option using sqlcl to specify the encoding to UTF-8 when exporting data to a text file? I can specify the encoding within SQL Developer and it works fine, however, I could not find any option to do this in SQLcl. If you don’t currently support it, are there any plans to add this functionality. This tool would work nicely for a project that I am working on except for the encoding which would be a showstopper for us.

    Thanks in advance!

  13. Hi Jeff,

    I need my query output as data should come in single line without being wrapped up and 3000 characters need to be displayed .

    I am using
    Set linesize 3000;

    However, only 2000 charcters are displayed with wrapped out .

    Can you resolve it please.

    Appreciate your help!

    • set long 3000

      and spool to a file to make sure your console/shell/terminal isn’t truncating the display

    • Hi Jeff,

      output is spooled but then too I am facing the issue.
      I am running query via toad.

      Can running a query through toad cause issue of truncation?

    • Toad? We’re talking about Toad? No can help you there.

      But in our command-line, SQLcl…

       
      R@orcl?? >SET long 3000
      HR@orcl?? >spool 3000.txt
      HR@orcl?? >SELECT lots_of_text FROM three3cols;
       
      LOTS_OF_TEXT                                                                                               
      -----------------------------------------------------------------------------------------------------------
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzz... -- to 3000 characters
       
      HR@orcl?? >spool off;
      HR@orcl?? >exit
      Disconnected FROM Oracle DATABASE 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
      WITH the Partitioning, OLAP, Advanced Analytics AND REAL Application Testing options
       
      ┌─[10:09:54][wvu1999][MacBook-Air-Smith]:/Applications/sqlclOTN/bin$
      └─>wc -c 3000.txt
          3486 3000.txt
      ┌─[10:10:27][wvu1999][MacBook-Air-Smith]:/Applications/sqlclOTN/bin$
      └─>more 3000.txt
      @|red HR|@@@|green orcl|@@|blue <U+1F37B><U+1F37A> >|@SELECT lots_of_text FROM three3cols;
       
      LOTS_OF_TEXT                                                                                               
      -----------------------------------------------------------------------------------------------------------
      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
      zzzzzzzzzzzzzzzzzzzzzzzzz -- to the 2,999 z chars i put in the column

      So, it should be working – but you haven’t shown me exactly what you’re doing, so I’m guessing.

  14. Hi Jeff,
    I love the SQLFORMAT DELIMITED feature. I’m able to paste a TAB character one space after the DELIMITED keyword for a tab delimited file, but I don’t see a way to *not* include any start/end string qualifiers. Is there some trick to avoid quotes around the data? Otherwise, I will strip out the quotes after the output file is generated.

    set sqlformat delimited

    • How are you able to do this? it does not work in mine!. I wish there were a text option or allow for Tab reserved word.
      using 18.4.0.376

    • Hi Jeff,

      I’m trying to get the same file that I would get by selecting Export on SQL Developer, format: text. When I put in SQL DEVELOPER SET SQLFORMAT DELIMITED ” ” It does not work. Am I doing something wrong?

      This is the script:

      /*Set the format to a CSV with a tab separator */
      SET SQLFORMAT CSV
      /*SET SQLFORMAT DELIMITED ” “*/
      /*set colsep chr(9)*/

      /*Set the spool to Crse Attributes */
      spool C:\temp\PS_CRSE_ATTRIBUTES.txt

      /*Run the query */
      Select…..

      /*Turn off Spool */
      spool off

      I could not make a tab separate the values, at the end I use CSV format and change the , for Tab in Notepad++ but that does not work correctly since descriptions have , too.

      Thank you

  15. I like the addition of SET SQLFORMAT DELIMITED option. However, at this time the option doesn’t appear in the output of HELP SET SQLFORMAT.

    I only see these:
    SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}

    Thanks.

    • Marc Burgess

      Hi, is it possible to specify that {open string char} and {close string char} be nothing (i.e. no quotes surrounding strings)?

    • Check after 6 years – was it made possible to to specify that {open string char} and {close string char} be nothing (i.e. no quotes surrounding strings)? If not, is there any possibility to improve this? Oracle SQL Developer supports empty values here and it would be very nice to suport the same in sqlcl as well.

    • Why did you wait 6 years to check?

      SQL> set loadformat left off
      SQL> set loadformat right off
      SQL> unload table regions

      format csv

      column_names on
      delimiter ,
      enclosures off
      encoding UTF8
      row_terminator default

      ** UNLOAD Start ** at 2022.04.21-08.33.15
      Export Separate Files to C:\sqlcl\22.1\sqlcl\bin
      DATA TABLE REGIONS
      File Name: C:\sqlcl\22.1\sqlcl\bin\REGIONS_DATA_TABLE.csv
      Number of Rows Exported: 4
      ** UNLOAD End ** at 2022.04.21-08.33.15

      SQL> !type C:\sqlcl\22.1\sqlcl\bin\REGIONS_DATA_TABLE.csv
      REGION_ID,REGION_NAME
      1,Europe
      2,Americas
      3,Asia
      4,Middle East and Africa

      SQL>

    • Jeff, thanks for your response – having an option to remove the quotes when exporting the whole table (using LOADFORMAT) can be very useful indeed. However, the original question was for SQLFORMAT – i.e. getting the output from any SQL command (including much more complicated options than select * from table). Is there any possibility to remove the quotes when using SQLFORMAT?

    • I gave you the best answer I have. Unless you want to use the GUI where it’s easy to set the enclosures to null. Or write your own js routine to have a custom loadformat, i’ve show that before here on the blog if you search for it.

  16. Hi Jeff
    SQLcl looks really nice. the “set sqlformat” is very usefull. We just tried to use “set sqlformat insert” to export the table data on the client side to an text file. As there are BLOBs in the table, I was really impressed, that it just worked (with encoding ..) (it looks that it is the only format who supports BLOB).
    But it looks that there are some issues with it.
    Here a little test case:


    SQL> col for binary_lob a50
    SQL> create table blob_test (id number, text varchar2(10), binary_lob blob);
    SQL> insert into blob_test (id,text,binary_lob) values(1,’TEST 123′,utl_raw.cast_to_raw(‘TEST 123′));

    SQL> set sqlformat insert
    SQL> select * from blob_test;

    REM INSERTING into BLOB_TEST
    SET DEFINE OFF;
    Insert into BLOB_TEST (ID,TEXT,BINARY_LOB) values (1,’TEST 123′,’626567696E2030207575656E636F64652E6275660D0A2B35243533352220512C432C200D0A0A656E64′);

    SQL> set sqlformat
    SQL> SET DEFINE OFF
    SQL> Insert into TESTLI (ID,TEXT,BINARY_LOB) values (1,’TEST 123′,’626567696E2030207575656E636F64652E6275660D0A2B35243533352220512C432C200D0A0A656E64′);

    SQL> select * from blob_test;

    ID TEXT BINARY_LOB
    ———- ———- ——————————————————————————–
    1 TEST 123 5445535420313233
    1 TEST 123 626567696E2030207575656E636F64652E6275660D0A2B35243533352220512C432C200D0A0A656E

    select id, text, utl_raw.cast_to_varchar2(binary_lob) as binary_lob from blob_test;

    SQL> select id, text, utl_raw.cast_to_varchar2(binary_lob) as binary_lob from blob_test

    ID TEXT BINARY_LOB
    ———- ———- ————————————————–
    1 TEST 123 TEST 123
    1 TEST 123 begin 0 uuencode.buf
    +5$535″ Q,C,

    end

    SQL Session Trace shows:
    BEGIN :1 := utl_encode.uuencode(utl_raw.cast_to_raw(:2 ),’1′,’uuencode.buf’) ; END;

    —–

    With “bigger” BLOBs the “sqlformat insert” shows something like:
    TO_BLOB(HEXTORAW(‘626567696E2030207575656E636F64652E6275660D0A6C4F5B5C2021372D5220205D413B47314C3C4259233B56554D3B5659’))
    || TO_BLOB(HEXTORAW(‘21345531524F555A5F4F534C202550282020444420213731543E372145332020243D2635583D2730202444514A383739410D’))
    || TO_BLOB(HEXTORAW(‘0A6C2B5651413B463C4F345731523A3659472E574152202055413B47314C3C42592238372D4530352D34272B5C4D262B5C40’))
    || TO_BLOB(HEXTORAW(‘275B5C222020292C202031443B573D4E3D20202F3326254E3D2651520D0A6C2B5429413C5635213455305B332020253C4645’))
    || TO_BLOB(HEXTORAW(‘473A27315120275820205741503C5724203F4020203C5724203F4020203C272D512027582020272150202020202127302021’))
    || TO_BLOB(HEXTORAW(‘2731523D3634200D0A6C202020453D20202D354455493C573134383659443936553633372D512027582020272D5120275820’))
    || TO_BLOB(HEXTORAW(‘20272D5120275820202721533C30215E202021503C20202020215D54202048532C3258500D0A6C2C3258522C232450202020’))
    || TO_BLOB(HEXTORAW(‘2029373020255539453C47315238363D533836594C38363D45392625543D36553F334635553C5724203F4020203C272D5120’))
    || TO_BLOB(HEXTORAW(‘2758202027215020202020293730200D0A6C26244D4F3B4631493D26454F3B455D213B4731453A3651343836594439365536’))
    || TO_BLOB(HEXTORAW(‘33302020202235542021392B3B5659443A3731493B56593F3526254E3926354D3544553F33463555202020200D0A6C225730’))
    || TO_BLOB(HEXTORAW(‘20203359533C30215E202021503C2020202021595420202450202020202927302023244526375531283134593F3134513331’))
    || TO_BLOB(HEXTORAW(‘302020202059542020285D2F372020202020443D20202C0D0A303234393F3524412533455D2533252D250D0A0A656E64′))

    This does not work in SQL.

    SQL> insert into blob_test (id,text,binary_lob) values(1,’TEST 123’,to_blob(utl_raw.cast_to_raw(‘TEST 123’)) || to_blob(utl_raw.cast_to_raw(‘TEST 123′)));

    Error starting at line : 1 in command –
    insert into blob_test (id,text,binary_lob) values(1,’TEST 123’,to_blob(utl_raw.cast_to_raw(‘TEST 123’)) || to_blob(utl_raw.cast_to_raw(‘TEST 123′)))
    Error at Command Line : 1 Column : 64
    Error report –
    SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
    00932. 00000 – “inconsistent datatypes: expected %s got %s”
    *Cause:
    *Action:

    SQL> insert into blob_test (id,text,binary_lob) values(1,’TEST 123’,to_blob(utl_raw.cast_to_raw(‘TEST 123’)));
    1 row inserted.

    cheers

  17. Hi Jeff,

    just want to share an experience maybe it will help someone . I’m starting to use more and more the sqlcl client . more specifically I create a bash script to generate reference file in csv that I use to transfer to other liquibase projet. The bash script call a sql script file using both commands set sqlformat csv and spool.

    the 1rst observation and you mention that on one of your comment in the past is the the spool file for some reason will create the first line empty. This whatever set options you will try to specify ( feedback off, echo off , verify off etc,,,) believe me I tried almose all the combinations. Not big deal but good thing to know to avoid searching.

    2nd, like you mention Jeff with sqlcl command line tool you cannot specity the delimiter ( comma ) or the enclosed character ( double quote ). When you export any query with string / int fields you ll never have any problem. Sqlcl will know when to enclosed in case there is a comma inside a column value. The issue that I faced was when you query numeric and timestamp columns that both could have the decimal character.

    basically those values won t be enclosed automatically and therefore will create inconsistency in terms of number of columns inside the csv. The temporary solution that I have found to be the cleanest was to change some session values.
    ALTER SESSION SET nls_timestamp_format=’YYYY-MM-DD’;
    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ‘. ‘;

    my recommandation for complete flexibility :
    choice 1 let us choose the delimiter character as in sqldeveloper and enclosed character
    choice 2 always encode the columns whatever their type or their content.

    Here you go hope that helps.

    Ricardo

  18. Hi,

    A sqlformat “pivot” would be nice (similar to Tom Kyte’s “print_table” function) for displaying rows with many columns.

    Cheers, Markus

    • Thanks. I’m always getting “error in line 1, unknown command” when I try “script printtab select * from dual;”. Any idea?

      Cheers, Markus

    • make sure you’re on the latest version, we JUST added it

      go to OTN and download it, it’s about 12MB

  19. Hello,
    In cvs format or delimiter format, any way to change the delimiter ?

    Thank you

    • not in SQLcl or not using the /*format*/ comments or by using SET SQLFORMAT

      However, in SQL Developer, using the Export dialog on the grid, you can definitely change the delimiters

  20. Hi Jeff,
    It is possible to set Delimiter, Header use/not, Encoding to SQLDEVELOPER CLI to make batch export file as
    select /*delimited*/ * from table;

    Thanks.
    Peter

    • you can’t set the delimiter, and we’re ignore SET HEAD OFF when using the formatters – gonna log a bug on that

  21. Hi Jeff,

    Who can I to know every possibles for set sqlformat?

    Thanks!
    Rodrigo

    • they match up with the formatters when exporting data in SQL Developer, so

      • csv
      • delimited
      • fixed
      • html
      • insert
      • json
      • loader
      • text
      • xml
      • ..and finally ‘ansiconsole’
    • ansiconsole works ok, but it is not good for cyrillic symbols.
      is there any parameter which works for cyrillic?

    • Hi,
      I want integer also in double quotes.
      basically the requirement is to double quote all the columns (string, int, double, etc..) with comma separated using sql developer to export in CSV.

    • Why would you quote a number?

      But nevermind, you’ll need to write custom formatter or just add the quotes yourself via the query.

  22. Keith Williams Reply

    Hi Jeff:

    Nice work. Kudos to you and the whole SQL Developer team. I noticed that when outputting JSON all of the strings have a leading space. Is this by design, or is this a small bug?

    Keith

  23. Thank you Jeff! I will give this a go. You always give me fun stuff to explore. Cheers!

  24. I was a bad boy – I don’t think the json formatter is in the 4.1 Early Adopter release, but it will be in EA2 and the 4.1 final version…

    Run this to ‘clear’ the SQLFORMAT
    SET SQLFORMAT

    And you’ll see your output return to ‘normal’ with this message as well.
    ‘SQL Format Cleared’

  25. Hi, nice feature. Is “set sqlformat json” available in 4.1 EA? I am getting “Bad Format specified” if I try to use it.

Write A Comment