I have asked this same question on StackOverflow here, but I think it may require more Oracle expertise.
The versions of Oracle I have tested this on, as provided by "select version from v$instance" are:
11.2.0.3.0 and
12.1.0.2.0
A simplified version of the query that fails is:
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
Not all StackOverflow users were able to replicate the issue, in fact, most were not. Could there be something else at play besides the version of the Oracle database?
Some observations on the behavior of the query:
- Changing the value "ninechars" to "ninecharsx" allows the query to work
- Changing the value "ninechars" to "abcdefghi" also breaks the query
- I have found a reliance on the number of characters in "ninechars" and the number of characters in the "- Unknown" portion of the last entry. From testing, it seems like as long as the length of these values match and are >= the third label - 2 chars, which is currently "Im stumped", causes the query to fail. So anything of 8+ characters will fail. For example,: changing "ninechars" to "works" and "- Unknown -" to "- see -" is ok. But changing "ninechars" to "ThisIsAnError" and "- Unknown -" to "YesItReallyIs -" breaks. However, changing "ninechars" to "ThisIsAnError" and "- Unknown -" to "YesItReallyIs -" and "Im stumped" to "Imreally stumped" then it works.
- Changing the occurrences of 2624 to any value >= 2628 allows the query to work, while changing them to any value <= 2624 causes the query to break. So it seems like there's a strange reliance on this record being first by id
- 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
- Changing the value "- Unknown -" to "SoUnknownmx" and changing the delimiter from " -> " to "mxWhyAreYouBroken" causes the query to fail. There also seems to be some weird reliance on the leftover characters in the last entry matching the beginning of the delimiter. The characters in the last entry that matter are the leftover characters that exceed the length of the first entry "ninechars"... in this case that would be the "mx" portion
Please help! What is the source of the ORA-30004 error? Why does Oracle think that the delimiter is appearing as part of a column value?