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:
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