SQL manipulation on dba_views column text is impossible because it’s LONG type
SELECT view_name, text FROM user_views WHERE text LIKE '%SEARCH%'; SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got LONG 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause: *Action: |
So the column must be converted to CLOB. It can be done by using TO_LOB function which can be used in INSERT, CREATE TABLE as select, and UPDATE statements to convert.
You can’t use it for SELECT.
- LONG into a CLOB
- LONG RAW into a BLOB
CREATE TABLE convert_tbl AS SELECT view_name, TO_LOB(text) text FROM user_views; |
Now you can do your search
SELECT * FROM convert_tbl WHERE text LIKE '%SOME SERCH%'; |
Another way is to create dedicated temporary table where you can store output for your INSERT
CREATE global temporary TABLE convert_glb (view_name VARCHAR2(30), text clob); INSERT INTO convert_glb SELECT view_name, TO_LOB(text) text FROM user_views SELECT * FROM convert_glb WHERE high_value LIKE '%SOME SERCH%'; |
Hope it helps 🙂
Tomasz