Re: Imbedded semicolon breaks insert statement -- why?

Roy,

> 1) Is this really an error, or is sqlplus broken?

It is an "error". SQL*Plus is not broken. It is working the way that it was designed. The semicolon is a statement terminator. It tells SQL*Plus "here is the end of my command". If you do not want to signify the end of your SQL statement, then you need to 'escape' the semicolon in your string. See below.  

> 2) Regardless of the answer to the above, what's the best way around the
> problem? The sql files in question will be machine generated, so it has to
> be something that's easy to generate mechanically.

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.

SQL> commit;

Commit complete.

SQL> select * from foo;

        ID

         1
this is a long sentence; with a semicolon

Did you see how I put the escape character (which I defined as '\') right before the semicolon?

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================