Dirk, yes, grabbing the magic id with the tcl proc then using the result as a bindvar in the query is a clean way to avoid using a magic number in the query.
Also ... acs_object_context_index is weighing in at less than four times the number of objects. This is a table with three integer columns in it, a pretty skinny table, the actual storage used will be considerably less than that for the objects and auxillary tables.
Hmmm...one concern with your query (even though you don't want to talk about it :) is the number of physical reads. Looks like bumping the shared buffer space for Oracle is in order, no?
In the next few weeks I'll load up my nice new server with its nice new disks with a much larger number of objects than I've tested with in the past. I'll play around with expanding the hierarchy view in Oracle as we do in PG and see what effect that has.
