Strange problem in Oracle hierarchical query
Today I’ve decided to get rid of the Windows virtual where Oracle XE 10 and to move data to Oracle XE installed on my Fedora host.
Everything seemed to migrate correctly using imp/exp utilities. After I ran the appllication server and tested it a bit, I’ve ran into the problem with the hierarchical queries. I’m trying to run the query like:
SELECT sys_connect_by_path(ent.STATE,’//’) as state
FROM E1 element LEFT JOIN E2 ent ON element.id = ent.id
WHERE CONNECT_BY_ISLEAF = 1
START WITH element.ID = ‘Test’
CONNECT BY NOCYCLE PRIOR element.id = element.PARENT_ELEMENT_ID
This query now fails with the error:
ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value
Since the data is identical on both Windows and Fedora installations I didn’t expect such a weird behaviour. So the problem is in ‘//’ characters which are used to concatenate strings. I’ve checked all the strings in E2#STATE and coudn’t find ‘/’ chars in any record. This is not a symptom described in: http://www.error-code.org.uk/view.asp?e=ORACLE-ORA-30004. After I’ve replaced ‘//’ with ‘/’ or with ‘@’ everything works fine. However the reason of the problem is still unclear. The only assumption I have is that this error is OS specific. So I’ve checked various combinations like ”, ‘\’, ‘//’, ‘/’ on the Windows Oracle installation, but it worked correctly.
Strange things happen.