Antwort: Re: Antwort: RE: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?
Date: Tue, 9 Jun 2009 11:08:47 +0200
> But I'm stubborn.
> 1) Removing the weird character from your final result
> 2) Removing the weird character from the factorized expression, so that
> you don't hit your own query.
This is what I've been struggeling for, but wasn't able to figure it out. Your stuff is perfect for my case, thanks a lot!
> It doesn't solve the 4,000 character limit, though.
I will find a suitable solution for my special case, like Kenneth suggested, a limit on PIECE would do IMO.
> with data
> select sql_id,
> replace(SQL_TEXT, '¬', ' ') SQL_TEXT,
> row_number() over (partition by SQL_ID order by PIECE) rn,
> count(*) over (partition by SQL_ID) cnt
> from stats$sqltext
> select replace(SQL_FULLTEXT, '¬', '') SQL_FULLTEXT
> from (select SQL_ID,
> sys_connect_by_path(SQL_TEXT, '¬') SQL_FULLTEXT
> from data
> where rn = cnt
> start with rn = 1
> connect by prior SQL_ID = SQL_ID and prior rn = rn-1)
... works like a charm!
Thanks a lot to all who worked on this!
-- Mit freundlichem Gruß Martin Klier -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 09 2009 - 04:08:47 CDT