Custom SQLFORMATs with SQLcl

thatjeffsmith SQL Developer 8 Comments

Tell Others About This Story:

When I make jokes about copying and pasting code from StackOverflow or Github, I’m not really joking.

Funny, because it's true.

Funny, because it’s true.

But in this case, it’s OK. Because, I’m copying and pasting code from OUR GitHub project, AND my boss wrote the code.

A question came in, and it’s come in a few times, but here’s the most recent variant:

So basically, you want to use one of our formatters, but you don’t like it 100%, so you want to change it a bit. And I said, just write your own then.

To the GitHubs!

So I’m going to change up the code to write the records delimited by a ‘;’ and no quotes on the strings.

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(cmd.rownum+ "\n");
	cmd.printColumn = function(val,view,model) {
		 var v  = NLSUtils.getValue(conn,val);
		 ctx.write(v + ";");
		} catch(e){
	cmd.endRow = function () {
	cmd.end    = function () {
    cmd.type = function() {
    	return "weird";
    cmd.ext = function() {
    	return ".weird";
// Actual Extend of the Java CommandListener
var weirdFormat = 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 weirdFormat());

Yes, I hard-coded the delimiter – ‘;’

Note that I named this format ‘weird’ – totally not judging folks in Europe for calling it CSV but not using a comma 🙂

So let’s see it work.

Any bugs here are mine - I'm not a great copy-paster to begin with and my JS skills are hilariously inept.

Any bugs here are mine – I’m not a great copy-paster to begin with and my JS skills are hilariously inept.

Related Posts

Tell Others About This Story:

Comments 8


    Thank you for the weird2.js
    This is almost 100% what I am looking for, except one minor issue.
    With this custom sqlformat, you would notifice that the semi-colon also appears after the last column.
    I tried changing
    ctx.write(v + “;”);
    ctx.write(“;” + v );
    but of course it would put the semi-colon before the front of the first column.
    How could I avoid that (either the delimiter at the front or at the end of a record)?


  2. Was using sqlcl to generate insert statements from an existing table and came across an issue when one of the selected columns is a date. My nls_date_format is set to ‘YYYY/MM/DD HH24:MI:SS’ but sqlcl just assumed it was set to ‘DD-MON-RR HH.MI.SSXFF AM’ so I got all the insert statements like this:
    to_timestamp(‘2012/01/11 00:00:00′,’DD-MON-RR HH.MI.SSXFF AM’)

    As you can see, the selected date format doesn’t match the to_timestamp format and I had to replace it using a text editor. Not a big deal but wouldn’t it be nice if sqlcl actually used the format specified in the nls_date_format?

  3. Thanks, this was really helpful! However, how would you also include the column headers in the format? Unfortunately, it doesn’t seem Oracle has any Java docs available for the CopyFormatter class, which I had initially looked for to solve this myself.

    1. thatjeffsmith Post
    2. Thanks for your work.

      But if I use your second script I can’t change nls_date_format in the result.

      Example : alter session set nls_date_format = ‘YYYYMMDD HH24:MI:SS’;

      with your sql format I can change date format but not the header.

      Do you have a solution for recover the header in your sqlformat ?

    3. thatjeffsmith Post
    4. an exemple is better that lots sentences
      — default date format
      SQL> select sysdate from dual;
      –Change date format before use your second script
      SQL> alter session set nls_date_format = ‘YYYYMMDD HH24:MI:SS’;
      Session modifi├®(e).
      –the change is applied in my session
      SQL> select sysdate from dual;
      20170221 17:08:41
      — I have change your script for add schemas in the request (the last argument)
      SQL> script C:\Dev\exportBis.js select sysdate from dual test;
      select sysdate from dual

      >Schemas: test
      >ROW: 0
      — result don’t have the godd date format
      >Schemas: test
      >ROW: 1
      SYSDATE||2017-02-21 17:08:58.0;

      I don’t want to change something in the header. but I want to change the date format in the result ( and I want the header in result for a custom data format)

Leave a Reply

Your email address will not be published. Required fields are marked *