Friday, 12 December 2008

'Publish to APEX' from SQL Developer 1.5.3

Since SQL Developer 1.2.1 and APEX 3.0.1, we've had some useful integration between APEX and SQL Developer, the ability to import and deploy applications, browse the APEX metadata, remote debug PL/SQL in your applications and more. With the latest release of SQL Developer 1.5.3, it is now possible to create a quick and simple APEX application from within SQL Developer (thanks to a bug fix). This is possible through the 'Publish to APEX' feature and creates a simple 1 page APEX application containing an Interactive Report Region based upon a SQL statement.

(Note: Requires APEX 3.1 or above.)

This feature allows you to take any grid of data, right click on it and select to 'Publish to APEX'. (Note a 'grid of data' includes results from executing a SQL statement, results of pre-defined or user-defined reports from the 'Reports' tab, specific table / view properties such as columns, data, constraints etc. and I'm sure there are more.) Upon selecting 'Publish to APEX', the following dialog is displayed:

This dialog allows you to specify 5 properties:
1) Workspaces- The workspace where you want the application to be created (this list will only display workspaces that are associated with the schema of the context of your current grid of data).
2) Application Name - The name of the application that will be created.
3) Theme - The theme for your new application, specifying look and feel.
4) Page Name - The name of the page that will be created.
5) SQL - The SQL that will be used to generate an interactive report region within the page. This defaults to the SQL used to build the original grid of data, but can be changed.

Upon clicking 'Apply' SQL Developer will create the application and show a dialog with some basic creation information such as application name and ID. This creates a 1 page application in the workspace defined, containing an interactive report region with a source of the SQL specified in the dialog.

A few more points to note about this application:
- The application's authentication scheme defaults to 'Database Account Credentials' meaning that you'll need to authenticate into the application using valid database account username and password. This can obviously be changed to something else if required.
- The application will be created in an application group called 'Published from SQL Developer'.
- The interactive report region only displays the first ten columns of the report by default, but again this can easily be changed via the interactive reports menu bar 'Actions' green cog drop down, then select 'Select Columns'.

Here is an example of the application that is generated. I selected to publish data that showed access attempts to my workspace (selecting from the APEX metadata view apex_workspace_access_log). I then used interactive report features to refine my data to show all the failed login attempts for applications within a workspace, grouped by application:

Have fun publishing to APEX!!!


Stew said...

How cool is THAT?

Unfortunately, this will be difficult to use this feature at our site, as the DBAs required us to set up a separate schema (for security purposes) and won't grant our regular schemas access to the Apex workspace schema.

Simon Gadd said...

What a great function and another reason to opt for SQL Developer for tight APEX integration.

Stew said...

I found a way around my privilege issue so I can use this nifty new feature and the other stuff.

While playing I noticed a bug in the Deploy Application feature when you want to update an existing app id (e.g. moving alpha to beta application version). The Overwrite checkbox is disabled until you click Next, get the error that you need to overwrite the existing id, then the checkbox is enabled.

Anthony Rayner said...


Glad you found away around it!! Regarding the bug, thanks for pointing that out, I'll forward the details onto the SQL Developer team.

Stew said...

Thanks for forwarding that Anthony. I know how to connect with the Apex team but not with Developer. But the more I use it, the more impressed I am. I've been a fan of PL/SQL Developer for the past 3+ years but SQL Developer is very strong, especially given the price.