Skip to Main Content
  • Questions
  • binding "where <value> in :<value>" as an in-list


Question and Answer

Tom Kyte

Thanks for the question, Mark .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: November 15, 2009 - 2:43 pm UTC


Viewed 10K+ times! This question is

You Asked

Hi Tom,
I am using Pro*C and I need to send an array of strings
in a "where <value> in :<value>" is this possible?
And if not what should I do?

and Tom said...

In Oracle7, this is rather difficult. 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@8i> create or replace type myTableType as table of number;
2 /
Type created.

ops$tkyte@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
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@8i> select *
2 from THE ( select cast( in_list('1,2,3,5,12') as
mytableType ) from dual ) a
3 /


Which means we can do that in a subquery:

ops$tkyte@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 /

------------------------------ ---------- ---------
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@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 );
6 function get_list_count(p_string in varchar2)
return number;
7 pragma restrict_references( get_list_count, wnds,
rnds, wnps, rnps );
9 pragma restrict_references( my_pkg, wnds, rnds,
wnps, rnps );
10 end;
11 /

Package created.

ops$tkyte@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;
14 l_stop := instr( p_string ||',', ',', 1, p_idx );
16 return ltrim( rtrim( substr( p_string, l_start,
l_stop-l_start ) ) );
17 end;
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;
28 return l_cnt;
29 end;
31 end;
32 /

Package body created.

So, now I can code a query like:

ops$tkyte@8i> variable bindVar varchar2(255)
ops$tkyte@8i> exec :bindVar := '1, 3, 44, 1000'

PL/SQL procedure successfully completed.

ops$tkyte@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 /


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@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 /

------------------------------ ---------- ---------


  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Parameter receive Multiple values

Chandresh, March 16, 2001 - 10:50 am UTC


The solution provided is excellent. The only think i was thinking is to use DBMS_SQL. But the solution you provided help by using simple SQL and PL/SQL.

Highly appreciated for your help.


A reader, July 19, 2002 - 10:43 am UTC


I'm using Oracle v734. I generate a large number of values
based on a cursor and set the block property in forms dynamically. So for each execution it will generate a different set of values. While using your function
I will have to set set_block_property('block name',
default_where, 'key in
( select my_pkg.in_list( :bindVar, rownum )
from all_objects
where rownum <= ( select
y_pkg.get_list_count( :bindVar ) from dual )

The number elements in the in list could be quite large( sometimes more than the count of all_objects ).

Earlier I thought just dynamically creating the list
and assigning to the block. But since this would increase the parse cost I abandoned that idea.

Please let me know whether this is a good approach and any other alternative.

Tom Kyte
July 19, 2002 - 11:08 am UTC

If you are generating an inlist that exceeds the number of rows in all_objects -- well, I think something is wrong right there.

Anyway, if you exceed the number of rows in all objects -- that'll not work (you'll not get some of them in the subquery).

also, in 7.3 you'll be limited to a string of 2000 bytes in the sql query (:bindVar will be 2k at most). If you are exceeding the number of rows in all objects, you've blown that limit for sure.

In any case -- with such a huge set -- can't you just plug the QUERY that gets those values into the subquery itself???? I cannot imagine a user would be picking thousands of elements from a list.

A reader, July 19, 2002 - 1:18 pm UTC


I am forgoing the option to have the elemenents in the list
greater than the count of all_objects if so I have 2 options since I'm operating in 734.

1. Create a temporary table and populate it on
forms runtime.Plug in the temporary table as sub query.
and each time I execute delete the table.

2. Use package option from ur site.

Please tell how you rate both the options.

Tom Kyte
July 19, 2002 - 3:30 pm UTC

what about 3) use a subquery against data in an existing table.

well, in any case -- I would do this. benchmark it and see which performs best for you. They both have their ups and downs.

A reader, July 22, 2002 - 9:43 am UTC

You mentioned there are ups and downs in using the following methods

1. Temporary Table
1.Delete before insertion.
2.Insert records

2. Use a Package to Return the subquery IN values

Could you give some of the ups and downs in both approach. I greatly determince which I would want to follow.


Tom Kyte
July 22, 2002 - 10:05 am UTC

in order to populate the temporary table -- you need to run yet more sql.
You'll be in a transaction.
deleting from a temporary table will generate gobs of undo and hence redo.

using plsql is cpu intensive
it'll take memory
you need to write more code

things like that. again -- benchmark it in your environment to see which works best (and remember that neither is 100% superior or inferior to the other)

Replacing in-list with nested table

Mark D. Fortenbery, January 30, 2003 - 7:35 pm UTC

We had a similar situation and we created a nested table to use instead of an in-list.

Since the in-list varied in length, this significantly reduced hard parses.

But some of our queries were joins over three or more tables and in these cases the execution plan often had Full Table Scans over 1 or more of these tables.

We tried using an in-clause as a subselect from the nested table as well as joining on an in-line view over the nested table.

We have computed statistics and set optimizer_index_caching to 90 and optimizer_index_cost_adj to 30.

We replaced the nested table with a temp table and this has improved things quite a bit.

Suggestion/Question regarding may values in INLIST ..

Lasse Jenssen, November 13, 2009 - 6:26 am UTC

Hi Tom

I've run into the same type of question. 
One of the developers on my team told me the following:
They are parsing a file and have to check if uniquekey is present in a table (her: my_table).
They are using a cursor (see below) and a fixed number of bind variables in the IN-list (to overcome limits on sql length and IN-list count).

select uniquekey from my_table where uniquekey in (:b1, :b2, ...., :b200);

Then they run this several times depending of how many values to be checked.

I've read through several reviews on your (excellent page), but did not really find a solution.
You suggest getting the IN-list by running a subquery. In our case the data is not in the database but read from a file.

I found an article on google that suggested the following:


Java code:
/* PARSING ************************************************** */
ArrayDescriptor oracleCollection = 

PreparedStatement stmt = conn.prepareStatement(
                 " SELECT uniquekey FROM <table>                                "
                +"  WHERE uniquekey IN (                                          "
                +"   SELECT * FROM   TABLE( CAST ( ? as NUMBER_LIST_TYPE ) )  "
                +"   )                                                        "

/* EXECUTION 1 ********************************************** */

System.out.println("1st execution:");

/* define our java array */
int[] javaArray1 = { 7369,7566,7782 };

 /* define our oracle array */
ARRAY jdbcArray1 = new ARRAY (oracleCollection, conn, javaArray1);

/* bind that array to our statement bind variable */

/* execute the query and browse the result */
ResultSet r=stmt.executeQuery(); 

Would this still get the ORA-01795?
Would this be an appropriate way of solving this issue?
(Of cource - we will do some benchmarking to test the existing approach and this other solution)</code>
Tom Kyte
November 15, 2009 - 2:43 pm UTC

... They are parsing a file and have to check if uniquekey is present in a table
(her: my_table)....

why, tell me the entire problem you are trying to solve, not a small trivial (and probably wrong) approach to it they took.

when someone looks to see if a row exists, that always throws up a HUGE red flag for me. HUGE. it almost NEVER should be done in the first place.

Ora 01460: unimplemented or unreasonable conversion requested on large strings

MTs, March 01, 2010 - 8:55 am UTC

Hello Tom.
I've found your article very useful.
My problem is that I want to pass a large string (more than 4K).
I changed the ’InList’ function to work with clobs.
The new function is:

create or replace
function in_list( p_string in clob )
return myTableType
l_array myTableType:= myTableType ();
l_string clob default p_string || ',';
comma_index number;
exit when l_string is null;
comma_index := dbms_lob.instr(l_string, ',' );
l_array( l_array.count ) :=
dbms_lob.substr( l_string, comma_index-1 , 1);
l_string := dbms_lob.substr( l_string, dbms_lob.getlength(l_string), comma_index+1 );
end loop;
return l_array;

The problem occurs when I’m executing this query:
select * from THE ( select cast( in_list(:ids) as myTableType) from dual)
The “ids” parameter has string larger than 4k chars.
The exception is: ORA-01460: unimplemented or unreasonable conversion requested.

How can I resolve this?

Thanks, MTs

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library