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

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/05
Message-ID: <8eup08$1tt$1@nnrp1.deja.com>#1/1

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;

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.

Received on Fri May 05 2000 - 00:00:00 CDT