Forum OpenACS Development: Re: Limitation on length of bind variables
AFAIK, if you already have, say, 2000 ids in an Oracle table somewhere
you can use "
insert into TABLE (select FOO from BAR)",
and if you already have the 2000 ids in PL/SQL you can use FORALL to
do a bulk bind and insert (rather than looping and doing 2000 separate
But if you have those 2000 literal ids outside of Oracle entirely, there are basically only two ways to get them in: Either do 2000 insert statements, or use the 2000 literal ids in one big honking query. There isn't any way to take 2000 literal values outside of Oracle and insert them all at once with a single SQL statement. If I'm wrong about that and there is, please let me know!
In the example where I tried using a temporary table above, I did do all 2000 inserts (from sqlplus) into the temporary table - that was the only way to get them in. (And actually, I didn't even use bind variables in the insert statements, although I could have!) For the case I tested, that (including all the inserts) ended up about twice as fast as sticking all the literal ids into a query of the general form:
I didn't try the "parse a string into a PL/SQL table" approach.select ... from table1, ( select 1 as id from dual union all select 2 as id from dual union all ... select 1500 as id from dual ) table2 where table1.id = table2.id