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 Text
SomeText 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屋!