Thursday, September 29, 2005

A Better View of Your Trees

I’ve been playing around with Trees over the last couple of days. One of the requirements dictates that I render a Tree based on a sub-set of a larger hierarchy of data. Luckily, the data is already in the correct format (ID, PARENT_ID, DESCRIPTION) for an Oracle HTML DB Tree, so I don’t have to make any changes to the data structure.

Due to the large size of the table, it was much more efficient to create an HTML DB Collection based on a subset of the data, and then use that Collection in my Trees. Sounds simple enough, but you cannot easily create a Tree based on a Collection, as the view HTMLDB_COLLECTIONS does not show up in the Select List of Tables/Views that you can base a Tree on.

The solution to this was almost too simple: Create your own View for HTMLDB_COLLECTIONS. This killed two birds with one stone: I could now select my view from the Select List when I created a new Tree, and I could also rename the columns to reflect those of the base table, rather than be forced to use the C00X nomenclature that HTML DB Collections use.

Here’s the code that I used to create my view on the HTMLDB_COLLECTIONS table:

create or replace view my_collection_view
(collection_name, seq_id, id, name, address)
as
select collection_name, seq_id, c001, c002, c003
from htmldb_collections
where collection_name = 'MY_COLLECTION';

Now that I have my View, I had better create a Collection so that my View will have some data. I chose to create a named procedure to create the Collection. This way, all of my code is managed in the database, making it easier to edit and debug. I simply call this procedure when the user clicks on the corresponding button to create the Collection:

procedure create_my_collection
is
l_sql varchar2(32767);
l_collection_exists boolean;
begin
l_sql := 'select id, name, address
from my_collection_view';
l_collection_exists :=
htmldb_collection.collection_exists(p_collection_name =>
'MY_COLLECTION');
if l_collection_exists = true then
htmldb_collection.delete_collection(p_collection_name =>
'MY_COLLECTION');
end if;
htmldb_collection.create_collection_from_query(
p_collection_name => 'MY_COLLECTION',
p_query => l_sql);
end;

Of course you can edit the SQL statement which I stuff into l_sql in the above code to reflect user-specified or other parameters. In my actual application, that’s exactly what I do. Remember to use the v('ITEM_NAME') syntax to refer to HTML DB Item values while in a named PL/SQL unit.

There you have it – a much more “civilized” way to work with HTML DB Trees and Collections.

7 comments:

Anonymous said...

Very useful. Just what I needed. Please keep blogging about HTMLDB.

Scott said...

Anonymous,

Glad it helped. I have no plans on stopping this blog, and will try my best to keep it updated.

Thanks,

- Scott -

Anonymous said...

Scott, you are my new hero! Works like a champ! Thanks for all your help on the forum area!

Steve

Scott said...

Steve,

I'm happy to be of assistance. I'm also working on an eBusiness Suite implementation w/BOM - perhaps we can compare notes at some point in the future!

Thanks,

- Scott -

Colin said...

Was this OTN forum posting the inspiration for this blog post?

http://forums.oracle.com/forums/thread.jspa?threadID=330279

Scott said...

Colin,

Yes - I mentioned that in the post itself. :)

- Scott -

Colin Sheppard said...

I also noticed this related topic in the Tips and Tricks section of HTML DB Studio:

http://htmldb.oracle.com/pls/otn/f?p=18326:54:::::P54_ID:921