DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-00932: inconsistent datatypes: expected NUMBER got LONG

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    ORA-00932: inconsistent datatypes: expected NUMBER got LONG

    Hi folks,

    I'm trying to generate dynamically script of creating check constraints.
    I got the following error:

    Code:
    SQL> select 'ALTER TABLE '||a.table_name||' ADD CONSTRAINT '||a.constraint_name||' CHECK '||b.SEARCH_CONDITION||';'
      2  from user_cons_columns a,user_constraints b
      3  where a.table_name like 'T_%'
      4  and b.CONSTRAINT_TYPE='C'
      5  and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME;
    select 'ALTER TABLE '||a.table_name||' ADD CONSTRAINT '||a.constraint_name||' CHECK '||b.SEARCH_COND
                                                                                           *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got LONG
    How can I overcome on LONG columns in such select statement?

    Thanks in advance,
    Nir

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    omg I got the same error yesterday but with dba_mviews.query

    With long we have to convert it to varchar first then concatenate :(

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hey Pando,

    I cannot convert data dictionary table column from LONG to VARCHAR.

    Any other suggestions?

    Thanks in advance,
    Nir

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    This type of thing is possible in PL/SQL:

    Code:
    BEGIN
    	FOR r IN (
    		SELECT cns.table_name, cns.constraint_name
    		     , cns.search_condition
    		FROM   user_constraints cns
    		     , user_cons_columns col
    		WHERE  cns.constraint_type = 'C'
    		AND    col.owner = cns.owner
    		AND    col.table_name = cns.table_name
    		AND    col.constraint_name = cns.constraint_name
    	)
    	LOOP
    		DBMS_OUTPUT.PUT_LINE
    		( 'ALTER TABLE ' || r.table_name ||
    		 ' ADD CONSTRAINT ' || r.constraint_name ||
    		 ' CHECK (' || r.search_condition || ');');
    	END LOOP;
    END;
    However DBMS_METADATA might be a better approach.

  5. #5
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi WilliamR,

    Thanks a lot!!
    It works fantastic!

    Best regards,
    Nir

  6. #6
    Join Date
    Jan 2004
    Posts
    162
    Regarding original query - the join to user_cons_columns seems superfluous and in the case of multi-column constraints would result in multiple DDLs for a given constraint.

Posting Permissions

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


Click Here to Expand Forum to Full Width