oracle8.1.6 select i.item_id as file_id, r.title as name, i.live_revision file_live_rev, r.mime_type as type, 1 as ordering_key, 0 as num from cr_items i, cr_revisions r, acs_objects o where i.item_id = o.object_id and i.live_revision = r.revision_id (+) and i.parent_id in (:my_folder_id) and acs_permission.permission_p(i.item_id, :user_id, 'read') = 't' and i.content_type = 'content_revision' UNION select i.item_id as file_id, f.label as name, 0 as file_live_rev, 'Folder', 0 as ordering_key, (select count(*) from cr_items where parent_id = i.item_id) as num from cr_items i, cr_folders f where i.item_id = f.folder_id and i.parent_id in (:my_folder_id) and acs_permission.permission_p(i.item_id, :user_id, 'read') = 't' order by ordering_key,name select package_id from (select item_id from cr_items connect by prior parent_id = item_id start with item_id = :my_folder_id) this, fs_root_folders where item_id = folder_id