Geek asks and answers

Oracle ORA-30004 when using the SYS_CONNECT_BY_PATH function,

ORA-30004when using a function, SYS_CONNECT_BY_PATHcannot have a separator as part of a column

Action: Use a different delimiter that does not appear in any value column, then try again.

Error:

select ...
Sys_Connect_By_Path(myVariable || ':' || mySecondVariable, ' --> ') "myNewVar",
...

Works:

select ...
Sys_Connect_By_Path(myVariable || ':' || mySecondVariable, ' -> ') "myNewVar",
...

In the data we found text similar to this

  • SomeText B--More Text
  • SomeText A--More Text

Since there is no '-->'or for this mater no ' -->' in the data, why the first error? The second has space front and back.

+5
source share
1 answer

Thats because it --is part of the delimiter -->, but not part of the delimiter ->.

Even if your data value matters -->, this request should not be an 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). .

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). , , .

( )

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 -> -->, , --> ->.

+8

More articles:


All Articles