Redirecting Oracle Mod PLSQL Gateway Pages directly to files

I have some webapps that are based upon the Oracle Mod PLSQL gateway. Nowadays, a lot of developers prefer to build Oracle Webapps using some java component to generate the pages, but for a lot of purposes, I still think the Mod PLSQL gateway is not a bad solution at all. I'm the first to agree that mod plsql is not very suitable for generating complex html pages. But when you have to generate XML data that's readily derivable from your relational data stored in the database, i think it's superior to whatever solution running outside the database.

Anyway, once you do have to build or maintain a webapp based on the mod plsql gateway, you are going to want to view the pages you're generating without having to GET them via a HTTP request. It could be for debugging purposes or in case you'd want to make a part of your application accessible offline.

For example, an application I'm building for a client generates a lot of pages that are rendered as diagrams in the client browser. In this setup, I've written an oracle package that grabs all the data I need to draw diagrams of a particular type. So, I have one package to get me the data for business process model diagrams, one for diagrams describing information, one for organization charts, and so on.

The diagrams are usually interrelated, and you can click through these diagrams, zooming on and out. Together, these diagrams provide an integral description of an organization: its hierarchical structure, its business processes, and the associated data flows. The diagrams are used mainly for quality management, process reengineering, re-organization, and auditing purposes.

Anyway, these packages output the data as xml to the mod plsql gateway. I'm using nothing fancy, just htp.p. I do have one package that outputs xml elements that I need across diagram types, and that has a little cache builtin but that's about it.

Normally, users would GET xml documents using their internet browser. In my setup, the generated xml contains a instruction that can often be manipulated with a parameter in the uri. I use this to have the client browser initiate a XSLT transformation of the xml data. Usually, the transformation target is some form of DHTML, that is, HTML4.0/CSS/javascript.

So, in my apps, the actual rendering is left to the client, another thing wich will be frowned upon by most developers. Actually, I don't think its so bad, because now the network between the client and the webserver is loaded about 10 times less that it would have been had the XSLT transformation been done on the serverside.

Now, some groups of users do not have access to the intranet all of the time. They still need to access the diagrams though. Also, a presentation for an audience is usally a situation where you would want to use an offline version of the system rather than an online one. So, to handle these situations, I've build some utilities to generate an offline snapsot of a part of the data. This involves traversing the graph of interrelated diagrams, generating the xml and then, storing the xml data in a file that can be accessed offline.

The latter task is very general purpose, so I decided to post the code here:

    /********************************************
    *
    *   procedure p_file: p_take_snapshot
    *   purpose: store mod_plsql generated content in a file
    *   author: Roland Bouman (R underscore P underscore Bouman at hotmail dot com)
    *   parameters:
    *
    *       p_dir: the directory to create the file in
    *
    *           This must be a valid directory according
    *           to the builtin utl_file package.
    *           For Oracle 8 and below, the path must be 
    *           set in the utl_file_dir database parameter.
    *           For Oracle 9 this is the name of an existing
    *           DIRECTORy schema object.
    *
    *       p_file: the name of the file
    *
    *   example usage:
    *   
    *       my_web_app.p_generate_web_page('page1');
    *       p_take_snapshot(
    *           'offline_pages'
    *       ,   'page1.html'
    *       );
    *
    *    my_web_app.p_generate_web_page('page1') represents
    *    the generation of a webpage by the procedure 
    *    p_generate_web_page in the package my_web_app.
    *    Of course, this is totally hypothetical.
    *
    *   remarks: 
    *       The procedure stores whatever happens  
    *       to reside in the buffer maintained by 
    *       the builtin htp package in the file. 
    *       The file is created if it does not already 
    *       exist. If it does exist, it's contents are 
    *       overwritten. 
    *
    *********************************************/
    create or replace
    procedure p_take_snapshot(
        p_dir           varchar2              -- Path (<=v8) or name of DIRECTORY object(>=v9).        
    ,   p_file          varchar2              -- Filename of the target. 
    )
    is
        v_file          utl_file.file_type;   -- The handle to our file.        
        v_idx           integer;              -- current index of multi-line buffer (TABLE OF VARCHAR2(256s) in htp package        
        v_line          varchar2(256);        -- A local buffer we use to read a single line from the htp buffer        
        v_len           integer;              -- The actual length of the data in our local line buffer        
        v_buffsize      integer := 0;         -- total aount of data in the utl_file write buffer
        v_newlen        integer;              -- helper var to compute the new  size of the utl_file write buffer  
        v_max_buffsize  integer := 32767;   -- The maximum size of the utl_file write buffer
        procedure p_open_file                 -- Utility to open our file
        is
        begin
            v_file := utl_file.fopen(         -- get file handle
                p_dir                         -- for file in this dir 
            ,   p_file                        -- file has this name
            ,   'w'                           -- open for write 
            ,   v_max_buffsize                -- use this maximum size for the write buffer 
            );
        end;        
        procedure p_close_file                -- Utility to close our file
        is
        begin
            if utl_file.is_open(v_file) then  -- check if the file is open       
                utl_file.fclose(v_file);      -- close the open file       
            end if;
        end;
    begin        
        p_open_file;                          -- open file for write        
        loop                                  -- loop through lines in htp buffer
            v_line := htp.get_line(v_idx);    -- get a line from the htp buffer            
            v_len := length(v_line);          -- get amount of data in the line            
            v_newlen := v_buffsize + v_len;   -- get total amount of data in the utl_file buffer
            if v_newlen > v_max_buffsize then -- if total amount of data would overflow the utl_file buffer                
                utl_file.fflush(v_file);      -- flush the utl_file buffer                
                v_buffsize := v_len;          -- update cumulative buffersize            
            else                              -- we don't need to flush the utl_file buffer right now                
                v_buffsize := v_newlen;       -- update cumulative buffersize 
            end if;
            utl_file.put(v_file, v_line);     -- (buffered) write to the file 
            exit when v_idx = 0;              -- leave the loop when we did all lines 
        end loop;
        utl_file.fflush(v_file);              -- flush what's still in the utl_file buffer 
        p_close_file;                         -- close the file
    exception                                 -- handle runtime errors
        when others then                      -- catch all. TODO: handle exceptions declared in utl_file
        dbms_output.put_line(                 -- print som error info
            substr(
                'p_take_snapshot: '
            ||  sqlerrm
            ,   1
            ,   255
            )
        );
        p_close_file;                         -- close the file
        raise;                                -- propagate the exception
    end;

Interstingly, oracle does provide some functionality to do this already: htp.showpage outputs the content of the multi line buffer mainained by the htp package....but does it with dbms_output.put_line. Why they used dbms_output.put_line is a complete mystery to me. Why not simply use dbms_output.put ? Anyway, for my purpose, it just doesn't cut it. Because they used put_line, the output contains lots of newlines which mess up my XML data bigtime. My procedure really what they do in htp.showpage, I just used utl_file.put instead of dmbs_output.put_line.

Anyway, if you need this procedure, knock yourself out. You can have it, copy it, distribute it...Whatever. I'd like it very much if you send me your comments and/or improvements. Also, I'd like it if you'd credit me. Other ideas concerning mod plsql are welcome to.