Wrong Results when Using DBMS_XMLStore.deleteXML if Value contains SPACE at the End (Doc ID 1308510.1)

Last updated on MARCH 29, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

DBMS_XMLStore.deleteXML returns wrong results if value contains space at the end and is specified as key column.

SELECT auto_id,'>'||auto_name||'<' FROM tbl_auto
/

   AUTO_ID '>'||AUTO_NAME
---------- --------------
        10 >12345<
        11 >12345<
        20 > 12345<
        30 >12345 <

DECLARE
insCtx DBMS_XMLStore.ctxType;
rows NUMBER;
v VARCHAR2(32000) :='<ROWSET>
<ROW>
<AUTO_ID>30</AUTO_ID>
<AUTO_NAME>12345 </AUTO_NAME>
</ROW>
</ROWSET>';
BEGIN
insCtx := DBMS_XMLStore.newContext( 'TBL_AUTO' );
DBMS_XMLStore.setRowTag(insCtx, 'ROW');
DBMS_XMLStore.setKeyColumn(insCtx,'AUTO_NAME');
rows := DBMS_XMLStore.deleteXML (insCtx , v);
dbms_output.put_line(ROWS||' rows deleted');
DBMS_XMLStore.closeContext (insCtx);
END;
/

2 rows deleted

PL/SQL procedure successfully completed.


SELECT auto_id,'>'||auto_name||'<' FROM tbl_auto
/

   AUTO_ID '>'||AUTO_NAME
---------- --------------
        20 > 12345<
        30 >12345 <

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms