9 Replies Latest reply on Jul 26, 2017 6:13 PM by Sanjeev Chauhan

    ORA-30003 in SQL Developer but not in SQLPlus

    Sanjeev Chauhan

      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: