Re: Inserting a multi-line string into a table?

In article <3RFQ4.21010$0o4.208246_at_iad-read.news.verio.net>,   gamma_at_clark.net (Gamma) wrote:
> At 07:28 PM 5/5/00 +0000, Thomas J. Kyte wrote:
> In article <_9DQ4.20987$0o4.207181_at_iad-read.news.verio.net>,
> gamma_at_clark.net (Gamma) wrote:
> > Yikes! Surround the entire thing in a block?
> >
>
> yikes? why yikes? your not using bind variables anyway, compiling a
> snippet of plsql isn't going to be much worse then compiling an
 insert.
>
> no yikes about it.
>
> I guess it's "yikes" because I didn't tell you the whole story. :-)
> This insert statement is one of about 500. They're generated by some
> Java code. I cringed at the thought of changing the code to surround
> each and every insert statement with a PL/SQL block.
>
> I suppose there are a handful of reasons why I shouldn't worry. One,
> I indeed was worried about the performance of that block vs. an
> insert. Two, only about five of those 500 inserts had a multi-line
> value; I could get away with just fixing those five. Three, I
> really, really ought to be doing this in some other way anyway; this
> is just a temporary kludge until I'm able to better understand the
> problem.
>
> Speaking of which, what WOULD be the best way to do this, I wonder...
> This generated load is 500 rows now, but that could easily be much
> bigger. I'm betting I could generate the necessary files to make
> this work using SQLLDR; would this be the appropriate tool?
>

If I was pre-processing these in Java already, I would simply use JDBC and a prepared statment and insert them directly instead of writing a SQLPlus script and then running sqlplus after the fact.

You could generate SQLLDR scripts but with text with embedded newlines - - its hard.

With 500 rows (or 1,000 or more) JDBC with an appropriate batch size will perform just fine (faster, by far then SQLPlus with these unique sql statements)...

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat May 06 2000 - 00:00:00 CDT