Constraint definition not properly inserted by query in Oracle

Question

I have the below query from Oracle query point of view is that I have created a constraint on table BOA_INVOICE as shown below

ALTER TABLE BOA_INVOICE 
   ADD CONSTRAINT CK_INVOICE_SOURCE_SYSTEM 
       CHECK (SOURCE_SYSTEM IN ('PCE', 'PDS', 'WALLSTREET', 'SYSTEM X & ECOTRADE'));

Now this constraint is added successfully for table BOA_INVOICE but the problem is that when I see the constraint definition in Oracle developer it is shown as shown below

SOURCE_SYSTEM IN ('PCE', 'PDS', 'WALLSTREET', 'SYSTEM X null')

Now when I carefully observe the last value was SYSTEM X & ECOTRADE but in the constraint definition in oracle developer I can see it was displayed as SYSTEM X null.

Please advise how can i correct this to SYSTEM X & ECOTRADE in the constraint definition

http://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus — OldProgrammer, May 21 '16 at 19:00

score 0 · Answer 1 · answered May 21 '16 at 19:07

The problem is not Oracle, per se - it's SQL*Plus. The & is used to tell SQL*Plus that you're naming a parameter, and its presence in the string is confusing SQL*Plus.

There are two workarounds. First you can use SET DEFINE OFF to tell SQL*Plus that you're not using named parameters, and it should just treat the & like any other character.

The other way to do it is to put the & as the last character in the string, then concatenate the remainder of the string to the first one, as in

ALTER TABLE BOA_INVOICE 
   ADD CONSTRAINT CK_INVOICE_SOURCE_SYSTEM 
       CHECK (SOURCE_SYSTEM IN ('PCE', 'PDS', 'WALLSTREET', 'SYSTEM X &' || ' ECOTRADE'));

Best of luck.

Constraint definition not properly inserted by query in Oracle

1 Answers1