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

On Dec 2, 10:18 am, yossarian <yossaria..._at_operamail.com> wrote:
> 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.

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

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%!

 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
-------------------- ------------------------------
------------------------------

   DELETED TEMPLATEID
---------- ----------

Public               Target Crossed!                Target Crossed!
Fantastic Sales
	Spree!

FOLDERNAME           TEMPLATENAME                   SUBJECT
-------------------- ------------------------------
------------------------------

   DELETED TEMPLATEID
---------- ----------

	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%.

FOLDERNAME           TEMPLATENAME                   SUBJECT
-------------------- ------------------------------
------------------------------

   DELETED TEMPLATEID
---------- ----------

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


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

FOLDERNAME           TEMPLATENAME                   SUBJECT
-------------------- ------------------------------
------------------------------

   DELETED TEMPLATEID
---------- ----------

	C you all there guys!
         0          8

SQL> David Fitzjarrell Received on Tue Dec 02 2008 - 10:41:31 CST