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();
            String userName = rset.getString(1);


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.

Wednesday, September 28, 2016

ORDS 3.0.7 more secure by default

Defaulting  PL/SQL Gateway Security

Oracle REST Data Services 3.0.7 went out yesterday.  There's an important change that went in to better secure installations by default.  It has always been the case that we recommend customers set the validations for the plsql gateway.  There has always been a validation configuration option to lock down what procedures are accessible which was outlined in this blog post

The change is that starting in this patch when the plsql gateway is enabled AND the db username is APEX_PUBLIC_USER, ORDS setup will add in the configurations setting  security.requestValidationFunction  to be set to the apex security procedure  WWV_FLOW_EPG_INCLUDE_MODULES.AUTHORIZE

Customized PL/SQL Gateway Security

This is just a defaulted settings so if you have a custom procedure just edit the file as normal. Alsoin the blog post that describes how this works there are new features over this basic procedure approach such as binding values like flow_id ( app id ), flow_setup_id ( page id ).  The uses of this are you could have one install of ORDS that is external facing and expose only one set of applications based on the APEX application ID. Then an internal ORDS setup that doesn't limit at all to have intranet applications all hosted out of one production database.

There's also the ability to use javascript for securing the PL/SQL gateway which has been around since version 2.0.  The main benefit with this approach is there doesn't have to be a trip to the database to exec a plsql procedure.  That means ORDS can stop a request before the database is ever called in any capacity.  The variables available for this are outlined in this blog post:

Here's the note in the README

Important Changes to Note

APEX_PUBLIC_USER & wwv_flow_epg_include_modules.authorize

In line with security best practice and as recommended by the Oracle Application Express Documentation when a database pool is configured to use the APEX_PUBLIC_USER, Oracle REST Data Services will automatically set the value of thesecurity.requestValidationFunction setting to be:
This activates the white list of callable procedures which ships with Oracle Application Express and prohibits calls to other procedures. Please consult the Oracle Application Express Documentation for more information about this procedure and how to customize it's behaviour.

Wednesday, June 22, 2016

Kill DB sessions the easy way with SQLcl

Seems I can not tweet these animated GIFs anymore. So this is another very short blog post to show real case for adding a new command into SQLcl which can be downloaded here.

It's hard annoying tedious to find SID and SESSION then alter to kill that when needed.  What if there was a simple kill command.

Here a sample one that takes in the 2 options:
  kill sid serial#
  Example :   kill 1 2
  Example: kill KLRICE

Thursday, June 16, 2016

SQLcl and Query Change Notification

The database has had Query Change Notification for a while but to use it required a custom program. Such as Tim outlines on his blog

Since SQLcl has Nashorn, now it can be integrated with a few lines of javascript to get notified when changes happen.  The script is below and posted.  The catch is QCN only works on Varchars and Numbers.

Monday, March 14, 2016


Seems I've never posted about PL/SQL based REST endpoints other than using the OWA toolkit.  Doing the htp.p manually can give the control over every aspect of the results however there is an easier way.

With PL/SQL based source types, the ins and outs can be used directly without any additional programming.  Here's a simple example of an anonymous block doing about as little as possible but should get the point across of what's possible.

The interesting part is on the Parameters tab.  There is where to define the IN , OUT, or IN/OUT.  These are a INs can be from the URI or from the HTTP Header.  This means if you want USER_AGENT to be useful in the the plsql block just define it and assign it to a bind variable.  Here is the :ct and :myval being defined.  The :ct is bound to the HTTP HEADER Content-Type.  The :myval is bound to RESPONSE. More robust support for UDTs is in the works.

Hopefully, this is no surprise as to what the output looks like.

Disqus for Kris' Blog