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"))
)';
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With