Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Custom SQLFORMATs with SQLcl

2 Mins Read

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() { 
		cmd.rownum++;
		ctx.write(cmd.rownum+ "\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 () {
 
	} 
	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.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

15 Comments

  1. Mark Reply

    Hi Jeff
    Regarding that custom cards.js file on gihub – I got that working OK. But it only prints column values. Any idea how to make it print the column headings beside the column values?
    It’s not obvious to me from the examples or any documentation I can find.

    • Mark

      Thanks Jeff, I have that ‘script cardview’ method working. But it’s not as convenient as the format option would be, as it requires reformatting my sql to fit on one line.

  2. Felix Reply

    Hey Jeff. Im trying to use this in a shell script and it errors out with “Could not load file: script.js”. Any idea why this might be happening?

    • Felix

      Never mind. Turns out it didn’t like leading spaces

  3. David Reply

    Hey Jeff, if you read CSV as Character Seperated Values, then everyone is happy..!! 🙂

  4. Mason Reply

    THATJEFFSMITH,

    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 + “;”);
    to
    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)?

    TIA
    Mason

  5. Claudio Reply

    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?

  6. Jure Reply

    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.

    • Jure

      Thanks! Is there no way to do it as a sqlformat then?

    • Mathieu

      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 ?

    • Mathieu

      an exemple is better that lots sentences
      — default date format
      SQL> select sysdate from dual;
      SYSDATE
      ——–
      21/02/17
      –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;
      SYSDATE
      —————–
      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
      SYSDATE||SYSDATE;
      — 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)

Write A Comment