Home » SQL & PL/SQL » SQL & PL/SQL » how to handle this - how to escape & (oracle 10g)
how to handle this - how to escape & [message #398039] Wed, 15 April 2009 00:13 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
HI,

i am having one insert statement like this
INSERT INTO EMRReligionsLkup(RELIGION_ID, RELIGION_NAME, STATUS) VALUES ('8', 'Babi & Baha''I faiths', '1')
/

while i am executing it is asking for enter value like

and inserting in table like Babi I faiths in that column. i want everything to be inserted like Babi & Baha''I faiths.how to handle using this '&' substitution operator

[Updated on: Wed, 15 April 2009 00:38] by Moderator

Report message to a moderator

Re: how to handle this [message #398042 is a reply to message #398039] Wed, 15 April 2009 00:23 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
use set define off command before running the insert command.
Re: how to handle this [message #398044 is a reply to message #398042] Wed, 15 April 2009 00:25 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
if i do it in oracle sql developer then sql*plus commands won't work know
Re: how to handle this [message #398048 is a reply to message #398044] Wed, 15 April 2009 00:32 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I have not used SQL developer myself however this link http://wiki.oracle.com/thread/1148359/Insert+using+&?t=anon says that you can give set define off in SQL worksheet.
alternatively you can use chr(38)

INSERT INTO EMRReligionsLkup(RELIGION_ID, RELIGION_NAME, STATUS) VALUES ('8', 'Babi '||chr(38)||'Baha''I faiths', '1')
Re: how to handle this [message #398049 is a reply to message #398048] Wed, 15 April 2009 00:36 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yeah its working.thank you
Re: how to handle this [message #398050 is a reply to message #398044] Wed, 15 April 2009 00:37 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
But you didn't mention that you are using sql developer in OP.

Check this link.

By
Vamsi
Re: how to handle this [message #398152 is a reply to message #398050] Wed, 15 April 2009 06:55 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello vamsi kasina,

What about this?

SQL>select Q'#Hi! Naresh & Paresh :) #' from dual;
Enter value for paresh: 1
old   1: select Q'#Hi! Naresh & Paresh :) #' from dual
new   1: select Q'#Hi! Naresh 1 :) #' from dual

Q'#HI!NARESH1:)#
================
Hi! Naresh 1 :)

1 row selected.


regards,
Delna
Re: how to handle this [message #398158 is a reply to message #398152] Wed, 15 April 2009 07:20 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quoting is nothing to do with '&'.

Check link1 and link2.

By
Vamsi
Re: how to handle this [message #398159 is a reply to message #398152] Wed, 15 April 2009 07:21 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> select '''dawn&''' dawn from dual;

DAWN
-------
'dawn&'



SQL> select 'dawn&' dawn from dual;

DAWN
-----
dawn&

[Updated on: Wed, 15 April 2009 07:24]

Report message to a moderator

Re: how to handle this [message #398173 is a reply to message #398159] Wed, 15 April 2009 08:39 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
& before single quote (') is a different issue.

My previous reply is for delna.sexy(Quoting Mechanism in 10g))

By
Vamsi

[Updated on: Wed, 15 April 2009 08:41]

Report message to a moderator

Re: how to handle this [message #398312 is a reply to message #398173] Thu, 16 April 2009 00:33 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you vamsi kasina, but I am not able to see the content. While opening those linked pages, they display error message. Any other link?

regards,
Delna
Re: how to handle this [message #398395 is a reply to message #398312] Thu, 16 April 2009 02:46 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Those links are from Oracle Documentation.
I didn't notice that those are internal sites...

Try these link1 and link2.

By
Vamsi
Previous Topic: Time difference and sum
Next Topic: Multiple Record return [multi-merge]
Goto Forum:
  


Current Time: Sun Dec 04 18:39:12 CST 2016

Total time taken to generate the page: 0.07201 seconds