I've been working in a simple reporting tool with PL/SQL Web Toolkit in Oracle 10g. Basically you just configure a DAD and the listener opens an HTTP port (8080 by default) and you just access your procedures and print html from PL/SQL. Pretty cool and no extra software or server configuration (no Apache or iAS or APEX needed).
For static content I needed to display image files, so I started looking a bit on Google and I end up with this configuration.
First, I created two tables: one to store my character lob content (CLOB) like html and js files, and another for my binary (BLOB content like images and other binary documents.
create table static_cfiles ( file_name varchar2(50), file_type varchar2(20), contents clob, primary key (file_name) ); create table static_bfiles ( file_name varchar2(50), file_type varchar2(20), contents blob, primary key (file_name) );
Then, I develop a procedure to read the tables based on the page name parameter. The CLOB is easy to read, you just assign it to a CLOB variable on PL/SQL and print it with HTP.P. But the BLOBS are a bit different. Here's a sample code for getting the blobs:
create or replace procedure showblob (p_name IN VARCHAR2) as page_b_contents blob; intImgSize INTEGER; content_type static_bfiles.file_type%TYPE; begin select file_type, contents into content_type, page_b_contents from static_bfiles where file_name = p_name; -- get image size intImgSize := dbms_lob.getlength(page_b_contents); -- print the header owa_util.mime_header(content_type, FALSE, NULL); htp.p('Content-length: '|| intImgSize); -- htp.p('Pragma: no-cache'); -- htp.p('Cache-Control: no-cache'); -- htp.p('Expires: Thu, 01 Jan 1970 12:00:00 GMT'); owa_util.http_header_close; -- download BLOB wpg_docload.download_file(page_b_contents); end; /
The wpg_docload.download_file procedure does all the magic: it signals the PL/SQL Gateway that the blob is to be downloaded to the client's browser. No copy is needed!
I've seen examples that do a dbms_lob.copy to a PL/SQL variable previously to displaying the file: that's dangerous! Imagine you have a 4GB document... you'll be eating all the PGA!
The Content-Length is useful when you are sending a big file since your browser will be able to know how much is left and can draw you a progress bar or just give you the percent done. I've also put there other common headers, feel free to use them as you need.
For static images you better not use the No-Cache option... unless you are changing images from time to time :)