Sign in or 
Location: The Official Oracle Wiki
Discussion: Insert using &
Category:
Discussion Forum /
General Discussion
Share this|
jstarkey |
Insert using &
Jan 21 2008, 2:05 PM EST
Hi,Does anyone know how to use the insert statement with inserting a value that includes the &?? I have a column in my database that requires me to use the & sign. For example, the field would be 'PB&J'. However, SQL Developer does not allow me to insert those values because it keeps asking me to insert a substitution value. Any ideas on how to get around this? Thanks! 10 out of 96 found this valuable. Do you? |
|
awnelson |
1. RE: Insert using &
Jan 21 2008, 5:12 PM EST
Not sure about SQL Developer, but in SQL*PLUS, variable substitution can be disabled by issuing SET VERIFY OFF.
4
out of
72 found this valuable.
Do you?
|
|
sheld06 |
2. RE: Insert using &
Jan 23 2008, 2:55 AM EST
HI, try using chr(38) instead, so your value would be 'PB'||CHR(38)||'J'
5
out of
67 found this valuable.
Do you?
|
|
lmuccio |
3. RE: Insert using &
Jan 23 2008, 3:16 PM EST
"Not sure about SQL Developer, but in SQL*PLUS, variable substitution can be disabled by issuing SET VERIFY OFF."SET SCAN OFF turns off & as a marker for a substition variable and lets it become a literal in your VARCHAR field. 4 out of 61 found this valuable. Do you? |
|
DrSQL |
4. RE: Insert using &
Jan 24 2008, 10:40 AM EST
"Not sure about SQL Developer, but in SQL*PLUS, variable substitution can be disabled by issuing SET VERIFY OFF."Actually, the command in SQL*Plus is SET DEF[ine] OFF You can also use the SET ESC[ape] \ which allows you to use & without giving up substitution variables. If you are creating dynamic SQL, just use a replace to "escape" your ampersands: replace(field_that_might_have_ampersand, chr(38), chr(92)||chr(38)) And, if you're typing it in: PB\&J Also, if you ever get asked to provide a value, keep in mind that it doesn't scan the input again. So, if your PB&J triggered "Enter value for J" you could respond with &J I realize you asked about Developer (not sure of the component or version), but I thought this side discussion might be helpful. 6 out of 63 found this valuable. Do you? |
saibabadandu |
5. RE: Insert using &
Jan 25 2008, 1:47 AM EST
Hi !In SQL*PLUS it works by default. No need to make SET VERIFY OFF. 1 out of 56 found this valuable. Do you? |
rucknrun |
6. RE: Insert using &
Jan 25 2008, 10:22 AM EST
Using SET VERIFY OFF is how I have done it in the past. Usually from
2
out of
56 found this valuable.
Do you?
|
|
rucknrun |
7. RE: Insert using &
Jan 25 2008, 10:23 AM EST
a SQL session. Sorry hit post to quickly!! 1 out of 52 found this valuable. Do you? |
|
mannu1841986 |
8. RE: Insert using &
Jan 27 2008, 12:23 AM EST
check for constraint violation for that column which u may hav mentioned in the table creation step!! drop it or alter the same!
1
out of
48 found this valuable.
Do you?
|
Hirdey |
9. RE: Insert using &
Jan 31 2008, 10:42 AM EST
write set prompt off before running the sql command then it wont ask
1
out of
28 found this valuable.
Do you?
|
|
mikew12345 |
10. RE: Insert using &
Feb 1 2008, 1:41 PM EST
Here's another way that works in SQL*PLUS:update dept set dname = concat('xy', '&') where deptno = 50 1 out of 27 found this valuable. Do you? |
wgblack3 |
11. RE: Insert using &
Feb 1 2008, 2:09 PM EST
I use chr(38). i.e. insert into table a values('t&j'); would be insert into tables a values ('t'||chr(38)||'j'); 9 out of 33 found this valuable. Do you? |
|
TheChakra |
12. RE: Insert using &
Feb 6 2008, 1:21 PM EST
In SQL Worksheet issue the command set define off This will accomplish what you want. 11 out of 21 found this valuable. Do you? |
angelvillamor |
13. RE: Insert using &
Feb 17 2008, 1:31 PM EST
In TOAD, I use SET SCAN OFF to treat "&" as normal text
6
out of
10 found this valuable.
Do you?
|
lichtig |
14. RE: Insert using &
Feb 18 2008, 1:34 AM EST
You can also use SET DEFINE OFF
2
out of
7 found this valuable.
Do you?
|
|
applesauce79 |
15. RE: Insert using &
Aug 19 2008, 8:58 PM EDT
"You can also use SET DEFINE OFF"is this command the same in 10 and 11g? Do you find this valuable? |
jaideepdba |
16. RE: Insert using &
Sep 1 2008, 4:07 PM EDT
Here are the steps to insert & into columns. 13:05:07 SQL> create table jaideep ( test varchar2(10) ) tablespace aru_small_tables; Table created. 13:05:26 SQL> set define # 13:06:02 SQL> insert into jaideep values ('&'); 1 row created. 13:06:20 SQL> insert into jaideep values ('PB&J'); 1 row created. 13:07:29 SQL> select * from jaideep; TEST ---------- & PB&J 3 out of 4 found this valuable. Do you? |
|
HPOracle2 |
17. RE: Insert using &
Sep 3 2008, 6:28 AM EDT
Issue the commandSET SCAN OFF at the beginning of your script 0 out of 1 found this valuable. Do you? |
|
WilliamRobertson |
18. RE: Insert using &
Sep 5 2008, 2:33 AM EDT
Good grief, SET SCAN has been listed a obsolete since Oracle 7. Why on Earth would you use that?SET VERIFY is irrelevant here. SQL*Plus: http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/index.htm SQL Developer: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b31695/index.htm SQL Developer appears to support SET DEFINE OFF on the SQL Worksheet command line. I don't use SQL Dev though and am not likely to so whether there is a preference setting or not I don't know (I couldn't find one). 0 out of 1 found this valuable. Do you? |
