Oracle ORA-30004 使用 SYS_CONNECT_BY_PATH 函数时，
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11870605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle ORA-30004 when using SYS_CONNECT_BY_PATH function,
提问by Nathan Stanford
ORA-30004when using SYS_CONNECT_BY_PATHfunction, cannot have seperator as part of the column
ORA-30004使用SYS_CONNECT_BY_PATH函数时，不能将分隔符作为列的一部分
Action: Use another seperator which does not occur in any column value, then retry.
行动：使用另一个不会出现在任何列值中的分隔符，然后重试。
Error on:
错误：
select ...
Sys_Connect_By_Path(myVariable || ':' || mySecondVariable, ' --> ') "myNewVar",
...
Works:
作品：
select ...
Sys_Connect_By_Path(myVariable || ':' || mySecondVariable, ' -> ') "myNewVar",
...
In the data we found some text like this
在数据中我们发现了一些这样的文字
SomeText B--More TextSomeText A--More Text
SomeText B--More TextSomeText A--More Text
Since there is no '-->'or for that mater no '-->' in the data why does the first one error? The second one has a space in front and on the end.
既然数据中没有'-->'或没有“ -->”，为什么第一个错误？第二个在前后各有一个空格。
回答by AnBisw
Thats because --is a part of -->separator but not a part of ->separator.
那是因为--是-->分隔符的一部分而不是分隔符的一部分->。
Even if your data value has -->this query should not error. Like below.
即使你的数据值有-->这个查询也不应该出错。像下面。
SQL> select Sys_Connect_By_Path('SomeText B-->More Text' || ':' || 'SomeText A-->More Text', ' --> ') "myNewVar"
from dual
connect by rownum<=3;
myNewVar
----------------------------------------------------
--> SomeText B-->More Text:SomeText A-->More Text
--> SomeText B-->More Text:SomeText A-->More Text --> SomeText B-->More Text:SomeText A-->More Text
--> SomeText B-->More Text:SomeText A-->More Text --> SomeText B-->More Text:SomeText A-->More Text --> SomeText B-->More Text:SomeText A-->More Text
The separator above is -->, notice the whitespace. This whitespace is considered as part of the separator i.e. chr(1)||chr(45)||chr(45)||chr(62)||chr(1). This entire string is not a part of your data or column value.
上面的分隔符是-->，注意空格。该空格被视为分隔符的一部分，即chr(1)||chr(45)||chr(45)||chr(62)||chr(1). 整个字符串不是您的数据或列值的一部分。
Where as below would error
以下哪里会出错
SQL> select Sys_Connect_By_Path('SomeText B-->More Text' || ':' || 'SomeText A-->More Text', '-->') "myNewVar"
from dual
connect by rownum<=3;
ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value
30004. 00000 - "when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value"
*Cause:
*Action: Use another seperator which does not occur in any column value,
then retry.
The separator above is -->, notice there is no whitespace i.e. chr(45)||chr(45)||chr(62). This entire string is indeed a part of your data or column value and hence the error.
上面的分隔符是-->，注意没有空格，即chr(45)||chr(45)||chr(62)。整个字符串确实是您的数据或列值的一部分，因此是错误的。
And here's a solution (performance un-tested)
这是一个解决方案（性能未经测试）
select regexp_replace(Sys_Connect_By_Path('SomeText B-->More Text' || ':' || 'SomeText A-->More Text', ' -> '),' -> ','-->') "myNewVar"
from dual
connect by rownum<=3;
myNewVar
--------------------------------------
-->SomeText B-->More Text:SomeText A-->More Text
-->SomeText B-->More Text:SomeText A-->More Text-->SomeText B-->More Text:SomeText A-->More Text
-->SomeText B-->More Text:SomeText A-->More Text-->SomeText B-->More Text:SomeText A-->More Text-->SomeText B-->More Text:SomeText A-->More Text
Explanation -Here(in the query above) ->(with space) is not part of the data here i.e. -->. Once the column is conected by path the regexp_replacereplaces all occurences of ->with -->so this way you still get to have -->as your separator instead of ->.
说明 -这里（在上面的查询中）->（带空格）不是这里数据的一部分，即-->. 一旦该列通过路径连接，就会regexp_replace替换所有出现的->with -->，这样您仍然可以将其-->作为分隔符而不是->.
