Strange Oracle XMLType.getClobVal() result

Question

I use Oracle 11g (on Red Hat). I have simple regular table with XMLType column:

CREATE TABLE PROJECTS
(
  PROJECT_ID NUMBER(*, 0) NOT NULL,
  PROJECT SYS.XMLTYPE,
);

Using Oracle SQL Developer (on Windows) I do:

select T1.PROJECT P1 from PROJECTS T1 where PROJECT_ID = '161';

It works. I get one cell. I can double click and download whole XML file.

Then I tried to get result as CLOB:

select T1.PROJECT.getClobVal() P1 from PROJECTS T1 where PROJECT_ID = '161';

It works. I get one cell. I can double click and see whole text and copy it. BUT there is a problem. When I copy it to clipboard I get only first 4000 characters. It seems that there is 0x00 character at position 4000 and the rest of CLOB is not copied.

To confirm this, I wrote check in java:

// ... create projectsStatement
Reader reader = projectsStatement.getResultSet().getCharacterStream( "P1" );
BufferedReader bf = new BufferedReader( reader );
char buffer[] = new char[ 1024 ];
int count = 0;
int globalPos = 0;
while ( ( count = bf.read( buffer, 0, buffer.length ) ) > 0 )
    for ( int i = 0; i < count; i++, globalPos++ )
        if ( buffer[ i ] == 0 )
            throw new Exception( "ZERO at " + Integer.toString(globalPos) );

Reader returns full XML but my exception is thrown because there is null character at position 4000. I could remove this single byte but this would be rather strange workaround.

I don't use VARCHAR2 there but maybe this problem is related to VARCHAR2 limitation (4000 bytes) somehow ? Any other ideas ? Is this an Oracle bug or am I missing something ?

-------------------- Edit --------------------

Value was inserted using following stored procedure:

create or replace
procedure addProject( projectId number, projectXml clob ) is
  sqlstr varchar2(2000);
begin

  sqlstr := 'insert into projects ( PROJECT_ID, PROJECT ) VALUES ( :projectId, :projectData )';
  execute immediate sqlstr using projectId, XMLTYPE(projectXml);

end;

Java code used to call it:

try ( CallableStatement cs = connection.prepareCall("{call addProject(?,?)}") )
{
    cs.setInt( "projectId", projectId );
    cs.setCharacterStream( "projectXml", new StringReader(xmlStr) , xmlStr.length() );
    cs.execute();
}

-------------------- Edit. SIMPLE TEST --------------------

I will use all I learned from your answers. Create simplest table:

create table T1 ( P XMLTYPE );

Prepare two CLOBs with XMLs. First with null character, second without.

declare
  P1 clob;
  P2 clob;
  P3 clob;
begin

  P1 := '<a>';
  P2 := '<a>';
  FOR i IN 1..1000 LOOP
    P1 := P1 || '0123456789' || chr(0);
    P2 := P2 || '0123456789';
  END LOOP;
  P1 := P1 || '</a>';
  P2 := P2 || '</a>';

Check if null is in the first CLOB and not in the second one:

DBMS_OUTPUT.put_line( DBMS_LOB.INSTR( P1, chr(0) ) );
DBMS_OUTPUT.put_line( DBMS_LOB.INSTR( P2, chr(0) ) );

We will get as expected:

14
0

Try to insert first CLOB into XMLTYPE. It will not work. It is not possible to insert such value:

insert into T1 ( P ) values ( XMLTYPE( P1 ) );

Try to insert second CLOB into XMLTYPE. It will work:

insert into T1 ( P ) values ( XMLTYPE( P2 ) );

Try to read inserted XML into third CLOB. It will work:

select T.P.getClobVal() into P3 from T1 T where rownum = 1;

Check if there is null. There is NO null:

DBMS_OUTPUT.put_line( DBMS_LOB.INSTR( P3, chr(0) ) );

It seams that there is no null inside database and as long as we are in the PL/SQL context, there is no null. But when I try to use following SQL in SQL Developer ( on Windows ) or in Java ( on Red Hat EE and Tomcat7 ) I get null character at position 4000 in all returned CLOBs:

select T.P.getClobVal() from T1 T;

BR, JM

Answer 1

It's not an Oracle bug (it stores and retrieves the \0 just fine. It's a client/windows bug (Different clients behave differently in regards to "NUL" as does windows)

chr(0) is not a valid character in non-blobs really (I'm curious how you ever get the XMLType to accept it in the first place as usually it wouldn't parse).

\0 is used in C to denote the end of a string (NUL terminator) and some GUIs would stop processing the string at that point. For example:

![SQL> select 'IM VISIBLE'||chr(0)||'BUT IM INVISIBLE'
  2  from dual
  3  /

'IMVISIBLE'||CHR(0)||'BUTIM
---------------------------
IM VISIBLE BUT IM INVISIBLE

SQL>

yet toad fails miserably on this:

sql developer fares better, as you can see it:

but if you copy it, the clipboard will only copy it up to the nul character. this copy paste error isn't SQL developers fault though, it's a problem with windows clipboard not allowing NUL to paste properly.

you should just replace(T1.PROJECT.getClobVal(), chr(0), null) to get round this when using sql developer/windows clipboard.

Answer 2

I also was experiencing this same issue exactly as described by Mikosz (seeing an extra 'NUL' character around the 4000th character when outputting my XMLType value as a Clob). While playing around in SQLDeveloper I noticed an interesting workaround. I was trying to see the output of my XMLType, but was tired of scrolling to the 4000th character, so I started wrapping the Clob output in a substr(...). Much to my surprise, the issue actually disappeared. I incorporated this into my Java app and confirmed that the issue was no longer present and my Clob could be retrieved without the extra character. I know that this isn't an ideal workaround, and I'm still not sure why it works (would love if someone could explain it to me), but here's an abbreviated example of what I've currently got working:

// Gets the xml contents
String sql = "select substr(x.xml_content.getClobVal(), 0) as xml_content from my_table x";
ps = con.prepareStatement(sql);
if(rs.next()) {
  Reader reader = new BufferedReader(rs.getCharacterStream("xml_content"));
  ...
}