From: Thomas J. Kyte Subject: Re: Inserting a multi-line string into a table? Date: 2000/05/05 Message-ID: <8ev7c9$jbv$1@nnrp1.deja.com>#1/1 References: <8eup08$1tt$1@nnrp1.deja.com> <_9DQ4.20987$0o4.207181@iad-read.news.verio.net> To: gamma@clark.net X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0) X-Http-Proxy: 1.0 x23.deja.com:80 (Squid/1.1.22) for client 205.227.43.12 Organization: Deja.com - Before you buy. X-Article-Creation-Date: Fri May 05 19:28:28 2000 GMT X-MyDeja-Info: XMYDJUIDtkyte Newsgroups: comp.databases.oracle.misc 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. > 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'm kinda concerned that this is a Unix-centric newline; it's a > ^J, as opposed to a ^M or ^M^J. I would have considered ^M to be > the most "standard", but when I try chr(13), the above string > prints out little boxes for the ^Ms in SQL*PlusW. > line feed (chr(10)) almost always works (notepad isn't good with it, write is, word is, vi is)... > Anyway, thanks again for the reply. > > Thomas J. Kyte wrote: > >In article , > > gamma@clark.net (Gamma) wrote: > >> I have a test table: > >> > >> create table t (s VARCHAR2(2000)); > >> > >> In SQL*Plus, it's proving impossible (SFAIK) to insert passages of > >> text into t that contain blank lines. SQL*Plus stores the buffer > >> and blips back to the command line when I hit -Enter- twice. I > >> could probably eventually get this string in via some fancy editing, > >> except that human interaction with SQL*Plus will not always be an > >> option (I'd like to do this via @foo.sql). I've scoured the online > >> docs and two reference manuals, with no luck. > >> > >> Oh, and just in case anyone is wondering, the string could conceivably > >> contain an ASCII picture which requires blank lines. (I.e., this > >> isn't straight text where I could just take out all of the newlines.) > >> > >> Does anyone know of a way to embed newlines in a string that will > >> solve this problem? > >> > >> Thanks for any help. > >> > >> -- > >> > >> Paul Brinkley > >> gamma@clark.net > >> > >> > > > > > >try a plsql block. its not run until it sees a slash: > > > >ops$tkyte@8i> declare > > 2 x varchar2(2000) default 'this is a string > > 3 with > > 4 > > 5 a blank line in it > > 6 that > > 7 > > 8 I want to put into > > 9 > > 10 a table'; > > 11 begin > > 12 insert into t values ( x ); > > 13 end; > > 14 / > > > >PL/SQL procedure successfully completed. > > > >ops$tkyte@8i> select * from t; > > > >A > >--------------------------------------------------------------------- --- > >---------------------------- > >this is a string > >with > > > >a blank line in it > >that > > > >I want to put into > > > >a table > > > > > >ops$tkyte@8i> > > > > > > > >-- > >Thomas Kyte tkyte@us.oracle.com > >Oracle Service Industries > >http://osi.oracle.com/~tkyte/index.html > >-- > >Opinions are mine and do not necessarily reflect those of Oracle Corp > > > > > >Sent via Deja.com http://www.deja.com/ > >Before you buy. > > -- > > Paul Brinkley > gamma@clark.net > > -- Thomas Kyte tkyte@us.oracle.com Oracle Service Industries http://osi.oracle.com/~tkyte/index.html -- Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.