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

From: yossarian <yossarian99_at_operamail.com>
Date: Tue, 02 Dec 2008 17:18:49 +0100
Message-ID: <49355fea$0$14502$5fc30a8@news.tiscali.it>


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:

  • 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

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. Received on Tue Dec 02 2008 - 10:18:49 CST

Original text of this message