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 :)

Jose.

UPDATE 16-Jul-2009: apparently it's not working anymore! It took several weeks for facebook to fix it!

In order for Facebook to patch this, I'm writing this method that has become so popular these last days. You can see any album of any user in Facebook by following the next steps:

1) Get the person's ID: search him/her on facebook and look in the "View Frinds" link where you'll see something like this: http://www.facebook.com/friends/?id=1660869234. This number is the user's ID.

2) Go to http://developers.facebook.com/tools.php?api -> API Test Console

3) Choose "Facebook PHP Client" in Response Format.

4) Choose "fql.query" in Method.

5) Now you'll see a text area called "query": wrote there the following replacing xxxxxxxxxx with the contact's ID you find in the first step:

SELECT name, link FROM album WHERE owner=xxxxxxxxxx

6) Run the query to find all the contact's albums. Just take the URL of the album you want and paste it in your browser.

That's it! Let's hope Facebook fix it soon, but until then, enjoy looking at pictures your enemies shouldn't have uploaded ;)

Recently I posted an article about displaying a binary file from pl/sql directly to a browser. This is done with 10.2's feature EPG (Embedded PL/SQL Gateway). Yes, you can serve HTTP content (html, js, json, binary files like images and of course xml) directly from your Oracle database. I'll be posting a detailed article about how to configure it (if you need help now, check out this site). For now I'll post a very handy script I've just written.

Sometimes you don't know how EPG is configured and you have to go through the DBMS_EPG package to find that out. It's not a straight thing: you need to do some plsql programming... it would be nice a v$ view but it's not available as far as I know. So this script show come handy for those in need (I know I'll be one of them in the future ;) ).

set serveroutput on

declare
	/**
	*  Get all the database DADs and their configuration
	*  Written by jose.canciani
	*  Source: http://www.4tm.com.ar/4tmsite/wordpress/?p=31
	*/
	dads dbms_epg.varchar2_table;
	mappings dbms_epg.varchar2_table;
	attr_names dbms_epg.varchar2_table;
	attr_values dbms_epg.varchar2_table;
begin
	dbms_output.put_line('-Global Attributes:');
	dbms_epg.get_all_global_attributes(attr_names,attr_values);
	if attr_names.count > 0 then
		for i in 1..attr_names.count
		loop
			dbms_output.put_line('-  '||attr_names(i)||': "'||attr_values(i)||'"');
		end loop;
	else
		dbms_output.put_line('-  Not global attributes found.');
	end if;
	dbms_output.new_line;
	dbms_epg.get_dad_list(dads);
	for i in 1..dads.count
	loop
		dbms_output.put_line('-DAD: "'||dads(i)||'"');
		dbms_epg.get_all_dad_mappings(dads(i),mappings);
		dbms_output.put('-  Mappings:');
		for j in 1..mappings.count
		loop
			dbms_output.put(' "'||mappings(j)||'"');
		end loop;
		dbms_output.new_line;
		dbms_output.put('-  Authorized Users:');
		for j in (select username from dba_epg_dad_authorization where dad_name = dads(i))
		loop
			dbms_output.put(' "'||j.username||'"');
		end loop;
		dbms_output.new_line;
		dbms_output.put_line('-  Attributes:');
		dbms_epg.get_all_dad_attributes(dads(i),attr_names,attr_values);
		for j in 1..attr_names.count
		loop
			dbms_output.put_line('-    '||attr_names(j)||': "'||attr_values(j)||'"');
		end loop;
	end loop;
end;
/

Here's a quick output of the script (I only have one DAD configured and no global attributes):

-Global Attributes:
-  Not global attributes found.
-DAD: "ORAMON"
-  Mappings: "/oramon/*"
-  Authorized Users: "ORAMON"
-  Attributes:
-    database-username: "ORAMON"
-    default-page: "showpage?p_name=index.html"
-    error-style: "DebugStyle"
-    owa-debug-enable: "On"

That's all!