Re: oo4o binding values to "in" clause

"Jack" <never_at_inamillion1.com> wrote in message news:Xns93A693C2699D2neverinamillioncom_at_127.0.0.1...
> Hi Gods of Oracle,
>
> I'm in need of something like:
>
> select * from colourtable
> where colour in :somecolours
>
> Obviously :somecolours would be ('red','blue','green') in regular SQL but
> using oo4o (asp in particular (eww)) I imagine I need to create some kind
> of collection variable object first and pass that into the bind.
>
> Any one able to point me in the right direction? ASP or VB source would
be
> most welcome :-)

You are sort of on the right lines. You need to create a user defined type which is an array of varchars - this will be your inlist then you can parse your string into members of this array and finally you can use the new table functions as of 8i (I think) in the sql statement. The PL/SQL below works for me. I have only returned out parameters in this instance. I believe that the oo40 documentation has examples of how to return parameters to vb/asp.

HTH

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK

CREATE OR REPLACE Type DWPack_ListArray is table of varchar2(500);
/

create or replace package DWPack
as
Type rset is ref cursor;
Function ParseInList(i_list IN varchar2) return DWPack_ListArray;
Procedure ShowValuePercentage(i_valueList in varchar,i_percentageList in
varchar,o_val out number,o_perc out number,o_tot out number);
end DWPack;
/

show errors;

create or replace package body DWPack
AS
Function ParseInList(i_list IN varchar2) return DWPack_ListArray
is
l_string        long default i_list || ',';
l_data          DWPack_ListArray := DWPack_ListArray();
n               number;
begin
  loop
   exit when l_string is null;
   n := instr( l_string, ',' );
   l_data.extend;
   l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
   l_string := substr( l_string, n+1 );
  end loop;
 return l_data;
end ParseInList;


Procedure ShowValuePercentage(i_valueList in varchar,i_percentageList in
varchar,o_val out number,o_perc out number,o_tot out number)
is
t1_val number;
t2_val number;
begin
select sum(datavalue) into t1_val
from datavalue where dataset_id in
 (
  select * from table
  (
   select cast(ParseInList(i_valueList) as DWPack_ListArray) from dual
  )
 );

select sum(datavalue) into t2_val
from datavalue where dataset_id in
 (
  select * from table
  (
   select cast(ParseInList(i_percentageList) as DWPack_ListArray) from dual
  )
 );

o_perc:=t1_val;
o_tot:=t2_val;
o_val:=o_perc/o_tot*100;

end ShowValuePercentage;

end DWPack;
/