I want to drop and recreate a table with create table as select statement.
While I can extract constraint definitions as existing before dropping, I am not sure how Check contraint can be added to the table after recreating it since I see the check constraints names as existing now as
SYS_C0179 etc which indicate that the names are actually system generated.
While I can find out the check constraint definition from dba_constraints how do I add check constraint after recreating the table.
Originally posted by MePreeti
I think the following statement should work.
But why am I getting this error.
select 'alter table cdwprod.dim_employee add constraint '||constraint_name||' check('||search_condition||');' from DBA_constraints where table_name='DIM_EMPLOYEE'
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
ORA-00932 inconsistent datatypes
Cause: One of the following:
> An attempt was made to perform an operation on incompatible datatypes.
For example, adding a character field to a date field (dates may only be
added to numeric fields) or concatenating a character field with a long field.
> An attempt was made to perform an operation on a database object (such as
a table or view) that is not intended for normal use. For example, system
tables cannot be modified by a user. Note that on rare occasions this error
occurs because a misspelled object name matched a restricted object’s name.
> An attempt was made to use an undocumented view.
Action: If the cause is
> different datatypes, then use consistent datatypes. For example, convert the
character field to a numeric field with the TO_NUMBER function before
adding it to the date field. Functions may not be used with long fields.
> an object not intended for normal use, then do not access the restricted