I have query where i am trying to extract value from XML column. The query returns an error as:
31159. 00000 - "XML DB is in an invalid state"
*Cause: XML DB's internal tables are in an invalid state, probably
because the database was not upgraded or the upgrade was
not successful
*Action: Ensure that the database is upgraded successfully. If the problem
persists, contact Oracle Support
I have understood that the XDB user is not present in my Oracle version and therefore i am getting this error.But my confusion lies as i am using another query for which i am extraction the values from XML column and it works fine for me. Below is the query i used to check whether the Oracle XML database is present or not and it returns null:
select comp_name, status, version
from DBA_REGISTRY
where comp_name= 'Oracle XML Database'
Below is my query which works fine and extract the value from XML:
SELECT DISTINCT(ID),xt_req.SUBSCRIPTION_ID
FROM TEMP_SOAP_MONITORING sm
CROSS JOIN XMLTable(XMLNAMESPACES (
'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
'http://service.soap.CDRator.com' as "ns",
'http://core.data.soap.CDRator.com/xsd' as "ax2130",
'http://webshop.result.service.soap.CDRator.com/xsd' as "ax2147",
'http://core.signup.data.soap.CDRator.com/xsd' as "ns3",
'http://service.soap.CDRator.com' as "ns5",
'http://core.result.service.soap.CDRator.com/xsd' as "ax232"
),
'for $i in /soapenv:Envelope/soapenv:Body/ns:placeShopOrderResponse/ns:return/ax2147:subscriptions
return $i/ax2130:id'
passing XMLType(sm.RESPONSE_XML)
columns "SUBSCRIPTION_ID" number path '/') xt_req
,RATOR_MONITORING_CONFIGURATION.BRAND BR
WHERE xt_brandid.RATOR_BRAND_ID IS NOT NULL AND BR.RATOR_BRAND_ID = xt_brandid.RATOR_BRAND_ID;
Below is my query which gives an error as XML db is invalid state:
INSERT INTO TEMP_REQ_SENT_SPAIN(ID,PROVISIONING_TASK_ID,TIME_STAMP,REQUEST_TYPE,RESPONSE_TYPE,ORDER_NUMBER,EVENT_ID,STATUS_ID,ERROR_CODE,SUBSCRIPTION_ID)
Select DISTINCT(PL.ID),PL.PROVISIONING_TASK_ID,PL.TIME_STAMP,PL.REQUEST_TYPE,PL.RESPONSE_TYPE,PL.ORDER_NUMBER,
CASE WHEN (PL.STATUS_ID NOT IN (1) and REQUEST_TYPE='MIGOPT_PACK' and PL.RESPONSE_TYPE='RES') then 10021 END,
PL.STATUS_ID,xt.ERROR_CODE,PL.SUBSCRIPTION_ID
from TEMP_PROVISIONING_LOG PL
CROSS JOIN XMLTable(XMLNAMESPACES (
'http://core.signup.data.soap.CDRator.com/xsd' as "Header"),
'for $i in //Order return $i'
passing XMLType.createxml(PL.RESPONSE)
columns error_code varchar2(100) path 'Header/@errorCode') xt
I really dont know the reason why some of my query runs as XML DB user is not present in my Oracle version and why i am getting the error for other queries when trying to extract the value from XML column.