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

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?

>> After I posted, I was reading some old articles on escaping '&' in
>> strings when I ran across this trick:
>>
>> 'This' || chr(38) || 'that
>> ==> This&that
>>
>> So I experimented, and
>>
>> 'This string' || chr(10) || chr(10) || 'has a blank line.'
>>
>> seems to do what I want. Does this look okay to you?
>>
>
>if you want to convert all of you newlines into chr(10)'s, sure. But
>then i would say you are doing some heavy up front processing, not just
>glueing an "insert into t values ( '" in front and "');" onto the end.
>If you are doing so much up front processing -- i would suggest you do
>it right and do it in C or VB or some other language and use bind
>variables (if you care about performance).... Bind variables make the
>whole "newline", "single quote" and so on problems go away.

I don't think that doing this from C or VB using embedded SQL would be an option. The idea is to be able to generate files and/or scripts that are usable by anyone who has nothing but Oracle8i plus its tools (SQL*Plus, SQLLDR, etc.), but no access to a C compiler or any other program environment. So, I can't generate C/VB code+embedded SQL, for instance.

I'm not sure how bind variables would solve the single quote problem, assuming you're talking about using them inside SQL*Plus. Wouldn't you still have to double them? (Not that they're a big deal either way...)

I appreciate your help so far...

--

Paul Brinkley
gamma_at_clark.net