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

its not an Oracle bug (it stores and retrieves the \0 just fine. its 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 wouldnt 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, its 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.