IN with string Function

IN with string Function

Hi David,

Could anyone tell me what is going on here?
I can!

I have created a function to get a string from a configuration table
based upon the name of the entry.
Been there, done that!

Using dual, I can demonstrate the use of the function and the results.
SELECT get_config_parameter_func*('NON_OVATION_CABINET_TYPES')*
FROM DUAL*;***
'BCC','GCC2','LCC'

I then use it in the query:
...
SELECT 'SAFETY CABINET' drop_cabinet_ref_type*,*
FROM ic_cabinet
WHERE cabinet_type IN
(get_config_parameter_func('NON_OVATION_CABINET_TYPES'));**

But this returns no rows.
Correct. Your function returns a string. You need to return a table of
strings. The easiest way I know to do this, is to create PIPELINED
function to return each part of the string you want to use in an IN and
then use that in the above SELECT.

Looking at what you get in your current function, I'd say that you need
to split the string into three strings within the pipelined function
using the comma as a separator.

Something like the following will do what you want:

-- Your lengths may always be 4 or 5 or whatever, adjust to suit.
create type myInList as table of VARCHAR2(100);
/

create or replace function MyStringSplit(pString in varchar2, pDelimiter
in varchar2 := ',')
return myInList
PIPELINED
as
vDelimiter varchar2(1);
vString varchar2(32676);
vRow varchar2(100); -- Same as in the defined TYPE above.
vIndex number;
begin
-- NULL returns a single NULL row.
if (pString is NULL) then
pipe row(NULL);
return;
else
vString := pString;
end if;

-- If delimiter is NULL, use default anyway.
if (pDelimiter is NULL) then
vDelimiter := ',';
else
vDelimiter := pDelimiter;
end if;

-- Do we have a delimiter?
vIndex := instr(vString, vDelimiter);
if (vIndex = 0) then
-- NO, Pipe one row and exit.
pipe row (vString);
return;
end if;

-- We have a delimiter, so lets loop.
vString := vString || ',';
loop
vRow := trim(substr(vString, 1, vIndex-1));
pipe row (vRow);
if (vIndex <> length(vString)) then
vString := substr(vString, vIndex+1);
vIndex := instr(vString, vDelimiter);
else
return;
end if;
end loop;
return;
end;
/

In theory, the loop will end when the string ends!

So, using the above we do this:

select * from TABLE(myStringSplit('Norman,Dunbar');

COLUMN_VALUE

Norman
Dunbar

Or, using your example:

1* select * from table(mystringsplit('''BCC'',''GCC2'',''LCC'''));

COLUMN_VALUE

'BCC'
'GCC2'
'LCC'

You'll note I've had to double up on the quotes.

Now, the test, can it work?

with stuff as (
select 'A' as whatever from dual union all
select 'B' from dual union all
select 'C' from dual union all
select 'D' from dual union all
select 'E' from dual union all
select 'F' from dual union all
select 'G' from dual
) select whatever
from stuff
where whatever in (select * from table(myStringSplit('A,B,F,G')))

W

A
B
F
G

Ok, You will notice that I don't have surrounding quotes in my input
string - unlike yours. This is because we don't need them. Your query
will be something like:

SELECT 'SAFETY CABINET' drop_cabinet_ref_type,
FROM ic_cabinet
WHERE cabinet_type IN
(select * from
table(myStringSplit(get_config_parameter_func('NON_OVATION_CABINET_TYPES')));

However, your get_config_parameter_func needs to return the following:

'BCC,GCC2,LCC'

and not, as it currently does:

'BCC','GCC2','LCC'

Because the quotes are not required and they will cause an error due to
mismatched quotes etc. Plus, it won't work because the actual result
will be:

select * from table(myStringSplit('BCC','GCC2','LCC'))
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to
'MYSTRINGSPLIT'

HTH

Cheers,
Norm. [TeamT]

Message from: reedda

Norm: Thanks. I have implemented what you have provided with a few tweaks.
Learned some new functionality here and it works well. Dave


Historical Messages

Author: David Reed
Date: Wed Oct 06 12:27:42 PDT 2010
Norm: Thanks. I have implemented what you have provided with a few tweaks.
Learned some new functionality here and it works well. Dave
__

Author: Norman Dunbar
Date: Sat Oct 02 09:10:14 PDT 2010
Hi David, > Could anyone tell me what is going on here? I can! > I have created
a function to get a string from a configuration table > based upon the name of
the entry. Been there, done that! > Using dual, I can demonstrate the use of the
function and the results. > SELECT
get_config_parameter_func*(‘NON_OVATION_CABINET_TYPES’)* > FROM DUAL*;*** >
‘BCC’,‘GCC2’,‘LCC’ > > I then use it in the query: >… > SELECT ‘SAFETY
CABINET’ drop_cabinet_ref_type*,* > FROM ic_cabinet > WHERE cabinet_type
IN > (get_config_parameter_func(‘NON_OVATION_CABINET_TYPES’));** > But
this returns no rows. Correct. Your function returns a string. You need to
return a table of strings. The easiest way I know to do this, is to create
PIPELINED function to return each part of the string you want to use in an IN
and then use that in the above SELECT. Looking at what you get in your current
function, I’d say that you need to split the string into three strings within
the pipelined function using the comma as a separator. Something like the
following will do what you want: – Your lengths may always be 4 or 5 or
whatever, adjust to suit. create type myInList as table of VARCHAR2(100); /
create or replace function MyStringSplit(pString in varchar2, pDelimiter in
varchar2 := ‘,’) return myInList PIPELINED as vDelimiter varchar2(1); vString
varchar2(32676); vRow varchar2(100); – Same as in the defined TYPE above.
vIndex number; begin – NULL returns a single NULL row. if (pString is NULL)
then pipe row(NULL); return; else vString := pString; end if; – If delimiter is
NULL, use default anyway. if (pDelimiter is NULL) then vDelimiter := ‘,’; else
vDelimiter := pDelimiter; end if; – Do we have a delimiter? vIndex :=
instr(vString, vDelimiter); if (vIndex = 0) then – NO, Pipe one row and exit.
pipe row (vString); return; end if; – We have a delimiter, so lets loop.
vString := vString || ‘,’; loop vRow := trim(substr(vString, 1, vIndex-1)); pipe
row (vRow); if (vIndex length(vString)) then vString := substr(vString,
vIndex+1); vIndex := instr(vString, vDelimiter); else return; end if; end loop;
return; end; / In theory, the loop will end when the string ends! So, using the
above we do this: select * from TABLE(myStringSplit(‘Norman,Dunbar’);
COLUMN_VALUE ------------ Norman Dunbar Or, using your example: 1* select * from
table(mystringsplit(’’‘BCC’’,’‘GCC2’’,’‘LCC’’’)); COLUMN_VALUE ------------
‘BCC’ ‘GCC2’ ‘LCC’ You’ll note I’ve had to double up on the quotes. Now, the
test, can it work? with stuff as ( select ‘A’ as whatever from dual union all
select ‘B’ from dual union all select ‘C’ from dual union all select ‘D’ from
dual union all select ‘E’ from dual union all select ‘F’ from dual union all
select ‘G’ from dual ) select whatever from stuff where whatever in (select *
from table(myStringSplit(‘A,B,F,G’))) W - A B F G Ok, You will notice that I
don’t have surrounding quotes in my input string - unlike yours. This is because
we don’t need them. Your query will be something like: SELECT ‘SAFETY CABINET’
drop_cabinet_ref_type, FROM ic_cabinet WHERE cabinet_type IN (select * from
table(myStringSplit(get_config_parameter_func(‘NON_OVATION_CABINET_TYPES’)));
However, your get_config_parameter_func needs to return the following:
‘BCC,GCC2,LCC’ and not, as it currently does: ‘BCC’,‘GCC2’,‘LCC’ Because the
quotes are not required and they will cause an error due to mismatched quotes
etc. Plus, it won’t work because the actual result will be: select * from
table(myStringSplit(‘BCC’,‘GCC2’,‘LCC’)) * ERROR at line 1: ORA-06553: PLS-306:
wrong number or types of arguments in call to ‘MYSTRINGSPLIT’ HTH Cheers, Norm.
[TeamT]
__

Author: Guest
Date: Sat Oct 02 09:10:13 PDT 2010

__


Norm:

Thanks. I have implemented what you have provided with a few tweaks. Learned some new functionality here and it works well.

Dave

Morning Dave,

Norm: Thanks. I have implemented what you have provided with
a few tweaks. Learned some new functionality here and it
works well. Dave

Welcome. Better error trapping is good as well! :wink:

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk