RE: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?
Date: Mon, 8 Jun 2009 13:18:00 -0400
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),'_at_?@','`') 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,'`','_at_?@') 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 ) bwhere line_desc=1
From: Martin Klier [mailto:usn_at_usn-it.de] Sent: Monday, June 08, 2009 10:16 AM
To: Kenneth Naim
Subject: 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!)
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 - 12:18:00 CDT