Antwort: Re: Antwort: RE: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?
- From: "Martin Klier" <Martin.Klier@xxxxxxxxxx>
- To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- Date: Tue, 9 Jun 2009 11:08:47 +0200
Hi Stephane,
> 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
> as
> (
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Antwort: Re: Antwort: RE: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004? - Martin Klier
