Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH [message #294338] |
Thu, 17 January 2008 04:52  |
 |
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
I've been trying to get going with SYS_CONNECT_BY_PATH (based upon a sample by Maaher) and bumped into the following "error":
DECLARE
sepCHAR CONSTANT CHAR := 'X' ;
sepCHAR1 CONSTANT CHAR(1) := 'X' ;
sepVARCHAR210 CONSTANT VARCHAR2(10) := 'X' ;
theCat TAB_TO_PIVOT.THE_CAT%TYPE ;
theVal VARCHAR2(32767) ;
BEGIN
SELECT the_cat ,
MAX(SYS_CONNECT_BY_PATH(the_val,sepCHAR) the_val
INTO theCat , theVal
FROM
(
SELECT row_number() OVER ( PARTITION BY the_cat ORDER BY the_id ) seq_no ,
the_cat , the_val
FROM tab_to_pivot
WHERE the_cat = 1
ORDER BY the_cat, the_id
)
CONNECT BY the_cat = PRIOR the_cat
AND seq_no = PRIOR seq_no + 1
START WITH seq_no = 1
GROUP BY the_cat ;
END ;
/
Whatever argument I pass to the SYS_CONNECT_BY_PATH(the_val,sepCHAR) call, I get the error:
SELECT the_cat ,
*
ERROR at line 8:
ORA-06550: line 8, column 2:
PL/SQL: ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH function
ORA-06550: line 8, column 2:
PL/SQL: SQL Statement ignored
Only when I use a literal in the call, then there's no problem.
What am I overlooking here?
Scripts to create table & populate can be found here
(thanks to Maaher)
|
|
|
|
|
|
|
|