Regarding outer joins, "not exists (select ... " should be marginally more efficient than "0 = (select count(*) ... "
Clobs should become lztext, which is used just like varchar(). varchar2(n) should become either varchar(n) for small strings or lztext for bit strings where you don't want to state a limit.
Connect by is not on the PostgreSQL "todo" list, sorry :(
