posted 21 years ago
Hi Experts,
We have a procedure with 3 in parameter. The parameter p3 has got multipule value, we select the values from application,
create procedure proc_A(p1 IN integer,p2 IN integer,p3 IN varchar)
declare
a integer;
b varchar2(4000);
begin
b := replace('a|b|c|d',|',''', '''); -- where p3 is 'a|b|c|d'
b := chr(39) || b || chr(39); -- where b will be having value of p3 in formated mulitple entry ('a', 'b', 'c', 'd')
insert into taba(c1,c2,c3) select a,c,x from tabb where tabb.x in ( b) -- since b will be ('a', 'b', 'c', 'd') like list of values, this is the place where we find difficulty.
end;
/
The table tabb has values:
select x from tabb;
a
b
c
d
e
f
Our problem is that when i execute the procedure it doesn't insert any records into the table taba when you pass p3 parameter as 'a|b|c|d'.
Instead of passing the parameter p3 as formated b, if i do hard code value in the place of insert query in the procedure, it executes perfectly, but requirement is such that, the user select the list of values where we cannot define the values what he/she going to select.
insert into taba(c1,c2,c3) select a,c from tabb where tabb.x in ( 'a', 'b', 'c', 'd' )
we pass parameter from application to this procedure and the parameter p3 is multipe selection in application
execute proc_A(5,1,'a|b|c|d')
it executes successfully without any insert. We found out the difficulties but unable to solve the same.
the difficulty is that in insert statement it takes the full p3 parameter value as single value, instead of considering as list of values.
Do require your suggestion to solve this issue
SPECIALIST-WCSV5.1 Implementation.<br />SYSTEMS EXPERT-WAS4.0 Administration
