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 09:22:02 -0400
Message-ID: <001801c9e83c$1d7839f0$5868add0$_at_com>

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.

Ken

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier
Sent: Monday, June 08, 2009 6:05 AM
To: oracle-l_at_freelists.org
Subject: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?

Dear list,

I've got a question about hierarchical queries, especially sys_connect_by_path():

sys_connect_by_path requires a seperator that does not exist in the returned values, otherwise it spawns an ORA-30004 (or an ORA-30003 if an empty string is specified, which would do for my case).

Is there any way to avoid that? I want to use the query on a statspack sql table (stats$sqltext), so no matter what I choose, sooner or later it will be part of the data. :)

What I did try, is to get the full SQL text from stats$sqltext in one line, without whitespaces. It works very well, but only until my query becomes part of the SQL history. :P

As I'm typing that, I become aware that I can exclude this query from this query, but it would be generally interesting if I somehow can be fully independent from a fixed seperator string (or a seperator in general), since hierarchivcal queries are useful otherwise, too, and it's always a risk to rely on a fixed expression?

My code (the skeleton "stolen" from hali's blog):

---

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

)
-- TRANSLATE replaces '_' with '_', and '$$$' with nothing,

• since no replacement char is specified :) select SQL_ID, translate(sys_connect_by_path(SQL_TEXT,'$$$'), '_$$$', '_') SQL_TEXT from data where rn = cnt start with rn = 1 connect by prior SQL_ID = SQL_ID and prior rn = rn-1 order by SQL_ID ;

  ---

Thanks in advance
Martin
--

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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jun 08 2009 - 08:22:02 CDT