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

From: Martin Klier <Martin.Klier_at_klug-is.de>
Date: Tue, 9 Jun 2009 08:31:20 +0200
Message-ID: <OF31D4B77F.A8FEE7CC-ONC12575D0.0022D932-C12575D0.0023D3FF_at_klug-is.de>



Hi Kenneth,

thanks for your time. The query tactics you provided are interesting, but my major problem isn't solved yet: The seperator will still be needed, and be part of the query, and can't be filtered within.

> "Kenneth Naim" <kennaim_at_gmail.com>
>
> An:
>
> "'Martin Klier'" <usn_at_usn-it.de>
> I used dba_source as it should be similar in concept to stats$sqltext
with
> the following notes.
> ...
> This will defeat the entire purpose of this query.
>
>
> select owner,name,type,line,text original_text, length(sql_text)
> length_sql_text, replace(replace(sql_text,'`',null),'_at_?@','`') clean_text

> from (with data as
> (select /*+ materialize */ owner,name,type,
> row_number() over (partition by owner,name,type order
by
> line) line,
> row_number() over (partition by owner,name,type order
by
> line desc) line_desc,
> replace(text,'`','_at_?@') text
> from dba_source a
> where owner='CUSTOM'
> and line<=10
> )
> select /*+ materialize */ data.*, sys_connect_by_path(text,'`')
> sql_text
> from data
> start with line=1
> connect by prior name = name and prior owner=owner and prior
> type=type and prior line = line-1
> ) b
> where line_desc=1

--
Mit freundlichem Gruß


Martin Klier

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 09 2009 - 01:31:20 CDT

Original text of this message