oracle ORA-00932: 不一致的数据类型：预期 - 得到 CLOB 00932. 00000
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28282195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000
提问by Alex Poole
I have column response_xml and request_xml which consist of large string. I have used substring function for this large string in order to fetch the SUBSCRIPTION_ID from response_xml and orderType from request_xml. The query is working fine. But now i want to put condition for this query such that it should only return SUBSCRIPTION_ID where orderType='NEW'. I used the below query but resulting an error as ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s". I think i need to cast column REQUEST_XML from clob to VARCHAR or XMLTYPE and then apply substring on this. But dont know how to do this. The error comes on where condition iw.order_type='NEW' . Here is my query:
我有包含大字符串的列 response_xml 和 request_xml 。我对这个大字符串使用了子字符串函数，以便从 response_xml 中获取 SUBSCRIPTION_ID，从 request_xml 中获取 orderType。查询工作正常。但现在我想为这个查询设置条件，使其只返回 SUBSCRIPTION_ID where orderType='NEW'。我使用了以下查询，但导致错误为ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s". 我想我需要将列 REQUEST_XML 从 clob 转换为 VARCHAR 或 XMLTYPE，然后在其上应用子字符串。但不知道如何做到这一点。错误出现在条件 iw.order_type='NEW' 上。这是我的查询：
select iw.order_type
from (
SELECT REPLACE(REPLACE(REGEXP_SUBSTR(RESPONSE_XML, '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>\d+</ax2130:id>'),
'<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>', ''), '</ax2130:id>', '')
AS SUBSCRIPTION_ID ,
REPLACE(REPLACE(REGEXP_SUBSTR(REQUEST_XML, '<ns7:orderType>\d+</ns7:orderType>'), '<ns7:orderType>', ''), '</ns7:orderType>', '')
AS order_type,
CREATE_DATE
FROM
SOAP_MONITORING
where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder'
) iw
where iw.order_type='NEW'
order by iw.CREATE_DATE desc
回答by Alex Poole
The column expression is being returned as a CLOB; you can cast it to a VARCHAR2:
列表达式作为 CLOB 返回；您可以将其转换为 VARCHAR2：
CAST(REPLACE(REPLACE(
REGEXP_SUBSTR(REQUEST_XML, '<ns7:orderType>\d+</ns7:orderType>'),
'<ns7:orderType>', ''), '</ns7:orderType>', '')
AS VARCHAR(10)) AS order_type,
Where the size you specify should be large enough for the longest value you expect in that element. If you're expecting a string, though, having \d+in that regular expression isn't going to be right - if the actual value is NEW you'll get null back. You can use '<ns7:orderType>.+</ns7:orderType>', for example.
您指定的大小应该足以满足您在该元素中期望的最长值。但是，如果您需要一个字符串，\d+则在该正则表达式中使用该字符串是不正确的 - 如果实际值为 NEW，您将返回 null。'<ns7:orderType>.+</ns7:orderType>'例如，您可以使用。
You could wrap the subscription_idexpression in a to_number()call as well if you want that to be more usable; you already know that's a numeric value from the \d+regular expression, where it does seem more appropriate.
如果您希望它更有用，您也可以将subscription_id表达式包装在to_number()调用中；您已经知道这是\d+正则表达式中的数值，它看起来更合适。
