Try running these two simple statements on Oracle 10.2:
CREATE TABLE mytest
(table_name varchar2(30),
index_name varchar2(30),
column_expression clob,
column_position number);
INSERT INTO mytest
(table_name,
index_name,
column_expression,
column_position)
SELECT table_name, index_name,
to_lob(column_expression), column_position
FROM user_ind_expressions EXPRA
WHERE NOT EXISTS
(SELECT 1 FROM user_constraints
WHERE constraint_name = EXPRA.index_name
AND table_name = EXPRA.table_name);
This results in this error:
ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got LONG
If I omit the WHERE NOT EXISTS like this:
INSERT INTO mytest
(table_name,index_name,column_expression, column_position)
SELECT table_name,index_name,
to_lob(column_expression), column_position
FROM user_ind_expressions EXPRA;
It works:
23 rows created.
What is going on?
