Re: Imbedded semicolon breaks insert statement -- why?

From: Roy Smith <roy_at_panix.com>
Date: 9 Jan 2001 10:59:50 -0500
Message-ID: <93fchm$j00$1@panix3.panix.com>

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> Received on Tue Jan 09 2001 - 09:59:50 CST