http://www.perlmonks.org?node_id=141063

joealba has asked for the wisdom of the Perl Monks concerning the following question:

Background: I have a CGI script which performs a search of 5000+ text files (classified ads from a newspaper) and displays the results (20 at a time). The user can choose to sort the results alphabetically or by the first date of publication.

The pub date and first 50 text characters are stored in an indexed Oracle table for quick sorting. The optimal keyword search is performed by swish-e.

So when the search runs, swish-e gives me the list of files matching the search criteria. Then I ask Oracle to return the same list of files, sorted by those indexed columns. Finally, I take the sorted list of filenames and print the appropriate 20 results by reading in each flat file.

Problem: I can't say select filename from search_classifieds where filename in (list o' filenames) order by... in cases where swish-e returns more than 1000 filenames. So, I need to create a temporary table and get the filename list using an embedded query.

I suppose I could output a file and use SQL*Loader, but I was hoping that someone hooked that functionality into DBI somewhere. Node 106999 shows a MySQL solution, but that doesn't work in Oracle 8. This thread references an Oracle 8 feature called "direct load insert", but I can't dig up any more information on how to access it through DBI.

My question: Does anyone know how I can do a quick insert of 1000+ rows to a temporary table in Oracle 8 through DBI?