Monday, October 10, 2016

Export APEX application with SQLcl


APEXExport has been around a long time for exporting an application and anything else like images, feedback,websheets,.. into a file system so that they can be version controlled.  This is a must if there is ever a need to rollback or see what the application was X days ago.  This is a java program that is part of the apex distribution.  The catch for some folks is that it's a java program and being mostly DB / APEX / PLSQL people java isn't something that's normally done which makes it a tad cumbersome.


Now, enter my long flight today with nothing much to do.


SQLcl and Javascript


There's not much that can't be done with SQLcl and Javascript.  Here's the latest example that is probably the most useful example for anyone using APEX.

The benefit is that this is just a command in SQLcl just like the new INFO command or the DESC or anything else.  Just another command for developers to use.  I took the existing APEXExport which was all java around 800+ lines of code and rewrote into javascript which cut the size in half!  The reason for this is that as a java program the APEXExport had to create a connection, take db username|password|tns.  Then it also had to deal with the low lever database calls for looping resultsets and closing them after.

Just as an example, here's the normal JDBC vs SQLcl way to get a single column from a single row into a variable to use.

PreparedStatement stmt = conn.prepareStatement("select user from dual");
            ResultSet rset = stmt.executeQuery();
            rset.next();
            String userName = rset.getString(1);
            rset.close();
            stmt.close();

VS

var user = util.executeReturnOneCol('select user from dual');









To load this just grab the script from github and load it into SQLcl. Once this is done SQLcl will now have the "apxexp" command.  This is a first pass as the port of the code.  If anyone finds an issue, log a ticket or just ping me on twitter @krisrice

SQL>script apxexp


Next I'll port the APEXExportSplitter over.