Re: Interesting jdbc problem regarding integer column qualifier and set

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 26 Dec 1999 09:15:03 -0500
Message-ID: <gi8c6s888jns8gis6gd1v8i09fmlnfr0ln@4ax.com>

A copy of this was sent to John Harrison <jharriso_at_slip.net> (if that email address didn't require changing) On Sat, 25 Dec 1999 22:18:14 -0800, you wrote:

>Hey All ...
>
>I've got an interesting problem (I think) concerning the coding of a
>jdbc query with an
>integer column qualifier against a set of values:
>
>select ... from table
>where integerColumn in (rangeOf Values)
>
>Basically, the column is integer, and the range of values most simply
>needs to be a string
>value (something like "12,133,10,0,154) ... and you receive a type
>conflict during execution.
>
>Can anyone think of a straight-forward approach for an jdbc solution to
>this?
>
>Thanks!

This really isn't a jdbc issue, it sounds to me like you want to bind a single comma delimited string of values and have the database intrepret it as a 'set' dynamically. Binding does not work that way -- you would never be able to search for things that had commas in them if it did...

You can use object types and a function to achieve this though. what we need to do is turn a string into a set and be able to query it. This shows how in 8.0 and up:

tkyte_at_8.0> create or replace type myTableType as table of number;   2 /

Type created.

tkyte_at_8.0> create or replace function test( p_str in varchar2 ) return myTableType
  2 as

  3      l_str   long default p_str || ',';
  4      l_n        number;
  5      l_data    myTableType := myTabletype();
  6  begin
  7      loop
  8          l_n := instr( l_str, ',' );
  9          exit when (nvl(l_n,0) = 0);
 10          l_data.extend;
 11          l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 12          l_str := substr( l_str, l_n+1 );
 13      end loop;
 14      return l_data;

 15 end;
 16 /

Function created.

tkyte_at_8.0> 
tkyte_at_8.0> variable some_string varchar2(25)
tkyte_at_8.0> exec :some_string := '1,5,'||uid

PL/SQL procedure successfully completed.

tkyte_at_8.0>
tkyte_at_8.0> select a.column_value val
  2 from THE ( select cast( test( :some_string ) as mytableType ) from dual ) a
  3 /

       VAL

---

         1
         5
     20149

tkyte_at_8.0>
tkyte_at_8.0> select * from all_users where user_id in   2 (
  3 select a.column_value val
  4 from THE ( select cast( test( :some_string ) as mytableType ) from dual ) a
  5 )
  6 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 20-AUG-99
TKYTE                               20149 10-NOV-99

tkyte_at_8.0>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Dec 26 1999 - 08:15:03 CST