?
Solved

how to insert values containing '&'  into a table

Posted on 2008-06-18
4
Medium Priority
?
691 Views
Last Modified: 2013-12-19
When i try to insert a value with & into a table, like

insert into test_tab (test_field) values ('&test');

Oracle is substituting it before it gets passed to the oracle server. How can i make the insertion successful.

Please, clarify.
0
Comment
Question by:Prashanth_atthaluri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 600 total points
ID: 21812118
in your sql*plus script put

set define off

before your insert lines
0
 

Author Comment

by:Prashanth_atthaluri
ID: 21812143
What to do if am not using SQL*PLUS.
0
 
LVL 22

Accepted Solution

by:
DrSQL earned 1400 total points
ID: 21812211
Prashanth_atthaluri,
     I'm not sure what tool you are using.  The '&' substitution is a SQL*Plus convention, not an Oracle-wide one.  Most other tools (like Toad) have an escape character (like the "\").  You can also use the chr function to get an ampersand:

insert into test_tab (test_field) values (chr(38)||'test');

The '&' is an ascii 38.  The double bars are the concatenation characters.

Good luck!
DrSQL
0
 

Author Closing Comment

by:Prashanth_atthaluri
ID: 31468306
Thank You both for the solution.
If I have offended any one of you by splitting the points, I am sorry.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question