4.7.3 Output Formats Supported by SET SQLFORMAT Command

By using the SET SQLFORMAT command, you can generate the query output in a variety for formats.

Note:

These output formats are available for the Script interpreter. Therefore, you must include the prefix %script.

The available output formats are:

• CSV — The CSV format produces standard comma-separated variable output, with string values enclosed in double quotes. The syntax is:

  %script

  SET SQLFORMAT CSV

• HTML — The HTML format produces the HTML for a responsive table. The content of the table changes dynamically to match the search string entered in the text field. The syntax is:

  %script

  SET SQLFORMAT HTML

• XML — The XML format produces a tag based XML document. All data is presented as CDATA tags. The syntax is:

  %script

  SET SQLFORMAT XML

• JSON — The JSON format produces a JSON document containing the definitions of the columns along with the data that it contains. The syntax is:

  %script

  SET SQLFORMAT JSON

• ANSICONSOLE — The ANSICONSOLE format resizes the columns to the width of the data to save space. It also underlines the columns, instead of separate line of output. The syntax is:

  %script

  SET SQLFORMAT ANSICONSOLE

• INSERT — The INSERT format produces the INSERT statements that could be used to recreate the rows in a table. The syntax is:

  %script

  SET SQLFORMAT INSERT

• LOADER — The LOADER format produces pipe delimited output with string values enclosed in double quotes. The column names are not included in the output. The syntax is:

  %script

  SET SQLFORMAT LOADER

• FIXED — The FIXED format produces fixed width columns with all data enclosed in double-quotes. The syntax is:

  %script

  SET SQLFORMAT FIXED

• DEFAULT — The DEFAULT option clears all previous SQLFORMAT settings, and returns to the default output. The syntax is:

  %script

  SET SQLFORMAT DEFAULT

  Note:

  You can also run this command without the format name DEFAULT by simply typing SET SQLFORMAT.

• DELIMITED — The DELIMITED format allows you to manually define the delimiter string, and the characters that are enclosed in the string values. The syntax is:

  %script

  SQLFORMAT DELIMITED delimiter left_enclosure right_enclosure

  For example,

  %script

  SET SQLFORMAT DELIMITED ~del~ " "

  SELECT * FROM emp WHERE deptno = 20;

  Output:

  "EMPNO"~del~"ENAME"~del~"JOB"~del~"MGR"~del~"HIREDATE"~del~"SAL"~del~"COMM"~del~"DEPTNO"

  In this example, the delimiter string is ~del~ and string values such as EMPNO, ENAME, JOB and so on, are enclosed in double quotes.