Re: Imbedded semicolon breaks insert statement -- why?

From: David Fitzjarrell <oratune_at_aol.com>
Date: Fri, 12 Jan 2001 16:40:36 GMT
Message-ID: <93nc22$jtd$1@nnrp1.deja.com>

In our last gripping episode roy_at_panix.com (Roy Smith) wrote:
> Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
> >This is only a problem in SQL*Plus. So if you want to use SQL*Plus to
> >insert a semicolon into a field, you'll need to escape. Check out
 this
> >example:
> >
> >SQL> create table foo (id number, msg varchar2(200));
> >
> >Table created.
> >
> >SQL> set escape \
> >SQL> show escape
> >escape "\" (hex 5c)
> >SQL> insert into foo values (1,'this is a long sentence\; with a
> >semicolon');
> >
> >1 row created.
>
> Yeah, but apparantly as long as the semicolon is not at the end of a
> line, sqlplus reads right past it without needing to escape it. It's
> only when it's at the end of a line that sqlplus gets upset.
>
> SQL> create table foo (text varchar2(100));
>
> Table created.
>
> SQL> insert into foo values ('this sentence is ok; the
 semicolon
> 2 is in the middle of the line');
>
> 1 row created.
>
> SQL> insert into foo values ('this is one is not;
> ERROR:
> ORA-01756: quoted string not properly terminated
>
> SQL> select from foo;
> select from foo
> *
> ERROR at line 1:
> ORA-00936: missing expression
>
> SQL> select * from foo;
>
> TEXT
> ----------------------------------------------------------------------

---

> this sentence is ok; the semicolon
> is in the middle of the line
>
> SQL>
>

If you must include a ';' in your text you should try to write the text on a single line. You need not break a line with newlines to get SQL*Plus to process it:

 SQL> insert into foo values ('this sentence is ok; the semicolon is in the middle of the line');

If you're breaking your insert lines for your own readability you should be able to increase the monitor resolution so that longer lines will remain intact. Keeping such insert text to a single line will help tremendously in 'correcting' this SQL*Plus 'error'.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/

Received on Fri Jan 12 2001 - 10:40:36 CST