-1

Hi I updated all column values of a column like :

Update Sample_table 
set sample_column = 'A&B' 
where sample column is Null;

Some variable pop up came where it was selecting '&b' and i clicked ok. I see the results and all values are showing as 'AA&B'. What do i do now. I try to again all values to A&b and it doesnt work. A pop up comes up asking type variables. This should go into production as well so instead of replacing the first A or deleting it how can i update all values to A&B now and also when its Null. what is wrong with the '&' in the value ?

eshirvana
  • 23,227
  • 3
  • 22
  • 38
sree
  • 1
  • 4

2 Answers2

1

Add this line before your update statement:

SET DEFINE OFF;

Oracle SQL Developer implements a feature from SQLplus that allows you to create parameters for your SQLplus script. Parameters start with an ampersand. So &B is treated as a parameter and Oracle SQL Developer asks for the value of the parameter.

SET DEFINE OFF turns this feature off.

Codo
  • 75,595
  • 17
  • 168
  • 206
0

You should SET DEFINE OFF. It depends on tool you use how exactly to do that.

In SQL*Plus and SQL Developer (both Oracle's tools), you do exactly that - run that statement.

This is what you currently have:

SQL> select 'a&b' from dual;
Enter value for b:
old   1: select 'a&b' from dual
new   1: select 'a' from dual

'
-
a

This is what you should do:

SQL> set define off
SQL> select 'a&b' from dual;

'A&
---
a&b

SQL>

In TOAD, you'd enter its options and search for "Substitution variable prompting" and set it to "None".

I don't use other tools so I can't suggest anything, but they must have that option as well.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Well i did understand your suggestions but set define off also prompted me the same variable dialogue box. I entered the value as &B for the variable and it fixed the value to A&B instead of AA&B. I will put the SET define off and give it to Prod. DBA should take care. Thanks Littlefoot for the help as always. – sree Aug 17 '21 at 19:16