ORA-00932: inconsistent datatypes: expected NUMBER got LONG

[nir_s]

Hi folks,

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

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

[pando]

omg I got the same error yesterday but with dba_mviews.query

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

[nir_s]

Hey Pando,

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

Any other suggestions?

Thanks in advance,
Nir

[WilliamR]

This type of thing is possible in PL/SQL:

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.

[nir_s]

Hi WilliamR,

Thanks a lot!!
It works fantastic!

Best regards,
Nir

[padders]

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.