Menu
An even better fix is to run the following code in SQL Developer: SET DEFINE OFF; Once you run that, you won't ever see the “Enter Substitution Variable” again!
SQLDeveloper understands the SET DEFINE command as used in SQLPlus. So setting this value to OFF will disable value substitution. Easy! The command is run like a normal SQL statement, After that, no more substitution is performed, the prompts go away and the ampersands behave as regular characters.
This happens because when you define variables this way, the value is not stored anywhere. The variable is just substituted by the value and the value is discarded, so if the variable appears again, SQL Developer will prompt for a value again.
Call this before the query:
set define off;
Alternatively, hacky:
update t set country = 'Trinidad and Tobago' where country = 'trinidad &' || ' tobago';
From Tuning SQL*Plus:
SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.
In SQL*Plus putting SET DEFINE ? at the top of the script will normally solve this. Might work for Oracle SQL Developer as well.
this will work as you asked without CHAR(38):
update t set country = 'Trinidad and Tobago' where country = 'trinidad & '|| 'tobago';
create table table99(col1 varchar(40));
insert into table99 values('Trinidad &' || ' Tobago');
insert into table99 values('Trinidad &' || ' Tobago');
insert into table99 values('Trinidad &' || ' Tobago');
insert into table99 values('Trinidad &' || ' Tobago');
SELECT * FROM table99;
update table99 set col1 = 'Trinidad and Tobago' where col1 = 'Trinidad &'||' Tobago';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With