Re: String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004?
- From: Martin Klier <usn@xxxxxxxxx>
- To: Kenneth Naim <kennaim@xxxxxxxxx>
- Date: Mon, 08 Jun 2009 16:16:05 +0200
Hi Kenneth,
thanks for your reply. First of all, I am using 10gR2.
Kenneth Naim schrieb:
> You have a few options.
> 1. use the chr function to specify the character so the actual character
> won't be in the path.
> 2. use the replace function on the sql_text field to strip out whichever
> character you are using.
> 3. filter your query out of the results based on its sqlid.
How would you do Options 2 and 3? A WHERE applied within the WITH and/or
SELECT block of the hierarchical query does not avoid ORA-30004 as far
as I can try here. Do you have an example that works, or that you think
of it should work?
Option 1 seems not to work at all, since chr(64) is not recognized as
"non-empty string" though all the web says the same as you did. Did I
miss anything? (I would understand a 30004 here due to data, but 30003
is nothing I would have expected!)
===============================
CHR(64)
-------
@
1 rows selected
===============================
Error starting at line 3 in command:
with data
as
(
select sql_id,
SQL_TEXT,
row_number() over (partition by SQL_ID order by PIECE) rn,
count(*) over (partition by SQL_ID) cnt
from stats$sqltext
)
select SQL_ID, sys_connect_by_path(SQL_TEXT, CHR(64)) SQL_FULLTEXT
from data
where rn = cnt
start with rn = 1
connect by prior SQL_ID = SQL_ID and prior rn = rn-1
Error at Command Line:9 Column:4
Error report:
SQL Error: ORA-30003: Unzulässiger Parameter in Funktion SYS_CONNECT_BY_PATH
30003. 00000 - "illegal parameter in SYS_CONNECT_BY_PATH function"
*Cause:
*Action: use a non-empty constant string as the second argument,
then retry the operation.
===============================
Your help is greatly appreciated,
Martin
--
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
