CODEWITHSUNDEEP
sqloracle-database
jdjohnson920
jdjohnson920

Reputation: 57

Oracle SQL - Inserting a row is causing a pop-up box?

I'm inserting a few rows of data into a table, and one of them is causing an unusual result. The code is

INSERT INTO TABLE_GROUPRISK 
(GROUP, RISKLEVEL) VALUES ('APIs & Intermediates','Low');

When I run this statement, I get a pop-up with the word Intermediates. If I click the X on the pop up, the row is inserted as 

APIs null

Rather than as 

APIs & Intermediates

Does anyone know what's going on here?

Upvotes: 0

Views: 1283

Answers (2)

Aleksej
Aleksej

Reputation: 22949

'&' is used for substitution variables, so when you write '&' Oracle asks for a value to use a a substitute; for example:

SQL> INSERT INTO TABLE_GROUPRISK
  2  ("GROUP", RISKLEVEL) VALUES ('APIs & Intermediates','Low');
Enter value for intermediates: XXX
old   2: ("GROUP", RISKLEVEL) VALUES ('APIs & Intermediates','Low')
new   2: ("GROUP", RISKLEVEL) VALUES ('APIs XXX','Low')

1 row created.

You can not simply escape it:

SQL> INSERT INTO TABLE_GROUPRISK
  2  ("GROUP", RISKLEVEL) VALUES ('APIs \& Intermediates','Low');
Enter value for intermediates: YYY
old   2: ("GROUP", RISKLEVEL) VALUES ('APIs \& Intermediates','Low')
new   2: ("GROUP", RISKLEVEL) VALUES ('APIs \YYY','Low')

1 row created.

SQL> INSERT INTO TABLE_GROUPRISK
  2  ("GROUP", RISKLEVEL) VALUES (q'[APIs & Intermediates]','Low');
Enter value for intermediates: ZZZ
old   2: ("GROUP", RISKLEVEL) VALUES (q'[APIs & Intermediates]','Low')
new   2: ("GROUP", RISKLEVEL) VALUES (q'[APIs ZZZ]','Low')

1 row created.

You can decide to avoid substitution:

SQL> set define off
SQL> INSERT INTO TABLE_GROUPRISK
  2  ("GROUP", RISKLEVEL) VALUES ('APIs & Intermediates','Low');

1 row created.



SQL> select "GROUP" from table_grouprisk;

GROUP
--------------------------------------------------------------------------------
APIs XXX
APIs \YYY
APIs ZZZ
APIs & Intermediates

Upvotes: 3

CathalMF
CathalMF

Reputation: 10055

An ampersand character & needs to be escaped in a sql string.

You can replace it with the character code instead. 

INSERT INTO TABLE_GROUPRISK 
(GROUP, RISKLEVEL) VALUES ('APIs  ' || chr(38) || ' Intermediates' ,'Low');

alternatively you can place the & before the first single quote

INSERT INTO TABLE_GROUPRISK 
(GROUP, RISKLEVEL) VALUES ('APIs &' || ' Intermediates','Low');

Upvotes: 1

Related Questions