I was at the airport when I replied to your email so I didn't have access to a db. I am looking for a 10gr2 instance with statspack installed and so far I haven't found one yet as we use awr for 10g db's. I used dba_source as it should be similar in concept to stats$sqltext with the following notes. 1. I used the row_number function to get the line numbers even though dba_source has them already as I wasn't sure if stats$sqltext piece started with 1 like dba_source. 2. I used row_number to do the filtering of the extraneous rows similar to how you used count. 3. You will run into an issue with sql statements than run over 4000 characters as concatenation operations which sys_connect_by_path uses cannot exceeed 4000 characters. I used owner='CUSTOM' and line<=10 to prevent this error (ora-01489 result of string concatenation is too long) as an example. This will defeat the entire purpose of this query. select owner,name,type,line,text original_text, length(sql_text) length_sql_text, replace(replace(sql_text,'`',null),'@?@','`') clean_text from (with data as (select /*+ materialize */ owner,name,type, row_number() over (partition by owner,name,type order by line) line, row_number() over (partition by owner,name,type order by line desc) line_desc, replace(text,'`','@?@') text from dba_source a where owner='CUSTOM' and line<=10 ) select /*+ materialize */ data.*, sys_connect_by_path(text,'`') sql_text from data start with line=1 connect by prior name = name and prior owner=owner and prior type=type and prior line = line-1 ) b where line_desc=1 -----Original Message----- From: Martin Klier [mailto:usn@xxxxxxxxx] Sent: Monday, June 08, 2009 10:16 AM To: Kenneth Naim Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004? 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!) =============================== 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 -- //www.freelists.org/webpage/oracle-l