Re: Pl/SQL: Using Variable With IN Operator

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/07
Message-ID: <8hmk3d$obr$1@nnrp1.deja.com>

In article <8hmgs9$m0q$1_at_nnrp1.deja.com>,   Tim Humphrey <zzhumphreyt_at_my-deja.com> wrote:
> In PL/SQL is it possible to have the following statement:
>
> CURSOR some_cursor IS
> SELECT
> *
> FROM
> some_table
> WHERE
> some_field IN a_variable;
>
> If so how do I declare the variable and assign values to it? I've
> experimented with records and nested tables with no luck. I'd rather
> not individually store the elements in separate variables if I don't
> have to. I'm on Oracle 7.3.3.4 and PL/SQL 2.3.3.4.
>
> --
> ..._Tim_...
> --=[It has just been discovered that research causes cancer in rats.]
 =--
> http://www.birdnest.org/zzhumphreyt
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

In Oracle7, this is harder then in v8 (but we can do it).

You cannot bind in a
SINGLE value and expect it to be treated as MANY values (think about how hard it would be to find something in an IN LIST that had a "comma" in it for example).

In Oracle8.0 and up, this is easy with object types and nested tables. Just to see what that will look like, I'll show that - then I'll show the v7 implementation.

ops$tkyte_at_8i> create or replace type myTableType as table of number;
  2 /
Type created.

ops$tkyte_at_8i> create or replace function in_list( p_string in varchar2 )
return myTableType
  2 as

  3      l_data             myTableType := myTableType();
  4          l_string       long default p_string || ',';
  5          l_n            number;

  6 begin
  7

  8          loop
  9                  exit when l_string is null;
 10                  l_data.extend;
 11                  l_n := instr( l_string, ',' );
 12                  l_data( l_data.count ) :=
                          substr( l_string, 1, l_n-1 );
 13                  l_string := substr( l_string, l_n+1 );
 14          end loop;
 15          return l_data;

 16 end;
 17 /

Function created.

So, in Oracle8 and up we can "select * from PLSQL_FUNCTION" when the function returns a SQL Table type as follows:

ops$tkyte_at_8i>
ops$tkyte_at_8i> select *
  2 from THE ( select cast( in_list('1,2,3,5,12') as

            mytableType ) from dual ) a
  3 /

COLUMN_VALUE

---

           1
           2
           3
           5
          12

Which means we can do that in a subquery:

ops$tkyte_at_8i> select *
  2 from all_users
  3 where user_id in ( select *

  4                      from THE ( select cast( in_list
                                            ('1,2,3,5,12') as
                                   mytableType ) from dual ) a )

  5 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 20-APR-99

In v7, we can do something very similar (works in 8.0 and up as well) with plsql tables. Its a little more work but not too much. It would look like this:

ops$tkyte_at_8i> create or replace package my_pkg   2 as
  3 function in_list( p_string in varchar2, p_idx in number )

                         return varchar2;
  4          pragma restrict_references( in_list, wnds, rnds,
                                                  wnps, rnps );
  5
  6          function get_list_count(p_string in varchar2)
                         return number;
  7          pragma restrict_references( get_list_count, wnds,
                                    rnds, wnps, rnps );
  8
  9          pragma restrict_references( my_pkg, wnds, rnds,
                                          wnps, rnps );

 10 end;
 11 /

Package created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package body my_pkg   2 as
  3 function in_list( p_string in varchar2, p_idx in number )

     return varchar2
  4 is

  5          l_start number;
  6          l_stop  number;
  7  begin
  8          if ( p_idx = 1 ) then
  9            l_start := 1;
 10          else
 11            l_start := instr( p_string, ',', 1, p_idx-1 )+1;
 12          end if;
 13
 14          l_stop := instr( p_string ||',', ',', 1, p_idx );
 15
 16          return ltrim( rtrim( substr( p_string, l_start,
                                          l_stop-l_start ) ) );

 17 end;
 18
 19 function get_list_count( p_string in varchar2 )

     return number
 20 is
 21 l_cnt number default 0;
 22 begin

 23     for i in 1 .. 1000 loop
 24         exit when nvl( instr( p_string, ',', 1, i ), 0 ) =
0;
 25         l_cnt := i+1;
 26     end loop;
 27
 28     return l_cnt;

 29 end;
 30
 31 end;
 32 /

Package body created.

So, now I can code a query like:

ops$tkyte_at_8i> variable bindVar varchar2(255)
ops$tkyte_at_8i>
ops$tkyte_at_8i> exec :bindVar := '1, 3, 44, 1000'

PL/SQL procedure successfully completed.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select my_pkg.in_list( :bindVar, rownum )   2 from all_objects
  3 where rownum <= ( select my_pkg.get_list_count( :bindVar ) from dual )
  4 /

MY_PKG.IN_LIST(:BINDVAR,ROWNUM)

---

---

---

1
3
44
1000

All we need for that to work is a table with MORE rows then we have IN LIST
items -- all_objects is generally a good candidate. It works by using
rownum as an "index" into the plsql table.

So, now we can simply:

ops$tkyte_at_8i> select *
  2 from all_users
  3 where user_id in ( select my_pkg.in_list( :bindVar, rownum )

  4                       from all_objects
  5                      where rownum <= ( select
y_pkg.get_list_count( :bindVar ) from dual )
  6                                    )

  7 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
WEB$AZEILMAN                         1000 24-JUN-99

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Jun 07 2000 - 00:00:00 CDT