Forum OpenACS Q&A: Help needed porting a PG query to Oracle

I have not worked much with the tree/sortkey stuff we have for Postgres, which is a liability when trying to convert this query:

        SELECT n.node_id, i1.item_id
        FROM cr_items i1, cr_items i2, pa_package_root_folder_map m, site_nodes n
        WHERE m.folder_id = i2.item_id
          and i1.item_id = coalesce((select item_id from cr_revisions where revision_id = :photo_id),:photo_id)
          and n.object_id = m.package_id
          and i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey)
        limit 1

To work with Oracle. I looked at some other similar queries for inspiration but my porting attempts were not successful (resulted in no rows, which is wrong). Any suggestions?

Silly me! You need to port to Oracle. I should read, or not post so early.

Here is a similar Oracle query from file-storage as well

select site_node.url(node_id)
from site_nodes
where object_id = (select r.package_id,
r.folder_id as root_folder_id
from fs_root_folders r,
(select item_id as folder_id
from cr_items
connect by prior parent_id = item_id
start with item_id = :folder_id) t
where r.folder_id = t.folder_id)