How to check value exist of an input param value as a list against a PL/SQL variable value?

Question

C# variable

string val_1 = 'A','B','C';

I pass this variable as a PL/SQL method parameter and check a value of another variable in the PL/SQL method as follows:

Procedure x(val_1 IN VARCHAR2)
IS
    val_2  VARCHAR2(1):= 'A';
BEGIN
    IF (val_2 IN (val_1)) THEN
        dbms_output.put_line('matched');
    END IF;
END;

This condition is not working. Any idea what I am doing wrong?

`val_1` looks like just a string containing commas and quotes. For `val_2` to match it, it would have to have the value `'A','B','C'`. You probably need to pass in a nested table array and use `if val_2 member of val_1`, or an associative array and loop through its values, or keep the string and match using a regex. (I'd make this an answer but I'm a bit short of time right now.) — William Robertson, Sep 01 '17 at 07:22
perhaps `if ','||val1||',' like '%,'||val2||',%' then ...`? Your initial string looks a little suspect, though - are you actually passing in `'A', 'B', 'C'`, or are you passing `A, B, C`? — Boneist, Sep 01 '17 at 07:29
Please answer my question regarding the exact string that you're passing in. — Boneist, Sep 01 '17 at 08:30
So, what is the exact string you are passing in? Does it have to be a single string, and not a more standard array or something? — William Robertson, Sep 01 '17 at 22:43

score 1 · Answer 1 · answered Sep 01 '17 at 09:44

var1 varchar2(1000) := 'a','b','c' - Incorect
var1 varchar2(1000) := q'~ 'a','b','c'~'; - correct
var1 varchar2(1000) := 'a,b,c';- correct

somthing in (varchar2_variable) is incorrect. There is no such construciton in oracle .;

Example simple solutions. But both solutions can cause problem in future. More flexible is version with regexp

declare 
 var1 varchar2(1000)  := q'~ 'a','b','c' ~';
 to_test varchar2(100) := 'a';
begin 
 if instr(var1,to_test) > 0 then 
   dbms_output.put_line('Matched');
 end if; 
end;  

declare 
 var1 varchar2(1000)  := q'~ 'a','b','c' ~';
 to_test varchar2(100) := 'a';
begin 
 if regexp_instr(var1,to_test) > 0 then 
   dbms_output.put_line('Matched');
 end if; 
end;

I think you haven't got my question properly. var_1 variable is a C# variable which passes during the PLSQL method call happens. — Dinidu Hewage, Sep 02 '17 at 07:47

score 0 · Answer 2 · answered Sep 02 '17 at 07:42

If you can pass in an array then this becomes much simpler.

Create an array type if you don't already have one:

create or replace type shortlist as table of varchar2(100);

Use that as the parameter instead of a plain string:

create or replace procedure x
    ( val_1 in shortlist )
is
    val_2 varchar2(1) := 'A';
begin
    if val_2 member of val_1 then
        dbms_output.put_line('matched');
    end if;
end;

Call the procedure:

begin
    x(shortlist('A','B','C'));
end;

or define the array in C# somehow (I don't know C#)

string[] names = new string[3] {"A", "B", "C"};

and call the procedure as described here: Passing array to Oracle procedure from c#

How to check value exist of an input param value as a list against a PL/SQL variable value?

2 Answers2