I've collected some technics about using Hierarchical Trees in Oracle, and wrote small article "Hierarchical Queries (for nerds)". I've used some tricks in this module.Please read it before continiue reading this doc.
Generic_tree_types table stores meta data about a tree type. All instances of a tree type are stored in the same database table. Each tree type can have a set of Oracle functions for retrieving information (typically name) of the application data that is in the tree.
Generic_tree_functions is table for storing Oracle functions belonging to a certain tree type. These functions are used to retrieve node labels or other application data structured in the tree.
Generic_trees is Table for storing meta data about tree instances of different types. The actual tree structure is stored in a table (with the name specified in generic_tree_types) generated by the generic tree API.
-- create new tree type FUNCTION new ( type_id in generic_tree_types.type_id%TYPE default null, owner_id in generic_tree_types.owner_id%TYPE default null, type_name in generic_tree_types.type_name%TYPE, pretty_name in generic_tree_types.pretty_name%TYPE, pretty_plural in generic_tree_types.pretty_plural%TYPE, tree_struct_table in generic_tree_types.tree_struct_table%TYPE, default_function_no in generic_tree_types.default_function_no%TYPE default null, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null ) RETURN generic_tree_types.type_id%TYPE ; -- get tree type name FUNCTION name ( object_id in acs_objects.object_id%TYPE ) return varchar2; -- delete tree type PROCEDURE delete ( type_id in acs_objects.object_id%TYPE ); -- check db for possible conflicts before calling generate_db_objects function FUNCTION check_before_create ( p_type_id generic_tree_types.type_id%TYPE, error_descr in out varchar2 ) RETURN integer; -- generate nessesary objects in db for tree type FUNCTION generate_db_objects ( p_type_id generic_tree_types.type_id%TYPE, error_descr in out varchar2 ) RETURN integer; -- drop db objects for tree type FUNCTION drop_db_objects ( p_type_id generic_tree_types.type_id%TYPE, errors_descr in out varchar2 ) RETURN integer;
Table for storing the actual tree structure is stored in a table generated by the generic tree API.
-- Create new tree instance FUNCTION new ( tree_id in generic_trees.tree_id%TYPE default null, type_id in generic_tree_types.type_id%TYPE, tree_name in varchar2, description in varchar2, default_function_no in generic_trees.default_function_no%TYPE default null, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, context_id in acs_objects.context_id%TYPE default null ) RETURN generic_trees.tree_id%TYPE ; -- get instance name FUNCTION name ( object_id in acs_objects.object_id%TYPE ) return varchar2; -- delete tree instance PROCEDURE delete ( tree_id in acs_objects.object_id%TYPE ); -- add new node to the tree instance FUNCTION add_node ( p_node_id integer default null, p_tree_id in generic_trees.tree_id%TYPE, p_object_id in acs_objects.object_id%TYPE, p_parent_node_id integer ) RETURN integer ; -- delete node from tree instance PROCEDURE del_node ( p_node_id integer, p_tree_id in generic_trees.tree_id%TYPE, p_delete_childs boolean default false ) ; -- copy tree/part of tree structure to the same/another tree instance PROCEDURE copy_tree ( source_tree integer, dest_tree integer, source_node_id integer, dest_parent_node_id integer, depth integer default null ); -- move tree/part of tree structure to the same/another tree instance PROCEDURE move_tree (source_tree integer, dest_tree integer, source_node_id integer, dest_parent_node_id integer) ; -- index lef_ind and right index columns (nested set model support) PROCEDURE nested_on (p_tree_id integer);