oracle8.1.6
select i.item_id as file_id,
r.title as name,
i.live_revision,
content_item.get_path(i.item_id,file_storage.get_root_folder(:package_id)) as path,
r.mime_type as type,
to_char(o.last_modified,'YYYY-MM-DD HH24:MI') as last_modified,
-- dbms_lob.getlength(r.content) as content_size,
-- instead of above, we rely on CR to calculate for us the size of
-- r.content.
r.content_length as content_size,
1 as ordering_key
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 = :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,
content_item.get_path(f.folder_id) as path,
'Folder',
NULL,
0,
0
from cr_items i, cr_folders f
where i.item_id = f.folder_id
and i.parent_id = :folder_id
and acs_permission.permission_p(folder_id, :user_id, 'read') = 't'
order by ordering_key,name