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

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Mon, 8 Jun 2009 13:18:00 -0400
Message-ID: <005901c9e85d$14be7b00$3e3b7100$_at_com>

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

where line_desc=1

-----Original Message-----
From: Martin Klier [mailto:usn_at_usn-it.de] Sent: Monday, June 08, 2009 10:16 AM
To: Kenneth Naim
Cc: oracle-l_at_freelists.org
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)

---

_at_

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

Received on Mon Jun 08 2009 - 12:18:00 CDT