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.