Oracle ORA-30004在使用SYS_CONNECT_BY_PATH函数时， [英] Oracle ORA-30004 when using SYS_CONNECT_BY_PATH function,
问题描述
ORA-30004使用SYS_CONNECT_BY_PATH函数时，不能将分隔符作为列的一部分
ORA-30004 when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of the column
操作:使用另一个分隔符，该分隔符在任何列中都不会出现 值，然后重试.
Action: Use another seperator which does not occur in any column value, then retry.
错误:
select ...
Sys_Connect_By_Path(myVariable || ':' || mySecondVariable, ' --> ') "myNewVar",
...
作品:
select ...
Sys_Connect_By_Path(myVariable || ':' || mySecondVariable, ' -> ') "myNewVar",
...
在数据中，我们发现了这样的文本
In the data we found some text like this
-
SomeText B--More Text -
SomeText 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.
推荐答案
那是因为--是-->分隔符的一部分，而不是->分隔符的一部分.
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
上面的分隔符是-->，请注意空格.该空格被视为分隔符的一部分，即chr(1)||chr(45)||chr(45)||chr(62)||chr(1).这整个字符串不属于您的数据或列值.
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.
以下内容会出错
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.
上面的分隔符是-->，请注意没有空格，即chr(45)||chr(45)||chr(62).整个字符串确实是您的数据或列值的一部分，因此也是错误.
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.
这是一个解决方案(性能未经测试)
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
说明-这里(在上面的查询中)->(带空格)不是此处数据的一部分，即-->.一旦通过路径连接了列，regexp_replace就会用-->替换所有出现的->，因此通过这种方式，您仍然可以将-->用作分隔符，而不是->.
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_replace replaces all occurences of -> with --> so this way you still get to have --> as your separator instead of ->.
这篇关于Oracle ORA-30004在使用SYS_CONNECT_BY_PATH函数时，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
