How to Ignore Ampersands in a SQL Script Running from SQL Plus

How do I ignore ampersands in a SQL script running from SQL Plus?

This may work for you:

set define off

Otherwise the ampersand needs to be at the end of a string,

'StackOverflow &' || ' you'

EDIT: I was click-happy when saving... This was referenced from a blog.

Escaping ampersand character in SQL string

Instead of

node_name = 'Geometric Vectors \& Matrices'

use

node_name = 'Geometric Vectors ' || chr(38) || ' Matrices' 

38 is the ascii code for ampersand, and in this form it will be interpreted as a string, nothing else. I tried it and it worked.

Another way could be using LIKE and an underline instead the '&' character:

node_name LIKE 'Geometric Vectors _ Matrices' 

The chance that you'll find some other record too, which is different in only this one character, is quite low.

How to escape ampersand in TOAD?

Try putting set define off at the beginning of your script. It should work with F5:

set define off;
insert into x values('hello & world'); 
set define on;

Oracle SQL escape character (for a '&')

the & is the default value for DEFINE, which allows you to use substitution variables. I like to turn it off using

SET DEFINE OFF

then you won't have to worry about escaping or CHR(38).

Arguments passed to SQL*Plus script override the arguments of script which invoked it

You can save the original argument in a new variable:

file1.sql:

prompt Argument 1 = &1
define somevar = &1

@file2.sql col2 col1

prompt Original argument 1 = &somevar

Output:

SQL> @file1.sql col1
Argument 1 = col1
This is file2.sql
Original argument 1 = col1

Can I stop SQL*Plus from displaying connected when I have a connect in a script?

Here's a tip I've used from Tom Kyte's book (forget which one). I have a script called connect.sql in my sqlplus directory:

set termout off 
connect &1 
@login

and in my glogin.sql I've added this:

select lower(user) || '@' || 
substr( global_name,1, decode( dot, 0, length(global_name), dot-1) ) 
global_name 
from (select global_name, instr(global_name,'.') dot from global_name );

set sqlprompt '&gname> '

set termout on

then I call

@connect user_01/pass_01@db_01

instead of

connect user_01/pass_01@db_01

---

Related Topics