plsql oracle check in constraint error

Question

Im getting this error: ORA-00904: "M": invalid identifier --> if I put ('M','F') //single quotation i got this error message: PLS-00103: Encountered the symbol "M" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem return returning <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between into using || multiset bulk member SUBMULTISET_ --> if I remove the constraint the table would be created normally

this is my code

EXECUTE IMMEDIATE 'CREATE TABLE dependents
    ( Id    NUMBER(6)
    , FirstName     VARCHAR2(20)
    , LastName      VARCHAR2(25)
     CONSTRAINT     dep_last_name_nn  NOT NULL
    , Birthdate Date
    , Relation VARCHAR2(20)
    , Gender char(1) 
    , RelativeId Number(6) 
    , CONSTRAINT pk_dependent primary key (Id) 
    , CONSTRAINT ck_gender CHECK(Gender in("F","M")) 


    )';         

Increate

Answer 1

The oracle engine expects 'F', 'M'. Since it is embedded in a string, you must use pascal-like escape for quotes, try this:

EXECUTE IMMEDIATE 'CREATE TABLE dependents
    ( Id    NUMBER(6)
    , FirstName     VARCHAR2(20)
    , LastName      VARCHAR2(25)
     CONSTRAINT     dep_last_name_nn  NOT NULL
    , Birthdate Date
    , Relation VARCHAR2(20)
    , Gender char(1) 
    , RelativeId Number(6) 
    , CONSTRAINT pk_dependent primary key (Id) 
    , CONSTRAINT ck_gender CHECK(Gender in(''F'',''M'')) 
    )';         

It will run as you want.

Sheriff

Answer 2

Assuming you are using a relatively recent version of Oracle, I'd use the new string escape syntax

EXECUTE IMMEDIATE q'[CREATE TABLE dependents
    ( Id    NUMBER(6)
    , FirstName     VARCHAR2(20)
    , LastName      VARCHAR2(25)
      CONSTRAINT    dep_last_name_nn  NOT NULL
    , Birthdate Date
    , Relation VARCHAR2(20)
    , Gender char(1) 
    , RelativeId Number(6) 
    , CONSTRAINT pk_dependent primary key (Id) 
    , CONSTRAINT ck_gender CHECK(Gender in('F','M')) 
    )]';         

If you don't want to use the new syntax, you'll need two consecutive single quotes, not a double quote

EXECUTE IMMEDIATE 'CREATE TABLE dependents
    ( Id    NUMBER(6)
    , FirstName     VARCHAR2(20)
    , LastName      VARCHAR2(25)
      CONSTRAINT    dep_last_name_nn  NOT NULL
    , Birthdate Date
    , Relation VARCHAR2(20)
    , Gender char(1) 
    , RelativeId Number(6) 
    , CONSTRAINT pk_dependent primary key (Id) 
    , CONSTRAINT ck_gender CHECK(Gender in(''F'',''M'')) 
    )';         

I would, however, caution that dynamically creating objects is generally a bad idea-- there are generally better ways to accomplish this sort of thing.

Carvelbuilt

Answer 3

Change the CHECK constraint list to use single quotes:

CONSTRAINT ck_gender CHECK(Gender in(''F'',''M''))

Character constants in Oracle are enclosed in single quotes, not double quotes.

Moshemoshell