-
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
-
omg I got the same error yesterday but with dba_mviews.query
With long we have to convert it to varchar first then concatenate :(
-
Hey Pando,
I cannot convert data dictionary table column from LONG to VARCHAR.
Any other suggestions?
Thanks in advance,
Nir
-
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.
-
Hi WilliamR,
Thanks a lot!!
It works fantastic!
Best regards,
Nir
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|