Posted By: qulup
How can I write an insert statement which includes the & character? For example, if I wanted to insert “J&J Construction” into a column in the database.
I’m not sure if it makes a difference, but I’m using Oracle 9i.
I keep on forgetting this and coming back to it again! I think the best answer is a combination of the responses provided so far.
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 &.
set define on set escape on define myvar=/forth select 'back\ & &myvar' as swing from dual;
old 1: select 'back\ & &myvar' from dual new 1: select 'back & /forth' from dual SWING -------------- back & /forth
If you want to use a different escape character:
set define on set escape '#' define myvar=/forth select 'back #& &myvar' as swing from dual;
When you set a specific escape character, you may see ‘SP2-0272: escape character cannot be alphanumeric or whitespace’. This probably means you already have the escape character defined, and things get horribly self-referential. The clean way of avoiding this problem is to set escape off first:
set escape off set escape '#'