我是9.2.0.1,测试没有问题。你试试看看。
create table t1 as select rownum r from dual connect by rownum<=5;
create procedure p1(maxlevel number:=0)
as
v_maxlevel number;
begin
if maxlevel<=0 then
select count(*) into v_maxlevel from t1;
else
v_maxlevel:=maxlevel;
end if;
for t in (select substr(SYS_CONNECT_BY_PATH(r,','),2) s from t1 where level<=v_maxlevel connect by prior r<r) loop
dbms_output.put_line(t.s);
end loop;
end;
/
set serveroutput on
exec p1
insert into table a
(
select a.a||b.b||c.c||d.d xx from
(select 1 a from dual union select null from dual) a,
(select 2 b from dual union select null from dual) b,
(select 3 c from dual union select null from dual) c,
(select 4 d from dual union select null from dual) d
where a.a||b.b||c.c||d.d is not null
)
select a.a||b.b||c.c||d.d from
(select 1 a from dual union select null from dual) a,
(select 2 b from dual union select null from dual) b,
(select 3 c from dual union select null from dual) c,
(select 4 d from dual union select null from dual) d