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

From: Martin Klier <>
Date: Mon, 08 Jun 2009 16:16:05 +0200
Message-ID: <>

Hi Kenneth,

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:
with data
  select sql_id,
    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,

Usn's IT Blog for Linux, Oracle, Asterisk

Received on Mon Jun 08 2009 - 09:16:05 CDT

Original text of this message