Sql – ORA-00932: inconsistent datatypes: expected – got CLOB 00932. 00000

castingcloboraclesqlsubquery

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

Best Answer

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,

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.

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.

Related Topic