ORA-00932: inconsistent datatypes: expected CHAR got LONG

The error ORA-00932 means that SQL engine wanted to retrieve CHAR strings, but it saw LONG data type, which is unsearchable. SQL engine refused to process any further.

I am accessing the ALR_ALERTS in my Oracle EBS to see the select statement

SQL> conn apps/apps Connected.
SQL> desc alr_alerts
Name Null? Type

---------------------- -------- -------------

APPLICATION_ID NOT NULL NUMBER

ALERT_ID NOT NULL NUMBER

ALERT_NAME NOT NULL VARCHAR2(50)

LAST_UPDATE_DATE NOT NULL DATE

LAST_UPDATED_BY NOT NULL NUMBER

CREATION_DATE NOT NULL DATE

CREATED_BY NOT NULL NUMBER

LAST_UPDATE_LOGIN NUMBER

ALERT_CONDITION_TYPE NOT NULL VARCHAR2(1)

ENABLED_FLAG NOT NULL VARCHAR2(1)

START_DATE_ACTIVE NOT NULL DATE

END_DATE_ACTIVE DATE

TABLE_ID NUMBER

TABLE_APPLICATION_ID NUMBER

DESCRIPTION VARCHAR2(240)

FREQUENCY_TYPE VARCHAR2(1)

WEEKLY_CHECK_DAY VARCHAR2(3)

MONTHLY_CHECK_DAY_NUM NUMBER

DAYS_BETWEEN_CHECKS NUMBER

CHECK_BEGIN_DATE DATE

DATE_LAST_CHECKED DATE

INSERT_FLAG VARCHAR2(1)

UPDATE_FLAG VARCHAR2(1)

DELETE_FLAG VARCHAR2(1)

MAINTAIN_HISTORY_DAYS NUMBER

CHECK_TIME NUMBER

CHECK_START_TIME NUMBER

CHECK_END_TIME NUMBER

SECONDS_BETWEEN_CHECKS NUMBER

CHECK_ONCE_DAILY_FLAG VARCHAR2(1)

ONE_TIME_ONLY_FLAG NUMBER(1)

TABLE_NAME VARCHAR2(31)

SQL_STATEMENT_TEXT LONG

SECURITY_GROUP_ID NUMBER

Now, we want to search sql_statement_text of the LONG column.

SQL> select alert_name,description,sql_statement_text from alr_alerts where sql_statement_text like '%start_date%';

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 alert_name,description,to_lob(sql_statement_text) from alr_alerts where sql_statement_text like '%start_date%';

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 table.

1. Create Table as Select

We use CTAS to create the new table. I can drop it when my work is done

SQL> create table appsread.alr_alerts_bkp as (select alert_name,description,to_lob(sql_statement_text) sql_statement from alr_alerts where application_id='101');

Table created.

As you can see, we converted LONG into CLOB during creating the table.

2. Search the Table

Now we can search the table.

SQL> select * from appsread.alr_alerts_bkp where sql_statement like '%start_date%'

If you like please follow and comment

ORA-00932: inconsistent datatypes: expected CHAR got LONG