Oracle Error "Inconsistent data types: expected CHAR received LONG"
I am trying to run the following query to find views that contain a given keyword:
select *
from ALL_VIEWS
where OWNER = 'SALESDBA'
and TEXT like '%rownum%';
The following error message appears:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 4 Column: 13
if i just select from ALL_VIEWS than i see a query (TEXT) in the TEXT field.
What am I doing wrong here?
+3
source to share
1 answer
Your problem is that TEXT is of type LONG - although Oracle has deprecated this type for a long time, they still use it in their own views: --(
To convert LONG to searchable CLOB you can use a function TO_LOB()
(see Oracle documentation for TO_LOB () .
Unfortunately this doesn't work for simple operators SELECT
. You will need to create an intermediate table:
create table search_all_views as
select av.owner, av.view_name, to_lob(text) as text_clob
from ALL_VIEWS av;
Then you can search this table:
select *
from search_all_views
where text_clob like '%rownum%';
+5
source to share