WHERE key IN (blah, blah, blah)
and the values in the IN clause were literals. The list of literals is limited by Oracle to 1000 items, so the code was breaking up the list into 1000 item chunks and invoking the query multiple times.
Unfortunately, you can't just replace the literal list of values with a bind variable; Oracle interprets the whole thing as one giant string and you're sunk. After doing some research online and looking it up in Tom Kyte's book, I came upon a solution. You do a little setup:
create or replace type myTableType as table of number;
create or replace function str2tbl( p_str in varchar2 ) return myTableType
l_str long default p_str || ',';
l_data myTableType := myTabletype();
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
And then your WHERE clause looks like this:
WHERE key IN (select * from table ( cast ( str2tbl(:id_list) as myTableType)))
where id_list is the same comma-separated list of ids as before.
This runs much faster, but there's a catch I don't like. The length of the value in $id_list is limited to 4000 characters. That means, in this case, I can only have 500 items in the list and have to invoke the query twice as often. It's also more difficult to split up the ids into chunks since it's no longer just about the number of ids. This solution helps, but it would be even better if I could get rid of this limitation.
I can't find anything that says that bind variables are limited to 4000 chars, and I don't see any reason why I'm running into this limit. A varchar2 in PL/SQL can hold up to 32K, so it's not a limit of the function. Could it be a limitation of our Oracle driver?
The exact error is
ORA-01460: unimplemented or unreasonable conversion requested
And it happens any time the string in the bind variable is 4001 characters or longer.
I have tried implementing this with a temporary table instead; that is, inserting all of the ids into a temporary table and then the where clause becomes
WHERE key IN (select id from temp_table)
But this takes nearly twice as long to run.
Any suggestions on how to get around the apparent 4000 char limit on a bind variable?