Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4313
  • Last Modified:

Oracle procedure and commas in multiple select string

In my cold fusion application I have a multiple select list. Once data is select from the multiple select list it is passed to an oracle procedure as an in parameter and used in the IN clause of my query.

Im using the following cold fusion code to pass comma separated values to procedure from cold fusion multiple select list:
<CFSET v_list = #ListChangeDelims(FORM.listRight,"','", ",")#>

And used it in my procedure call as follows:
<cfprocparam dbvarname="p_list_right" cfsqltype="cf_sql_varchar"  value=#v_list# type="in">

The above works fine if there is no commas in the listRight string. In case there are commas, no data set is returned. How do I resolve this issue?  I was asked to use array, by not sure.

  • 2
2 Solutions
What is the specification of the procedure?
How is this comma separated list used in the procedure?
wjadAuthor Commented:
The string v_list is passed to a procedure parameter which is a varchar2 patameter. In the procedure this parameter value is used as an IN clause value for a column, then based on this IN clause the data is returned back to cold fusion application using out ref cursor.
One obvious problem is that you're missing quotes around the value parameter

<cfprocparam dbvarname="p_list_right" cfsqltype="cf_sql_varchar"  value=#v_list# type="in">

          ^            ^   Add Quotes

So it should be

<cfprocparam dbvarname="p_list_right" cfsqltype="cf_sql_varchar"  value="#v_list#" type="in">

>  In the procedure this parameter value is used as an IN clause value for a column

what is the datatype of this column?   If its a non-numeric column then you need to keep the single quotes around each item in the list.    

If its numeric, you will have no problem without the quotes :  (45,578,123)

But when you change the delimiter using this statement

#ListChangeDelims(FORM.listRight,"','", ",")#>

All you're doing is taking the single quotes out of the middle.  What about the two on the end?

That statement would change..


to this..


You need to get rid of the two ' quotes on the ends to use it in the IN clause (if its numeric)

If its alpha, you need to leave all the quotes in place.

It can be solved in the procedure by using dynamic sql. But, I would suggest you to re-design the logic in a way such that you dont have to send this in list into the procedure and frame the sql using that. This re-design depends on your program flow.
Why redesign? : Programs written using dynamic sql are not scalable. Dynamic sql cannot utilize the parsed sql reusability features offered by the oracle optimizer.

See this example on dynamic sql using a comma separated in-list:

create or replace procedure test_proc(p_list IN varchar2, x_cur OUT sys_refcursor)
 open x_cur for
 'select empno from emp where ename in ('||p_list||')';

 l_ename_list varchar2(80);
 l_cur sys_refcursor;
 l_empno number;
 l_ename_list := '''SCOTT'' , ''KING''';

 test_proc(l_ename_list, l_cur);

  fetch l_cur into l_empno;
  exit when l_cur%notfound;
 end loop;

Notice the usage or two single quotes around each of the values in the IN list. This two-single quotes are required for varchar2 values in the IN list. If your values in the list are numbers, then your dont have to have these quotes at all.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now