APEX Application to View Log Files

Oracle APEX is keeping all data in database and makes it easy to create different reports for tables or views.  But what if we want to present something outside of database?   Like text log files – how this could be done in APEX?   For regular web server it is a trivial task – usually simple configuration of web server enables to list directory and download any files from it (and it probably would be easiest way to do it). But what if we need to integrate logs browsing into APEX application?  Actually there is a way to list and serve files even in APEX, if it is required.

DB Schema Setup

First we need to add some privileges to  our APEX schema (let it call APEX_SCHEMA);  Execute this commands as sysdba:

This createa a DB directory object – so we can access the files within this directory from PL/SQL.  For some reasons ( probably security) it is not possible to list files in directory object.  So we  need some Java code to do this.  Java is running in a sandbox so we need to add explicit permissions to access files in our directory.

Supporting Code

We need some code to help us with directory listing:

This  creates a temporary table, that  contains the listing of the directory,  procedure get_dir_list that  fills this table and function load_file_to_blob.

 APEX Application

Now we can create two new pages in our APEX application – first page will show report with all files in given directory. We create regular report page, where there is following PL/SQL process started ‘Before Header':

Source of the the report is following query:

Last column should be a link,   which redirects to our next page.  There should be also page item PX_FNAME, that stores selected file name. To open link in  a new browser window set ‘Link Attributes’ to ‘target=”_blank”‘. Also use link to set item PX_FNAME to #FILENAME#.

Second page serves the file content. We create blank page and put this code into PL/SQL code assigned to ‘Before Header’ stage:

The code for serving file content is bit different in APEX 4.2  –  I found some examples on Internet, which has been writing additional headers  (Content-Disposition, Content-Length) using htp.p procedure and then closing header with OWA.UTIL.http_header_close. However this is not working with latest APEX version – everything written to response after OWA_UTIL.mime_header call is written to response body. On the other hand the length of content is calculated automatically.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">