user1234 user1234 - 1 year ago 129
SQL Question

SQL Error: ORA-00932: inconsistent datatypes: expected - got CHAR

when I run this simple query to delete a row in SQL*PLUS on Oracle, I get the error:

Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected - got CHAR
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:


Complete code:

delete from db where TITLE= '<values><value lang="EN">db shared</value></values>';


error:

Error starting at line : 8 in command -
delete from widget_template where TITLE= '<values><value lang="EN">template shared for assets</value></values>'
Error at Command Line : 8 Column : 42
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected - got CHAR
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:


i tried doing:

delete from db where to_char(TITLE)= '<value lang="EN">template shared for all assets</value>'
;

with this i get the following err:

ORA-00932: inconsistent datatypes: expected NUMBER got -
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:


PS: this is how the TITLE col looks:

enter image description here

not sure how to fix this..any ideas??
Thanks

Answer Source

You could use an XMLQuery to compare the XML with the value you want, for example using:

xmlquery('for $i in /values/value[@lang="EN"]
    where $i/text() = "template shared for all assets" return $i'
  passing title returning content)

Quick demo:

create table db (id number, title xmltype);
insert into db (id, title) values (1,
  xmltype('<values><value lang="EN">db shared</value></values>'));
insert into db (id, title) values (2,
  xmltype('<values><value lang="EN">template shared for all assets</value></values>'));

select id,
  xmlquery('/values/value[@lang="EN"]/text()' passing title returning content) as title,
  xmlquery('for $i in /values/value[@lang="EN"]
      where $i/text() = "template shared for all assets" return $i/text()'
    passing title returning content) as matched
from db
order by id;

        ID TITLE                               MATCHED                            
---------- ----------------------------------- -----------------------------------
         1 db shared                                                              
         2 template shared for all assets      template shared for all assets     

delete from db
where xmlquery('for $i in /values/value[@lang="EN"]
    where $i/text() = "template shared for all assets" return $i'
  passing title returning content) is not null;

1 row deleted.

select id,
  xmlquery('/values/value[@lang="EN"]/text()' passing title returning content) as title,
  xmlquery('for $i in /values/value[@lang="EN"]
      where $i/text() = "template shared for all assets" return $i/text()'
    passing title returning content) as matched
from db
order by id;

        ID TITLE                               MATCHED                            
---------- ----------------------------------- -----------------------------------
         1 db shared