Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Friday, September 16, 2005

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.
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
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
procedure p_close_file -- Utility to close our file
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;
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
'p_take_snapshot: '
|| sqlerrm
, 1
, 255
p_close_file; -- close the file
raise; -- propagate the exception

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.


Guillaume Lacasse said...

Great job !
Exactly what I needed !

Is there a way to remove the "X-ORACLE-IGNORE" stuuf at the top of the file ?

Roland Bouman said...

Hi Guillaume!

glad it's useful to you. I actually kind of forgot I wrote this entry.

Anyway, let me get this clear. You are using something like this but you are seeing X-ORACLE-IGNORE being written to your file? How odd, I don't see that.

It looks like a custom header to me, maybe they incorporated that in a later version of the gateway.

My guess is you're going to have to parse that out yourself. I don't think it is too hard - look for a completely blank CR/LF sequence - anything beyond that is your response body which you want to dump to file.

Let me know if that was helpful ;)


Guillaume Lacasse said...

Hi !

I added a small portion of code at the top of the function to remove the header lines.

Here's what I did:

--clear headers
if (INSTR(v_line, 'ORACLE-IGNORE') > 0) then
v_header := true; --process header lines
end if;
if (v_header) then
v_header_lines := v_header_lines - 1;
if (v_header_lines <= 0) then
v_header := false;
end if;
end if;
--end headers
if (v_header = false) then go on with line treatment

It's a bit ugly but it works for now !

Thanks !