In SQL Developer 17.2.0.188 and 4.2.0.17.089, I get the error below. However, the anonymous blocks works fine in SQLPlus and SQL Developer 3.2.20.09 (I just happened to have this). At first, I thought this was a 12.1 database bug. But three forum users and my SQLPlus test confirmed this as a SQL Developer bug.
This works
with
tab ( parent, child ) as (
select null, 1 from dual union all
select 1, 2 from dual
)
select ltrim(sys_connect_by_path(child, '/'), '/') as path
from tab
start with parent is null
connect by prior child = parent
/
PATH
1
1/2
This doesn't
begin
for rec in (
with
tab ( parent, child ) as (
select null, 1 from dual union all
select 1, 2 from dual
)
select ltrim(sys_connect_by_path(child, '/'), '/') as path
from tab
start with parent is null
connect by prior child = parent
)
loop
null;
end loop;
end;
/
Error report -
ORA-06550: line 3, column 5:
PL/SQL: ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH function
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action: