Antwort: RE: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?
- From: "Martin Klier" <Martin.Klier@xxxxxxxxxx>
- To: kennaim@xxxxxxxxx
- Date: Tue, 9 Jun 2009 08:31:20 +0200
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@xxxxxxxxx>
>
> An:
>
> "'Martin Klier'" <usn@xxxxxxxxx>
> 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),'@?@','`') 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,'`','@?@') 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
