SQL错误: ORA-00932:不一致的数据类型:预期- got CHAR

Question

当我在Oracle上运行这个简单的查询来删除SQL*PLUS中的一行时，我会得到以下错误：

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

完整代码：

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

错误：

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:

我试着做：

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

通过这个，我得到了以下错误：

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

PS:这个标题是这样的：

​

​

不知道如何修正this..any的想法？？谢谢

Answer 1

可以使用XMLQuery将XML与所需的值进行比较，例如：

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

快速演示：

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                                                              

Answer 2

该错误似乎是由于TITLE列是XMLTYPE这一事实造成的。如果是这样的话，这可以是一种方式：

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