通过XML向Oracle表插入数据无报错但记录未写入问题咨询

核心问题出在XML节点路径不匹配及代码细节错误，具体如下：

• 根节点名称不匹配：存储过程中提取RecodSet节点的XPath写的是//NewDataSet/RecodSet，但实际生成的XML根节点是<DocumentElement>，不是NewDataSet，这会导致XMLSequence()返回空结果集，后续循环压根不会执行，所以没有插入操作，自然也不会报错。
• 字段提取路径存在多余空格：MW_ANTENNA_OF_UPTO06_DIA字段的提取路径里，节点名后多了一个多余空格：'//RecodSet/MW_ANTENNA_OF_UPTO06_DIA /text()'，就算循环执行，这个字段也会提取到空值。
• 未显式提交事务：Oracle默认开启隐式事务，若存储过程执行后没有执行COMMIT操作，插入的记录仅对当前会话可见，其他会话查询不到数据，会话断开后插入的记录会直接回滚。

1. 修正XPath路径，把//NewDataSet/RecodSet改为//DocumentElement/RecodSet，也可以直接用//RecodSet跳过根节点匹配。
2. 删除MW_ANTENNA_OF_UPTO06_DIA提取路径里的多余空格。
3. 存储过程末尾增加COMMIT;语句，或者调用存储过程后手动执行提交操作。

修正后的核心代码示例：

FOR RECD IN           
(            
SELECT            
Records.EXTRACT('//RecodSet/ID/text()').getstringval() As ID,
Records.EXTRACT('//RecodSet/SAP_ID/text()').getstringval() As SAP_ID,            
Records.EXTRACT('//RecodSet/CITY/text()').getstringval() As CITY,            
Records.EXTRACT('//RecodSet/CIRCLE/text()').getstringval() As CIRCLE,
Records.EXTRACT('//RecodSet/FINAL_SO_DATE/text()').getstringval() As FINAL_SO_DATE,            
Records.EXTRACT('//RecodSet/INVOICE_DATE/text()').getstringval() As INVOICE_DATE , 
Records.EXTRACT('//RecodSet/IP_ID/text()').getstringval() As IP_ID  ,          
Records.EXTRACT('//RecodSet/APPLICABLE_MSA/text()').getstringval() As APPLICABLE_MSA,
Records.EXTRACT('//RecodSet/SITE_CATEGORY/text()').getstringval() As SITE_CATEGORY,
Records.EXTRACT('//RecodSet/ID_OD/text()').getstringval() As ID_OD ,           
Records.EXTRACT('//RecodSet/RFI_DATE/text()').getstringval() As RFI_DATE ,
Records.EXTRACT('//RecodSet/BILL_START_DATE/text()').getstringval() As BILL_START_DATE,
Records.EXTRACT('//RecodSet/BILL_END_DATE/text()').getstringval() As BILL_END_DATE ,
Records.EXTRACT('//RecodSet/NO_OF_OPCO/text()').getstringval() As NO_OF_OPCO  ,
Records.EXTRACT('//RecodSet/ACTUAL_RENT_AMT/text()').getstringval() As ACTUAL_RENT_AMT,
Records.EXTRACT('//RecodSet/TENURE/text()').getstringval() As TENURE ,
-- 删掉路径里的多余空格
Records.EXTRACT('//RecodSet/MW_ANTENNA_OF_UPTO06_DIA/text()').getstringval() As MW_ANTENNA_OF_UPTO06_DIA ,
Records.EXTRACT('//RecodSet/MW_ANTENNA_OF_12DIA/text()').getstringval() As MW_ANTENNA_OF_12DIA,
Records.EXTRACT('//RecodSet/MW_ANTENNA_OF_GREATER12_DIA/text()').getstringval() As MW_ANTENNA_OF_GREATER12_DIA ,
Records.EXTRACT('//RecodSet/HEIGHT_OF_HEIGHEST_ANTENNA/text()').getstringval() As HEIGHT_OF_HEIGHEST_ANTENNA ,
Records.EXTRACT('//RecodSet/SITE_RRH_COUNT/text()').getstringval() As SITE_RRH_COUNT ,            
Records.EXTRACT('//RecodSet/WIND_SPEED/text()').getstringval() As WIND_SPEED ,
Records.EXTRACT('//RecodSet/GREATER_9_RRH/text()').getstringval() As GREATER_9_RRH ,
Records.EXTRACT('//RecodSet/FLOOR_SPACE_INDOOR/text()').getstringval() As FLOOR_SPACE_INDOOR ,
Records.EXTRACT('//RecodSet/FLOOR_SPACE_OUTDOOR/text()').getstringval() As FLOOR_SPACE_OUTDOOR ,            
Records.EXTRACT('//RecodSet/EB_STATUS_VALUE/text()').getstringval() As EB_STATUS_VALUE ,
Records.EXTRACT('//RecodSet/NO_OF_US/text()').getstringval() As NO_OF_US ,
Records.EXTRACT('//RecodSet/VOLUME_DISCOUNT/text()').getstringval() As VOLUME_DISCOUNT            
-- 修正根节点名称
FROM TABLE(XMLSequence(XMLType.EXTRACT('//DocumentElement/RecodSet')))Records
)LOOP

最后在存储过程的END UploadGTLData;之前添加COMMIT;即可。

内容的提问来源于stack exchange，提问作者Nadeem