ORA-00932
ORA-00932 means that SQL engine wanted to retrieve CHAR strings, but it saw LONG data type, which is unsearchable. So SQL engine refused to process any further.
Let's see a case that can reproduce ORA-00932.
SQL> conn hr/hr
Connected.
SQL> desc job_contents
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_ID VARCHAR2(10)
JOB_TITLE LONG
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
Now, we want to search content of the LONG column.
SQL> select job_id from job_contents where job_title like '%Manager%';
select job_id from job_contents where job_title like '%Manager%'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
Even though we convert the LONG column into CLOB at run-time, we still got ORA-00932.
SQL> select job_id from job_contents where to_lob(job_title) like '%Manager%';
select job_id from job_contents where to_lob(job_title) like '%Manager%'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got LONG
Solution
The solution is to convert the LONG column into a CLOB column by TO_LOB function in a new concrete table.
1. Create Table as Select
We use CTAS to create the new table.
SQL> create table job_contents_searchable as select job_id, to_lob(job_title) job_title, min_salary, max_salary from job_contents;
Table created.
As you can see, we converted LONG into CLOB during creating the table.
2. Rename Original Table
We moved away the original table to make a room for the new table.
SQL> rename job_contents to job_contents_bak;
Table renamed.
3. Rename New Table
We rename the new table to the original table name.
SQL> rename job_contents_searchable to job_contents;
Table renamed.
4. Searchable Table
Now we can search the table.
SQL> select job_id from job_contents where job_title like '%Manager%';
JOB_ID
----------
FI_MGR
AC_MGR
SA_MAN
PU_MAN
ST_MAN
MK_MAN
6 rows selected.
Another error case of ORA-00932 is to search TEXT column of ALL_VIEWS.