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

Collapse
Posted by Dirk Gomez on
Don, we need a smart portlet for an example then :)

Andy, I should have been more correct. The hack on aiesec was like this: for every item of the in list, there was an insert into the temporary table. Hundreds of little inserts vs. one reparse - guess what won. (In a loop - gosh)

On ShareNet the code was smarter: there we used temporary tables very successfully. They are generally handy and a great tool, I agree.

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