Using variables within Connect By...???

[Spikmeister]

I'm trying to use a connect by within a dynamic cursor but I keep encountering the error:

ORA-09999: Message 9999 not found; product=RDBMS; facility=ORA
ORA-02063: preceding line from TEST.WORLD


Here's the sql from my dynamic cursor:

select hier_view.ho_id, hier_view.lo_id, hier_view.ho_trib
from (select ho_id, lo_id, ho_trib, Level
from hierarchy@test.world CONNECT BY lo_id = prior ho_id
start with lo_id = :cctid ) hier_view
where hier_view.ho_id like '%DOS%'


Can anyone please give me any idea as to why I'm encountering problems...??? I know that you can't use a CONNECT BY when joining tables but I wasn't aware that you couldn't use it with variables...???

 

[sem]

It's a problem may be with your db_link. Check whether you can select from hierarchy@test.world at least (check subquery separately). BTW, is it an Oracle database?

Regards, Dima

 

[Spikmeister]

It's an Oracle database and I have no problem selecting from hierarchy@test.world at all, even using a standard connect by rather than one in a dynamic cursor.