SQL & PL/SQL

Hi all,

This is a "my query takes too long" question but it relates to the ALL_TAB_COLUMNS view.

I'm trying to generate some basic DML scripts from a large number of tables (insert/update) the cheating way by selecting columns from all_tab_columns

e.g.

select 'insert into '||table_name||' ('
  from all_tables
 where table_name = 'TABLE_NAME_HERE'
union all
select column_name||','
  from all_tab_columns
where table_name = 'TABLE_NAME_HERE'
--etc.

however, I want the column names all on one line.

so I have the following, which I imagine should create the column_name's on one line but it "runs for ages"

select sys_connect_by_path(column_name,', ') c
  from all_tab_columns
 where owner = 'WHITEHAT'
   and table_name = 'FOO'
   and connect_by_isleaf = 1
   start with column_id = 1
   connect by column_id = prior column_id + 1

I am using 10gR2 so unfortunately I can't use recursive subquery factoring just yet, although that equivalent runs quickly as expected on our 11g environment:

with t (column_name, column_id) 
             as (select cast(column_name as varchar2(4000)), column_id
                   from all_tab_columns
                  where owner = 'WHITEHAT'
                    and table_name = 'FOO'
                    and column_id = 1
                  UNION ALL
                  SELECT cast(t.column_name||', '||atc.column_name as varchar2(4000))
                        ,atc.column_id
                    from t
                        ,all_tab_columns atc
                   where owner = 'WHITEHAT'
                    and table_name = 'FOO'
                    and atc.column_id = t.column_id + 1
                 )
select column_name
  from (
         select column_name, column_id, max(column_id) over () max_col_id
         from t           
       )
 where column_id = max_col_id

what am I doing wrong?