Insert into my_table select (subquery) on 8.1.7

Hi,

I am attempting this in a PL/SQL routine and I get

PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

Which is fair enough. I understand that this is because the PL/SQL parser is being used as it doesn't understand the sub query. My only problem is that the reason I am using a sub query is because it's doing a hierarchical query, and I can't move that out into the main query either "ORA-01437: cannot have join with CONNECT BY" Which leaves me in a bit of a bind.

Is there any agreed work around (Bar an upgrade to 10g). I could use dynamic SQL, but would rather not, it makes it less maintainable. I think I can use a view to hide the nested sub query, but it seems a bit over the top creating a new view in the app just for a single insert statement.

any recommendations?

Thanks in advance,

Mike Received on Fri Jan 14 2005 - 11:16:09 CST