Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unanswered: Adding Check Constraint

    Hi,
    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.
    Thanks!

  2. #2
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    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

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    The error is coming at 'search_condition' in teh statement

  4. #4
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    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
    Hi,

    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
    object.

  5. #5
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    But why should I get it for the query I have mentioned. I am only trying to get the check condition from dba_constraints.

  6. #6
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    OK , Got it, its because I am trying to concatenate a character with the LONG Field.
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •