Some readers might know about the SQL Developer feature to publish a SQL Query as an APEX application. When a SQL query is being issued in SQL Developer one just has to right-click on the data grid and choose Publish to APEX in the context menu. SQL Developer then generates an APEX application containing an interactive report for that particular query and can be easily published to end users. The SQL Developer dialog looks as follows ...

The question is, what SQL Developer does in the background. I've turned on SQL tracing in order to get the calls which are issued by SQL Developer. The trace file then contained the following PL/SQL block:

And this call is executable by everyone (PUBLIC). So you don't have to use SQL Developer to do this;
it's also possible from SQL*Plus or any other database connection. The only requirements are an active APEX installation in the database
and the current database user must be connected to an APEX workspace. Of course: you have to replace the bind variables (colon-syntax) with
own (PL/SQL or Java) variables. So it's very easy to publish the results of a SQL query to the web; most
of the parameters are self-explaining, others are easy to determine - as follows:

p_workspace_id: The apex workspace ID can be found in the dictionary view APEX_WORKSPACES

The application ID is being generated and returned in the OUT parameter p_application_id. This call
can be used from within any database session; so APEX can also be used as an easy report generator for an application. The application just
has to execute this call and can afterwards redirect the browser to the APEX applications' page. The report rendering and handling is
then done completely by APEX.

Today I'd like to play a little bit with using external native code from PL/SQL programs. I've found a quite useful example: When a database (PL/SQL) application works with RAW or BLOB content it not necessarily has a file name or a mimetype information in order to determine which kind of content this particular BLOB is. On UNIX/Linux systems there is the file utility which can determine the kind of binary content just by examining the bytes. And this functionality is also contained in the library libmagic.so (which the file utility actually uses I'd think). Therefore I'd like to provide this functionality to PL/SQL.

This is basically the file utility - reinvented. When using the magic library we need to create a "context" first (magic_open). Then we load the "database" (NULL loads the standard file - see the Linux-manpage of "libmagic" for more information) with magic_load and after that we can determine the file content with magic_file. Finally the context has to be closed with magic_close.

I don't want to do all these calls from PL/SQL - I just want to have an equivalent to file utility. But unlike the file utility I want to determine the content type of a byte array (as RAW datatype). The magic library provides a function for that: magic_buffer.

Now we start. First we code a simple C program (oramagic.c) which encapsulated the libmagic functionality in two C functions: mimetype_bytes (which is the lesson target) and mimetype_file (for testing purposes).

And if we have the contents in a BLOB variable we can determine the content type as follows. Note that we don't need to pass the whole BLOB to the library - the first few (here: 200) bytes are sufficient.

Basically this is pretty nice ... but there are also some serious disadvantages of linking PL/SQL to external native code. The most important one is that this is platform-dependant. This example was created on a linux system and this code runs unchanged only on a linux system. On other platforms we first have to lookup the magic library, then code a c program (maybe slightly different), run the compiler (slightly different parameters) and link to PL/SQL (unchanged). The second disadvantage is that the code is outside the database which has to be taken in to account for the backup & recovery process.

For those reasons I'd prefer Java in the database as the method for embedding external code. For the actual problem of having the "magic" functionality in the database this article describes another very interesting approach.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.