Custom SQLFORMATs with SQLcl

thatjeffsmith SQL Developer 6 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() { 
		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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 6

  1. 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
      Author
      1. 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 ?

        1. thatjeffsmith Post
          Author
          1. 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)

Leave a Reply

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