Imbedded semicolon breaks insert statement -- why?

[Roy Smith]

If I do something like:

create table foo (
id integer,
text varchar2(2000)
);

insert into foo values (
1, 'this is a very long string
which wraps around several lines;
in fact, three of them');

I get a syntax error when I feed it to sqlplus. Apparantly, what's going on
is the ";" imbedded in the text field makes sqlplus think the statement is
terminated. I would have thought the quotes would protect it. According to
the Oracle SQL Reference, the only character which needs to be escaped inside
a quoted string is a quote.

Two questions:

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

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.

[Brian Peasland]

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
----------
MSG
----------------------------------------------------------------------------------------------------
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!
========================================

[Roy Smith]

Brian Peasland <peas...@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>

[Peter Dickmann]

In principle SQL*Plus is line-oriented. There might exist better solutions,
however, if you could survive with eliminating the newline's, you could add
a hyphen (-) at the end of each line; this would result in a single line,
since the hyphen is the continuating character. Or replace all smicolons by
a special character and vice versa when selecting. Or avoid SQL*Plus.

Peter

Roy Smith <r...@panix.com> wrote in message
news:roy-347340.2...@news.panix.com...

[David Fitzjarrell]

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/

[John Russell]

With long strings, you might find SQL*Loader is more flexible than
SQL*Plus.

John