ampersand in DML statement 1

[ChrisQuick]

Is there a way (other than setting verify off in PL/SQL which is not what we're using to submit the statements) to allow an ampersand (&) character in a DML statement? I know for example, that to allow a single quote character ('), you can simply double it up (''). Is there anything similar for ampersands? Thanks. [sig][/sig]

 

[carp]

Try this:

SET ESCAPE '\'
INSERT INTO my_table VALUES 'Smith \& Wesson';

[sig][/sig]

 

[ChrisQuick]

I not sure that will work for me. I need to be able to send everything as a single statement. [sig][/sig]

 

[carp]

Check to find out what the default escape character is for your system. Then use that character before the ampersand. For instance, if you start a sqlplus session and enter the command
SHOW escape
and get a response that indicates your escape character is '~', then try
INSERT INTO my_table VALUES ('Smith ~& Wesson');
[sig][/sig]

 

[karluk]

Another possibility is

INSERT INTO my_table VALUES ('Smith '||CHR(38)||' Wesson');

This may work for you, depending on how easy it is to modify your sql. [sig][/sig]

 

[ChrisQuick]

Carp, when I did a SHOW ESCAPE in PL/SQL it shows that it is OFF. I also tried karluk's suggestion of the CHR(38) and that didn't work either. Is there some one of setting the show escape to always be on in the database? [sig][/sig]

 

[carp]

escape OFF means you do not have an escape character defined. You can remedy this by either executing the SET ESCAPE command previously shown or entering this command into your glogin.sql script. I recommend the latter, since it will automatically set this command when you start a session. [sig][/sig]

 

[karluk]

May I ask what kind of problem you had with the chr(38) function? It works just fine on the few examples I've tested. The main problem I see is that it is cumbersome, not that it doesn't work at all. You have to build your string by concatenating three pieces. [sig][/sig]

 

[ChrisQuick]

Is the glogin.sql script applicable only to pl/sql or would it apply to connection made through other means (ODBC for example)? Would it apply to all users or just me?

The problem I had with karluk's suggestion was that the results in the column included the ||Chr(38|| in the value as if was just plain text. It didn't actually cause a concatenation to occur. [sig][/sig]

 

[karluk]

Ah, I think you missed two vital quotes. You have to replace your string with three separate pieces. The pieces before and after the chr(38) have to be enclosed in quotes, but the chr(38) can't have quotes around it:

'Smith '
||
chr(38)
||
' Wesson' [sig][/sig]