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%';

Another method is to use DBMS_XMLGEN package to transform first LONG into XML then XML into columns that can be used directly in sql. It’s very easy.

CREATE TABLE employees 
(
  employee_id NUMBER,
  employee_name VARCHAR2(100),
  department_id NUMBER,
  hire_date DATE
)
PARTITION BY LIST (department_id)
(
  PARTITION hr VALUES (10, 20),
  PARTITION it VALUES (30, 40),
  PARTITION sales VALUES (50, 60)
);

Now, let’s insert some sample data into the table:

INSERT INTO employees (employee_id, employee_name, department_id, hire_date) 
VALUES (1, 'John Doe', 10, TO_DATE('2020-01-01', 'YYYY-MM-DD')); 

INSERT INTO employees (employee_id, employee_name, department_id, hire_date) 
VALUES (2, 'Jane Smith', 20, TO_DATE('2019-05-15', 'YYYY-MM-DD')); 

INSERT INTO employees (employee_id, employee_name, department_id, hire_date) 
VALUES (3, 'Jim Brown', 30, TO_DATE('2021-06-30', 'YYYY-MM-DD')); 

INSERT INTO employees (employee_id, employee_name, department_id, hire_date) 
VALUES (4, 'Jessica White', 50, TO_DATE('2020-11-01', 'YYYY-MM-DD'));

Next, we want to query the partition data from the DBA_TAB_PARTITIONS view, generate the XML, and then use XMLTable to filter out specific partitions based on the HIGH_VALUE.

For example, let’s say we want to filter out partitions where the HIGH_VALUE is greater than a certain value (for instance, partitions with HIGH_VALUE above '40').

SELECT * 
FROM XMLTable
( 
  '/ROWSET/ROW' PASSING 
  (SELECT XMLType(DBMS_XMLGEN.getXML('SELECT table_owner, table_name, partition_name, high_value 
FROM dba_tab_partitions
 WHERE table_name = ''EMPLOYEES'' ')) FROM dual) 
COLUMNS 
  table_owner VARCHAR2(128) PATH 'TABLE_OWNER', 
  table_name VARCHAR2(128) PATH 'TABLE_NAME', 
  partition_name VARCHAR2(128) PATH 'PARTITION_NAME', 
  high_value CLOB PATH 'HIGH_VALUE' 
) 
WHERE TO_NUMBER(high_value) > 40; -- Filter HIGH_VALUE > 40

Hope it helps 🙂

Tomasz