Oracle procedure and commas in multiple select string

Posted on 2007-07-26
Last Modified: 2013-12-24
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.

Question by:wjad
    LVL 27

    Expert Comment

    What is the specification of the procedure?
    How is this comma separated list used in the procedure?

    Author Comment

    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.
    LVL 39

    Assisted Solution

    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.

    LVL 27

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
    Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
    This video shows how to recover a database from a user managed backup
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now