ORA-00932 Error.

[adarshcu]

insert into AUDIT_TABLE(
    "TIMESTAMP",
    AUDITCATEGORY,
    USERID,
    AUDIT_XML,
    "AUDITID",
    "SOURCE",
    "ROLE",
    EVENT_ID,
    EVENT_DATA,
    TYPE_OF_APPLICATION)
      select
    CURRENT_TIMESTAMP,
    AUDITCATEGORY,
    USERID,
    AUDIT_XML,
    (SELECT MAX(AUDITID)+1 FROM AUDIT_TABLE),
    dummyNumber||i,
    "ROLE",
    EVENT_ID,
    EVENT_DATA,
    TYPE_OF_APPLICATION
     from AUDIT_TABLE
    
    where "SOURCE"='1312171509321';

Hi, Above is a part of my stored procedure , I'm trying to create test data for AUDIT_TABLE by using a row in the existing table. I get an ORA-00932: inconsistent datatypes: expected - got BLOB error at the AUDI_XML line, which is a BLOB type. Why is there this error ?

Note : Select returns multiple rows and AUDIT_ID is the primary key for the table. Any help would be much appreciated.

Thanks.

[hfx642]

Try this first (remove the double quotes)

insert into AUDIT_TABLE(
    TIMESTAMP,
    AUDITCATEGORY,
    USERID,
    AUDIT_XML,
    AUDITID,
    SOURCE,
    ROLE,
    EVENT_ID,
    EVENT_DATA,
    TYPE_OF_APPLICATION)
select
    CURRENT_TIMESTAMP,
    AUDITCATEGORY,
    USERID,
    AUDIT_XML,
    (SELECT MAX(AUDITID)+1 FROM AUDIT_TABLE),
    dummyNumber||i,
    'ROLE',
    EVENT_ID,
    EVENT_DATA,
    TYPE_OF_APPLICATION
from AUDIT_TABLE
where SOURCE = '1312171509321';

Second...
DON'T use TIMESTAMP or ROLE as column names!!!
They are reserved words.

[adarshcu]

Hi hfx642,

This is an existing table present in my databse. I used the double quotes only to make sure they are treated as column names rather than Reserved words.

Anyways i had a fix.

Instead of

AUDIT_XML

, I used

TO_BLOB('01010')

That solved the issue. Thanks !