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

> 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

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