arcster.com Blog

Here’s a ColdFusion/Oracle problem that took way longer than I thought it would.

I wanted to exclude a couple names from a result set an pass the names in a single-quote, comma-delimited list:

<cfset tempexcludelist ="PHIL,DON">
<cfset VARIABLES.excludelist = "("&ListQualify(tempexcludelist,"'")&")">

My Oracle query looked like this:

<cfquery name="who" datasource="#APPLICATION.ds#">
select
FULL_NAME,
initcap(TITLE) as TITLE,
BUILDING,
ROOM,
PHONE_NUMBER,
PRIMARYEMAIL
from
people
where
userid NOT IN #VARIABLES.excludelist#
</cfquery>

My expectation was for the final line to be output like so:

userid NOT IN ('PHIL','DON')

But it didn’t turn out that way. Instead I got

[Macromedia][Oracle JDBC Driver][Oracle]ORA-00907:
missing right parenthesis

and my debug output showed Oracle was processing the following
select  FULL_NAME, initcap(TITLE) as TITLE, BUILDING,
ROOM, PHONE_NUMBER, PRIMARYEMAIL from people where userid NOT IN (''PHIL'',''DON'')

Wha? Where did the extra single quotes come from?

I tried various alternatives for my listqualify qualifier, including

<cfset VARIABLES.excludelist = "("&ListQualify(tempexcludelist,"")&")">

<cfset VARIABLES.excludelist = "("&ListQualify(tempexcludelist,"''")&")">

<cfset VARIABLES.excludelist = "("&ListQualify(tempexcludelist,"#Chr(34)#")&")">

but nothing worked.

I finally resolved the problem by using a unique employee number so that I didn’t have to worry about quote marks, but next time I may not be so lucky. So where is the problem? ColdFusion? Oracle? Me?