Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

how to insert multiline values with empty lines in a VARCHAR2 column using SQL*Plus

595 views

Skip to first unread message

yossarian

unread,

Dec 2, 2008, 11:18:49 AM12/2/08

Maybe a trivial question...

I am able to insert multiline values in a VARCHAR2 column using SQL*Plus:

--- file x.sql ---
Insert into EMAILTEMPLATES
(FOLDERNAME,TEMPLATENAME,SUBJECT,DESCRIPTION,BODY,DELETED,TEMPLATEID)
values ('Public','Target Crossed!','Target Crossed!','Fantastic Sales
Spree!','Congratulations!
The numbers are in and I am proud to inform you that our
total sales for the previous quarter
amounts to $100,000,00.00!. This is the first time
we have exceeded the target by almost 30%.
We have also beat the previous quarter record by a
whopping 75%!
Let us meet at Smoking Joe for a drink in the evening!
C you all there guys!',0,8);
--- ---

hr@> @x

1 row created.

The problems start when the value include empty lines:

The numbers are in and I am proud to inform you that our
total sales for the previous quarter
amounts to $100,000,00.00!. This is the first time
we have exceeded the target by almost 30%.
We have also beat the previous quarter record by a
whopping 75%!

Let us meet at Smoking Joe for a drink in the evening!
C you all there guys!',0,8);
--- ---

hr@> @x
SP2-0734: unknown command beginning "The number..." - rest of line ignored.
SP2-0734: unknown command beginning "total sale..." - rest of line ignored.
SP2-0734: unknown command beginning "amounts to..." - rest of line ignored.
SP2-0734: unknown command beginning "we have ex..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "We have al..." - rest of line ignored.
SP2-0734: unknown command beginning "whopping 7..." - rest of line ignored.
SP2-0734: unknown command beginning "Let us mee..." - rest of line ignored.
SP2-0023: String not found.

How can I manage this situation?

Target databas is Oracle Database 10g Express Edition Release 10.2.0.1.0.

Thank you.

Kind regards, Y.

ddf

unread,

Dec 2, 2008, 11:41:31 AM12/2/08

Read the documentation; setting sqlblanklines to ON solves your
'problem':

SQL> set sqlblanklines on
SQL> create table emailtemplates(
2 foldername varchar2(20),
3 templatename varchar2(30),
4 subject varchar2(30),
5 description varchar2(50),
6 body varchar2(4000),
7 deleted number,
8 templateid number
9 );

Table created.

SQL>
SQL> Insert into EMAILTEMPLATES
2
(FOLDERNAME,TEMPLATENAME,SUBJECT,DESCRIPTION,BODY,DELETED,TEMPLATEID)
3 values ('Public','Target Crossed!','Target Crossed!','Fantastic
Sales
4 Spree!','Congratulations!
5
6
7 The numbers are in and I am proud to inform you that our
8 total sales for the previous quarter
9 amounts to $100,000,00.00!. This is the first time
10 we have exceeded the target by almost 30%.
11 We have also beat the previous quarter record by a
12 whopping 75%!
13
14
15 Let us meet at Smoking Joe for a drink in the evening!
16 C you all there guys!',0,8);

1 row created.

SQL>
SQL>
SQL> select * From emailtemplates;

FOLDERNAME TEMPLATENAME SUBJECT
-------------------- ------------------------------
------------------------------
DESCRIPTION
--------------------------------------------------
BODY
------------------------------------------------------------------------------------------------------------------------------------
DELETED TEMPLATEID
---------- ----------
Public Target Crossed! Target Crossed!
Fantastic Sales
Spree!
Congratulations!

The numbers are in and I am proud to inform you that our
total sales for the previous quarter
amounts to $100,000,00.00!. This is the first time
we have exceeded the target by almost 30%.

We have also beat the previous quarter record by a
whopping 75%!

Let us meet at Smoking Joe for a drink in the evening!

C you all there guys!

0 8

SQL>

David Fitzjarrell

0 new messages