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?