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?

In reply to DBI - Oracle 8 - Load many rows into a temp table w/o using SQL*Loader by joealba

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.