Re: Imbedded semicolon breaks insert statement -- why?

On 9 Jan 2001 10:59:50 -0500, 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>
With long strings, you might find SQL*Loader is more flexible than SQL*Plus.

John Received on Sat Jan 20 2001 - 16:56:41 CST