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)
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
l_sql varchar2(32767);
l_collection_exists boolean;
l_sql := 'select id, name, address
from my_collection_view';
l_collection_exists :=
htmldb_collection.collection_exists(p_collection_name =>
if l_collection_exists = true then
htmldb_collection.delete_collection(p_collection_name =>
end if;
p_collection_name => 'MY_COLLECTION',
p_query => l_sql);

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.


Anonymous said...

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

Scott said...


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


- Scott -

Anonymous said...

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


Scott said...


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!


- Scott -

Colin said...

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

Scott said...


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: