Why does a connect by expression in a FOR loop, execute only once?
It's a bug in a version of Oracle 9i confirmed up to 9.2.0.8, but not beyond.
It was previously discussed on Ask Tom, the response simply being that "sqlplus does that".
Premier support for Oracle 9.2 ended on 2007-07-31 and extended support ended on 2010-06-30. To fix this it is recommended that you upgrade to a current version of Oracle; if unable, you should patch your database past version 9.2.0.8.