Re: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?
Date: Mon, 08 Jun 2009 16:16:05 +0200
thanks for your reply. First of all, I am using 10gR2.
Kenneth Naim schrieb:
> You have a few options.
> 1. use the chr function to specify the character so the actual character
> won't be in the path.
> 2. use the replace function on the sql_text field to strip out whichever
> character you are using.
> 3. filter your query out of the results based on its sqlid.
How would you do Options 2 and 3? A WHERE applied within the WITH and/or SELECT block of the hierarchical query does not avoid ORA-30004 as far as I can try here. Do you have an example that works, or that you think of it should work?
Option 1 seems not to work at all, since chr(64) is not recognized as "non-empty string" though all the web says the same as you did. Did I miss anything? (I would understand a 30004 here due to data, but 30003 is nothing I would have expected!)
1 rows selected
Error starting at line 3 in command:
row_number() over (partition by SQL_ID order by PIECE) rn, count(*) over (partition by SQL_ID) cnt from stats$sqltext
select SQL_ID, sys_connect_by_path(SQL_TEXT, CHR(64)) SQL_FULLTEXT
where rn = cnt
start with rn = 1
connect by prior SQL_ID = SQL_ID and prior rn = rn-1
Error at Command Line:9 Column:4
SQL Error: ORA-30003: Unzulässiger Parameter in Funktion SYS_CONNECT_BY_PATH 30003. 00000 - "illegal parameter in SYS_CONNECT_BY_PATH function" *Cause:
*Action: use a non-empty constant string as the second argument,
then retry the operation.
Your help is greatly appreciated,
-- Usn's IT Blog for Linux, Oracle, Asterisk http://www.usn-it.de -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 08 2009 - 09:16:05 CDT