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
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:
l_sql := 'select id, name, address
if l_collection_exists = true then
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.