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

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_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
>
>
>

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