Multi value parameters 1

[xxing]

Hi

I have a stored procedure that has a multi-value parameter. The parameter called GetFruitNames will have a value such as

'kiwifruit,pear,orange'

In the select statement I have the folowing

select
fruitnames
from fruit
where
fruitnames in (GetFruitNames)

The GetFruitNames parameter has to be changed to
'kiwifruit','pear','orange'

does any know how to do this?

Thank you
Mark

 

[SantaMufasa]

I have a stored procedure that has a multi-value parameter...

I'm guessing, XXing, from the context of your example, which uses GetFruitNames, that instead of a stored procedure, you have a user-defined function (since you cannot refer to a stored procedure in a SELECT statement the way you have). Am I correct?


In any case, here are my re-creations of your context:

select * from fruit;

FRUITNAMES
----------
kiwifruit
lemon
pear
banana
orange
strawberry

select GetFruitNames from dual;

GETFRUITNAMES
---------------------
kiwifruit,pear,orange

...And here is a solution (I believe) to what you request:

select fruitnames from fruit
where GetFruitNames like '%'||fruitnames||'%';

FRUITNAMES
----------
kiwifruit
pear
orange

Let me know if I'm missing something.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[xxing]

Hi Dave

Thank you for replying.

I have a stored procedure that takes a parameter of a fruit name say Kiwifruit. A select statement will filter the records so only kiwifruit will appear.

I would like to change the stored procedure so it can take multi-values of say kiwifruit,pear,orange. These are seperated by commas and have quote marks at the ends -- i.e 'kiwifruit,pear,orange'. I would like to filter the results so that kiwifruit,pear and orange will appear.

I have a sql server background, but very new in oracle. In sql server I have a split table function that would put kiwifruit,pear and orange in a temporary table with three records. Then in the where clause I would have something like:

where fruitnames in (select item from split(@GetFruitNames,",")

I am unsure how to do something like this in oracle. Or perhaps oracle with its amazing amount of built in functions can do this in another manner?

Thanks
Mark

 

[SantaMufasa]

Mark,

Although Oracle doesn't have a built-in split function, you create a user-defined procedure that simulates the SQL Server split function. The SPLIT procedure, below, accepts any character string, with any delimiter. This split populates a temporary table (named "GTT_SPLIT") with a row for each delimited value from the incoming string. Multiple sessions can access this procedure (and the global temporary table) without contention:

create global temporary table GTT_SPLIT
      (String varchar2(2000));

Table created.

create or replace procedure SPLIT
      (String_in varchar2,delim varchar2)
is
    remainder                      varchar2(4000);
begin
    remainder := string_in;
    while length(remainder)>0 loop
        insert into GTT_SPLIT
               values (substr(remainder,1,instr(remainder||delim,delim)-1));
        remainder := substr(remainder,instr(remainder||delim,delim)+length(delim));
    end loop;
end;
/

Procedure created.

Here is the invocation of SPLIT that simulates your scenario:

exec split(GetFruitNames,',')

PL/SQL procedure successfully completed.

select * from gtt_split;

STRING
----------------
kiwifruit
pear
orange

3 rows selected.

Are we getting closer?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[KenCunningham]

Multivalues - ah, the joys of Pick (Prime Information variety)!

The internet - allowing those who don't know what they're talking about to have their say.

 

[xxing]

Thanks for your help SantaMufasa