Using the "&" on insert as a string 2

[jestrada101]

I used SET SCAN OFF

Is it safe to use this to allow sql to use the & in a string?

Thanks!
JE

 

[SantaMufasa]

Actually, a more specific method involves the SQL*Plus variable, "DEFINE". You can confirm the default contents of this variable with:

SQL> show define
define "&" (hex 26)
SQL> select 'Fine & Dandy' from dual;
Enter value for dandy: Tuning
old   1: select 'Fine & Dandy' from dual
new   1: select 'Fine Tuning' from dual

'FINETUNING
-----------
Fine Tuning

You can then turn it off with:

SQL> set define off
SQL> show define
define OFF

SQL> select 'Fine & Dandy' from dual;

'FINE&DANDY'
------------
Fine & Dandy

You can also re-assign any non alpha-numeric character you want to be the prompt character:

SQL> set define !
SQL> select 'Bang! Went the explosives' from dual;
Enter value for went: Sounded
old   1: select 'Bang! Went the explosives' from dual
new   1: select 'BangSounded the explosives' from dual

'BANGSOUNDEDTHEEXPLOSIVES'
--------------------------
BangSounded the explosives

Does this cover things?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[jestrada101]

Thanks Mufasa.. I think you've covered it all!

JE

 

[Zargo]

Good job Mufasa!!!