Passing quoted string in Test Window

[Gary Eckhardt]

I'm currently debugging a sql statement, and I need to set up a variable like 'ABCDE','FGHIJ','KLMNO'.... etc, with the quotes and the commas in the string. Everything I've tried so far has given me a ORA-01036. The section that the variable, "lContributions" is used as such:

(select *
          from (select trim(substr(txt,
                                   instr(txt, ',', 1, level) + 1,
                                   instr(txt, ',', 1, level + 1) -
                                   instr(txt, ',', 1, level) - 1)) as token
                  from (select ',' || lContributions || ',' as txt from dual)
                connect by level <=
                           length(txt) - length(replace(txt, ', ', '')) - 1))

First off, does the test window all me to pass in a variable this way, with the quotes and commas in the string, or should it be written a different way? Is there any way of seeing the actual variable replacements in the code so I can see if it's a coding issue?

Thanks.

 

[Theod]

Hi,

in an sql-Window you'll see your replacements, if you use &lContributions as an variable.

select ',' || &lContributions || ',' as txt from dual;

With 'ABCDE','FGHIJ','KLMNO' the result is one record with 3 colums.

Column => Value
','||'ABCDE' => ,ABCDE
'FGHIJ' => FGHIJ
TXT => KLMNO,