Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Inserting a multi-line string into a table?

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

From: Gamma <gamma_at_clark.net>
Date: 2000/05/05
Message-ID: <_9DQ4.20987$0o4.207181@iad-read.news.verio.net>#1/1

Yikes! Surround the entire thing in a block?

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?

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.

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
Received on Fri May 05 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US