Converting LONG to CLOB – scaning LONG

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

Leave a Reply

Your email address will not be published. Required fields are marked *