Re: Q: variable number of pl/sql bind variable ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 02 Dec 1999 11:38:59 -0500
Message-ID: <b38d4s8i67c30pclbs89njrrm6liv8slmo@4ax.com>

A copy of this was sent to tedchyn_at_yahoo.com (if that email address didn't require changing) On Thu, 02 Dec 1999 16:10:53 GMT, you wrote:

>Sir:
>
>I have a plsql block with
> select xx from tab_a where xx in(:v1,:v2 and etc)
>
>the max number of bind variables can be 0 to 1000.
>
>One of possible solution is to use a function -
>where xx in(select * from a <function>). I am looking
>for an example of this sort.
>
>Thanks in advance
>Ted
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

One method is to use a "where x in ( select * from plsql_function )" instead of a large in-list. large in-lists use concatenation of individual index reads. If we use "where x in (query)" it tends to use nested loops and goes faster.

Additionally -- we can vary the amount of data returned by the plsql function from call to call -- if we have 6 elements, we return 6. if we have 256, we return 256. We do not have the limits of X elements in the in list, it is purely a function of the number of elements we return from the function.

Here is an example of using this 'trick':

tkyte_at_8i> create or replace type myTableType as table of number;   2 /

Type created.

tkyte_at_8i>
tkyte_at_8i> create or replace function getMyTableType return myTableType   2 as
  3 l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );   4 begin
  5 return l_x;
  6 end;
  7 /

Function created.

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> select a.column_value  val

  2 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   3 /

       VAL

---

         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

I would write a small package that has functions to clear the table, add to the table and return the table. I would then select from this pkg.in_memory_table.

This works in Oracle8.0 and up.

--
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 Thu Dec 02 1999 - 10:38:59 CST