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

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

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