Selecting All Parents in a Path Enumerated Tree

Hi,

I have a tree structure which is maintained through the use of a path enumerated column:

CREATE TABLE items (
 item_id NUMERIC NOT NULL,
 path VARCHAR2(64) NOT NULL
);

The path is is a colon separated list of ids of the nodes of the tree. So, for example, in this structure:

0 -> 1 -> 2 -> 3 -> 4

item id 4 would have a path of '0:1:2:3' (0 is the root of all items, and does not actually exist). Notice that the path does not include the item's own id.

I would like to select all of the items in a given item's path:

SELECT id, path FROM items WHERE id IN (PATH_TO_IDS(path));

or maybe:

SELECT id, path FROM items WHERE PATH_EQUALS(id, path));

or maybe something else altogether. This should return:

ITEM_ID PATH
------- -------

      1 0
      2 0:1
      3 0:1:2
      4 0:1:2:3