ORA-00932：数据类型不一致：预期-获得CLOB 00932. 00000 [英] ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000

问题描述

我有由大字符串组成的response_xml和request_xml列。我已经为这个大字符串使用了子字符串功能，以便从response_xml中获取SUBSCRIPTION_ID，并从request_xml中获取orderType。查询工作正常。但是，现在我想对此查询设置条件，使其仅在orderType = NEW的情况下返回SUBSCRIPTION_ID。我使用以下查询，但由于 ORA-00932而导致错误：数据类型不一致：预期-得到CLOB00932。00000-数据类型不一致：预期％s得到了％s 。我认为我需要将Clob的REQUEST_XML列转换为VARCHAR或XMLTYPE，然后在其上应用子字符串。但是不知道该怎么做。错误发生在条件iw.order_type ='NEW'的地方。这是我的查询：

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:

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

推荐答案

列表达式作为CLOB返回；您可以将其转换为VARCHAR2：

The column expression is being returned as a CLOB; you can cast it to a VARCHAR2:

CAST(REPLACE(REPLACE(
  REGEXP_SUBSTR(REQUEST_XML, '<ns7:orderType>\d+</ns7:orderType>'),
    '<ns7:orderType>', ''), '</ns7:orderType>', '')
  AS VARCHAR(10)) AS order_type,

您指定的大小应足够大您在该元素中期望的最长价值。但是，如果您期望一个字符串，则在该正则表达式中包含 \d + 并不正确-如果实际值为NEW，您将得到null背部。例如，您可以使用'< ns7：orderType>。+< / ns7：orderType>'。

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.

如果希望更有用，也可以在 to_number（）调用中包装 subscription_id 表达式;您已经知道这是 \d + 正则表达式中的一个数值，它似乎更合适。

You could wrap the subscription_id expression 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.

这篇关于ORA-00932：数据类型不一致：预期-获得CLOB 00932. 00000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！