Re: Antwort: RE: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: Martin.Klier@xxxxxxxxxx
- Date: Tue, 09 Jun 2009 10:08:22 +0200
Martin,
I learned something today. In fact, sys_connect_by_path() seems to want
nothing but a plain hard-coded string as its second argument (if it's
not a bug, it looks furiously like one) - at least on my 11.1.0.6. Any
string function or even operation is rejected. I have tried to join with
a (select 'anything' as sep from dual) and refer to sep in the
expression, no chance.
But I'm stubborn.
If you manage to type a character weird enough by hitting 'Alt Gr' and
any random key on your keyboard, you can probably get something that is
accepted by SYS_CONNECT_BY_PATH. You just have to care about two things:
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.
It doesn't solve the 4,000 character limit, though.
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)
/
HTH
SF
Martin Klier wrote:
> 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
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
