From: gamma@clark.net (Gamma) Subject: Re: Inserting a multi-line string into a table? Date: 2000/05/05 Message-ID: <3RFQ4.21010$0o4.208246@iad-read.news.verio.net>#1/1 References: <8eup08$1tt$1@nnrp1.deja.com> <_9DQ4.20987$0o4.207181@iad-read.news.verio.net> <8ev7c9$jbv$1@nnrp1.deja.com> X-Complaints-To: abuse@verio.net X-Trace: iad-read.news.verio.net 957557183 168.143.0.8 (Fri, 05 May 2000 20:06:23 GMT) Organization: Clark Internet Services, Inc., Ellicott City, MD USA NNTP-Posting-Date: Fri, 05 May 2000 20:06:23 GMT Newsgroups: comp.databases.oracle.misc At 07:28 PM 5/5/00 +0000, Thomas J. Kyte wrote: In article <_9DQ4.20987$0o4.207181@iad-read.news.verio.net>, gamma@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@clark.net