INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Multi value parameter

Multi value parameter

(OP)
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)
...

RE: Multi value parameter

Crystal,

Here are some data:

CODE

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:

CODE

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: set Variable value) and their results:

CODE

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.


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

RE: Multi value parameter

(OP)
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'.

RE: Multi value parameter

Crystal,

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

CODE

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.

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

RE: Multi value parameter

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close