Forum Stats

  • 3,759,958 Users
  • 2,251,621 Discussions
  • 7,870,883 Comments

Discussions

SQLFormat Delimited no Enclosure

GrantPrellwitz
GrantPrellwitz Member Posts: 2 Blue Ribbon
edited Mar 4, 2019 11:58AM in SQLcl

I have a number of instances where I need to transmit pipe-delimited exports regularly to partners. Thus, I'm looking for a general solution that can be scripted, so I'm looking at SQLcl. In SQLDeveloper, I can tell it that I want the left- and right-enclosure characters to be "none", and there will be no enclosure characters. How do I replicate this behavior in SQLcl? I have seen similar questions (without an answer) about using the tab character. I have seen reference that SQLcl is just a different interface onto the SQLDeveloper code, so I should certainly hope that this is possible. I know that @thatJeffSmith-Oracle has done some blog posts about it, but don't see where he talks about setting the enclosure to empty.

Versions:

SQLcl: Release 18.4.0.0 Production

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL Developer IDE 18.1.0.095.1630

The screen in SQL Developer I'm trying to replicate in SQLcl is:

pastedImage_8.png

The built-in help doesn't even mention the delimited option:

SQL> help set sqlformat

SET SQLFORMAT

  SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}

My desired output would be:

SQL> select '1' one,'2' two from dual;

ONE|TWO

1|2

From searching, though, I find that SET SQLFORMAT DELIMITED [delimiter] [left_enclosure] [right_enclosure] is the proper format.

If I just leave off the *_enclosures, it gives the default double quotes.

SQL> SET SQLFORMAT DELIMITED |

SQL> select '1' one,'2' two from dual;

"ONE"|"TWO"

"1"|"2"

If I try the word "none" it just uses that as a string, showing that the enclosure is not limited to a single character and that the right enclosure will default to the double quote if not provided.

SQL> SET SQLFORMAT DELIMITED | none

SQL> select '1' one,'2' two from dual;

noneONE"|noneTWO"

noneone1"|none2"

I tried a variety of other possibilities for the enclosures, none of which were successful. I'm hoping someone can provide the correct parameter for the SET SQLFORMAT DELIMITED to get the equivalent of "none" available in SQL Developer for Enclosure.

I then considered a more complex enclosure that I could use sed or some other tool to remove afterwards. Not a preferred solution, but doable. Unexpectedly, the left enclosure is repeated twice at the beginning of the first data line, but not perfectly. Note that the second instance of the left enclosure is missing the first character.

SQL> SET SQLFORMAT DELIMITED | ~^~DELETEME~^~ ~^~DELETEME~^~

SQL> select '1' one,'2' two from dual;

~^~DELETEME~^~ONE~^~DELETEME~^~|~^~DELETEME~^~TWO~^~DELETEME~^~

~^~DELETEME~^~^~DELETEME~^~1~^~DELETEME~^~|~^~DELETEME~^~2~^~DELETEME~^~

SQL> SET SQLFORMAT DELIMITED | =J! =K!

SQL> select 'one' one,'two' two from dual;

=J!ONE=K!|=J!TWO=K!

=J!J!one=K!|=J!two=K!

Any help appreciated!

Grant Prellwitz

Harper College

GrantPrellwitz

Best Answer

Answers