Skip to content

How to Resolve ORA-00932: inconsistent datatypes: expected CHAR got LONG

  • by
  • November 28, 2020October 24, 2021

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.

Actually, Oracle recommends developers to use CLOB instead of LONG data type, moreover, it urges developers to convert LONG into CLOB.

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.

Leave a Reply

Your email address will not be published.