Forum OpenACS Development: Re: Limitation on length of bind variables

Posted by Andrew Piskorski on
Dirk, I'm not sure I understand your Aiesec vs. Sharenet distinction.

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 inserts).

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:

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 =
I didn't try the "parse a string into a PL/SQL table" approach.