How can I write an insert statement which includes the & character?


Firstly, & is the variable prefix in sqlplus/sqldeveloper, hence the problem - when it appears, it is expected to be part of a variable name.
SET DEFINE OFF will stop sqlplus interpreting & this way.
But what if you need to use sqlplus variables and literal & characters?
  • You need SET DEFINE ON to make variables work
  • And SET ESCAPE ON to escape uses of &.

===================

SQL> set define off;
SQL> update Table set Projectname='Project Name UNITS 1 & 2' where Projectname='Project AB';

1 row updated.

SQL> commit;

Commit complete.

SQL>