I am trying to get sqlcl to spool the results of a query with a specific line end sequence. In SQLDeveloper, it is possible to tell the Export Tool to emit {EOL} instead of the platform specific newline sequence. I can't seem to find a reference which tells me how to do this with sqlcl inside of a script.
For example, my script reads:
set heading off
set pause off
set serveroutput off
set trims on
set tab off
set feedback off
set termout off
set wrap off
set pagesize 0
set recsep off
set newpage 0
set space 0
set echo off
set verify off
set markup HTML off spool off
set linesize 0
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD.HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD.HH24.MI.SS.FF6';
set sqlformat delimited , " "
set encoding UTF8
spool /tmp/mydata.csv
select * from MYTABLE;
SPOOL close
exit
What I want is each row from MYTABLE to end with the text {EOL} instead of a newline. This is because I am parsing the results and newlines may be embedded in some of the character columns. As I need to emit the the data using UTF8, those bytes are not escaped. By looking for {EOL} instead of relying on the newlines, I can more easily read and parse the data.
When I export the data using SQL Developer's Database Export Wizard, I am allowed to select the line termination sequence. Can I do the same with sqlcl? I was under the impression they were the same underlying code.
I'm not aware of an inbuilt option.
But you can code your own formats with a little JS!
Borrowing from Jeff's example which uses semi-colon delimiters at
https://www.thatjeffsmith.com/archive/2016/12/custom-sqlformats-with-sqlcl/ You could create the following JS:
var CopyFormatter = Java.type("oracle.dbtools.raptor.format.CopyFormatter")
var FormatRegistry = Java.type("oracle.dbtools.raptor.format.FormatRegistry")
var NLSUtils = Java.type("oracle.dbtools.raptor.utils.NLSUtils");
var cmd = {};
cmd.rownum = 0;
cmd.start = function() {
}
cmd.startRow = function() {
ctx.write("\n");
}
cmd.printColumn = function(val,view,model) {
try{
var v = NLSUtils.getValue(conn,val);
ctx.write(v + ";");
} catch(e){
ctx.write(e);
}
}
cmd.endRow = function () {
ctx.write("{EOL}");
}
cmd.end = function () {
}
cmd.type = function() {
return "EOL";
}
cmd.ext = function() {
return ".EOL";
}
// Actual Extend of the Java CommandListener
var EOLFormat = Java.extend(CopyFormatter, {
start: cmd.start,
startRow: cmd.startRow,
printColumn: cmd.printColumn,
endRow: cmd.endRow ,
end: cmd.end,
getType: cmd.type,
getExt: cmd.ext,
setTableName: function(){}
});
// Registering the new Command
FormatRegistry.registerFormater(new EOLFormat());Save it as eolformat.js. Then invoke it like so:
SQL> script eolformat.js
SQL> set sqlformat EOL
SQL> select 'try'||chr(10)||'this' c1, level c2 from dual connect by level <= 5;
try
this;1;{EOL}
try
this;2;{EOL}
try
this;3;{EOL}
try
this;4;{EOL}
try
this;5;{EOL}