String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?
- From: Martin Klier <usn@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 08 Jun 2009 12:04:55 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
