CODEWITHSUNDEEP
oracle-database
user1333371
user1333371

Reputation: 618

Why do I get Ora-30004 when sys_connect_by_path delimiter is not present in column values

I have tested this on Oracle versions:
11.2.0.3.0
12.1.0.2.0

The following query throws an ORA-30004 error, but my delimiter of ' -> ' is not used in any of the column values:

with temptable as (
  select '2624' as id, 'ninechars' as label, '' as parentid from dual union 
  select '2625' as id, 'erewrettt' as label, '2624' as parentid from dual union 
  select '2626' as id, 'Im stumped' as label, '' as parentid from dual union 
  select '2627' as id, '- Unknown -' as label, '' as parentid from dual
)
select sys_connect_by_path(label, ' -> ' ) 
from temptable
start with parentid is null 
connect by prior id = parentid;


Some observations:

  • Changing the value "ninechars" to "ninecharsx" allows the query to work
  • Changing the value "ninechars" to "abcdefghi" also breaks the query
    • It seems like all nine character values here break the query

  • Leaving the value as "ninechars" and removing the last union statement, which is not connected to any of the other records, allows the query to work
  • Changing the delimiter from ' -> ' to ' *> ' allows the query to work


Question
What is the source of the ORA-30004 error? Why does Oracle think that the delimiter is appearing as part of a column value?

Edit: Thanks to bobdylan for this pastebin.com/Ad1edFcJ link left in the comments that helps illustrate the issue

Upvotes: 4

Views: 1791

Answers (1)

Francisco Sitja
Francisco Sitja

Reputation: 1003

This smells like a bug. If you need to workaround it and implement your logic, alternatively you can use recursive subquery factoring (recursive With), which works alright in 11.2.0.4:

SQL> with t (id, label, parentid, reportlevel, fake_connect_by_path) as (
  2  select id, label, parentid, 0 as reportlevel, ' -> ' || label as fake_connect_by_path
  3    from temptable
  4   where parentid is null
  5   union all
  6  select tt.id, tt.label, tt.parentid, reportlevel + 1, t.fake_connect_by_path || ' -> ' || tt.label as fake_connect_by_path
  7    from temptable tt
  8    join t on t.id = tt.parentid
  9  )
 10  select fake_connect_by_path
 11    from t;
FAKE_CONNECT_BY_PATH
--------------------------------------------------------------------------------
 -> ninechars
 -> Im stumped
 -> - Unknown -
 -> ninechars -> erewrettt

Upvotes: 1

Related Questions