Re: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?

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!)

CHR(64)

1 rows selected

Error starting at line 3 in command:
with data
 as
 (
  select sql_id,
    SQL_TEXT,
    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

  from data
  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
Error report:
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,
Martin

-- 
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de

--
http://www.freelists.org/webpage/oracle-l