How to insert '&user' in oracle column?

Question

One of my oracle database columns needs to have the value 'user.password&user.user_name' When I execute the query, I see a popup, because the query has '&user' in it. How do I get around it?

Example query:

update Table A set value = 'user.password&user.user_name' where id = 1;

Answer 1

Modify the query to insert the value

'user.password'||chr(38)||'user.user_name'

Example query:

update Table A set value = 'user.password'||chr(38)||'user.user_name' where id = 1;

Answer 2

You can find several decisions at How do I ignore ampersands in a SQL script running from SQL Plus?

Btw, you can use just update table set value = &value where id = 1 and type your value into popup.

Answer 3

chr(38) should work independent of the platform.

However, if you are using SQL*Plus, then you could use the sqlplus command:

set define off

In SQL*Plus, ampersand is used for substitution variable. The above command will ignore it.

For example,

SQL> SET DEFINE OFF
SQL> SELECT 'user.password&user.user_name' FROM DUAL;

'USER.PASSWORD&USER.USER_NAM
----------------------------
user.password&user.user_name

SQL>

Well, of course,chr(38) will work too:

SQL> SELECT 'user.password'||chr(38)||'user.user_name' FROM dual;

'USER.PASSWORD'||CHR(38)||'U
----------------------------
user.password&user.user_name

SQL>