Multi value parameter

[CrystalProgDev]

I have a function with a parameter. I need to pass multiple values to single parameter.
example parameter value 'A,B,C' in sql it has to be IN ('A','B','C').

Can any one please provide me the solution

FUNCTION MTO_ENG104_PIPELINED (str VARCHAR2)
RETURN WBS_TAB
PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR WBS_CUR_B
IS
SELECT * from table_nn where strval in (str)
...

 

[SantaMufasa]

Crystal,

Here are some data:

select last_name from s_emp order by 1;

LAST_NAME
-----------------
Biri
Catchpole
Chang
Dancs
Dumas
Giljum
Havel
Maduro
Magee
Markarian
Menchu
Nagayama
Newman
Ngao
Nguyen
Nozaki
Patel
Patel
Quick-To-See
Ropeburn
Schwartz
Sedeghi
Smith
Urguhart
Velasquez

25 rows selected.

Here is a function that returns the count of rows where the LAST_NAME matches one or more of the last names appearing in the single incoming string parameter, STR:

create or replace function sampler (str varchar2) return number is
    hold_count number;
begin
    select count(*) into hold_count from s_emp where instr(str,last_name) > 0;
    return hold_count;
end;
/

Function created.

Here are multiple invocations of the SAMPLER function (using Oracle's "q operator" described in your thread thread1662-1719267) and their results:

select sampler(q'~'Velasquez','Biri','Patel'~') How_many from dual;

  HOW_MANY
----------
         4

1 row selected.

(Notice that there are two 'Patel' rows in the original data.

select sampler(q'~'Patel'~') How_many from dual;

  HOW_MANY
----------
         2

1 row selected.

Let us know if this resolves your need.




Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[CrystalProgDev]

I didn't get any thing. If user selects a,b,c. when I pass the it as a parameter it will be some thing like this 'a,b,c'. but in sql it has to be ('a','b','c'). I am not sure how to convert 'a,b,c' to 'a','b','c'.

 

[SantaMufasa]

Crystal,

I don't think you used the "instr" methodology in my code (in the sample function I posted):

select count(*) into hold_count from s_emp where instr(str,last_name) > 0;

...because the "instr" method would work regardless of whether the string contained 'a,b,c' or 'a','b','c'. It would be helpful if you posted your code that didn't work so we could help isolate the problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ChrisHunt]

But what if you do this:

select sampler(q'~'Smithson'~') How_many from dual;

Won't it come back with a count of 1?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[taupirho]

So, you can do something like this

1* select x from tom
SQL> /

X
--
A
B
C
T


with data as (
select
trim( substr (txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1)
- instr (txt, ',', 1, level) -1 ) )
as token
from (select ','||'A,B,T'||',' txt
from dual)
connect by level <=
length('A,B,T')-length(replace('A,B,T',',',''))+1
)
select x from tom
where x in (select token from data)
/
SQL>

X
--
A
B
T



In order to understand recursion, you must first understand recursion.