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

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.

> 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 <tkyte_at_us.oracle.com> wrote:
> >In article <ZzAQ4.20965$0o4.206271_at_iad-read.news.verio.net>,
> > gamma_at_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_at_clark.net
> >>
> >>
> >
> >
> >try a plsql block. its not run until it sees a slash:
> >
> >ops$tkyte_at_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_at_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_at_8i>
> >
> >
> >
> >--
> >Thomas Kyte tkyte_at_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_at_clark.net
>
>

--
Thomas Kyte                              tkyte_at_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.