Home » SQL & PL/SQL » SQL & PL/SQL » Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH (10.2.0.3.0)
Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH [message #294338] Thu, 17 January 2008 04:52 Go to next message
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)
Re: Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH [message #294342 is a reply to message #294338] Thu, 17 January 2008 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Second parameter must be a character literal not a variable.

Regards
Michel
Re: Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH [message #294346 is a reply to message #294342] Thu, 17 January 2008 05:07 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Thu, 17 January 2008 12:03
Second parameter must be a character literal not a variable.

Regards
Michel



That's what I feared.

Didn't find anything in documentation telling me so (but then again, I didn't look very long)

Re: Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH [message #294350 is a reply to message #294346] Thu, 17 January 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is not clear in the documentation.
Syntax graph says "char", if you could give anything it should say "expression" accordingly to Oracle standard.
But it also says "column" for the first parameter when it should say "expression" as you can put any expression (that does not return the character speficified in the second parameter).

Regards
Michel
Re: Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH [message #294353 is a reply to message #294346] Thu, 17 January 2008 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Metalink note 173678.1

Regards
Michel
Re: Using a non-literal - Illegal parameter in SYS_CONNECT_BY_PATH [message #294367 is a reply to message #294353] Thu, 17 January 2008 06:09 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Thanks Michel.

The note is a bit clearer Smile
Previous Topic: Monthly Totals
Next Topic: duplicate records
Goto Forum:
  


Current Time: Sun Dec 04 00:30:07 CST 2016

Total time taken to generate the page: 0.13810 seconds