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.lis
And used it in my procedure call as follows:
<cfprocparam dbvarname="p_list_right" cfsqltype="cf_sql_varchar"
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.
Thanks!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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#"
^ ^ Add Quotes
So it should be
<cfprocparam dbvarname="p_list_right" cfsqltype="cf_sql_varchar"
> 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.lis
All you're doing is taking the single quotes out of the middle. What about the two on the end?
That statement would change..
'10','11','12','13'
to this..
'10,11,12,13'
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.
Well,
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)
as
begin
open x_cur for
'select empno from emp where ename in ('||p_list||')';
end;
/
declare
l_ename_list varchar2(80);
l_cur sys_refcursor;
l_empno number;
begin
l_ename_list := '''SCOTT'' , ''KING''';
test_proc(l_ename_list, l_cur);
loop
fetch l_cur into l_empno;
exit when l_cur%notfound;
dbms_output.put_line(l_emp
end loop;
end;
/
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.
Business Accounts
Answer for Membership
by: sujith80Posted on 2007-07-26 at 04:19:46ID: 19573352
What is the specification of the procedure?
How is this comma separated list used in the procedure?