title: Strange Oracle XMLType.getClobVal() result
body: <p>I use Oracle 11g (on Red Hat). I have simple regular table with XMLType column:</p>
  
  <pre><code>CREATE TABLE PROJECTS
  (
    PROJECT_ID NUMBER(*, 0) NOT NULL,
    PROJECT SYS.XMLTYPE,
  );
  </code></pre>
  
  <p>Using Oracle SQL Developer (on Windows) I do:</p>
  
  <pre><code>select T1.PROJECT P1 from PROJECTS T1 where PROJECT_ID = '161';
  </code></pre>
  
  <p>It works. I get one cell. I can double click and download whole XML file.</p>
  
  <p>Then I tried to get result as CLOB:</p>
  
  <pre><code>select T1.PROJECT.getClobVal() P1 from PROJECTS T1 where PROJECT_ID = '161';
  </code></pre>
  
  <p>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.</p>
  
  <p>To confirm this, I wrote check in java:</p>
  
  <pre><code>// ... 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 ) ) &gt; 0 )
      for ( int i = 0; i &lt; count; i++, globalPos++ )
          if ( buffer[ i ] == 0 )
              throw new Exception( "ZERO at " + Integer.toString(globalPos) );
  </code></pre>
  
  <p>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.</p>
  
  <p>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 ? </p>
  
  <p>-------------------- Edit --------------------</p>
  
  <p>Value was inserted using following stored procedure:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Java code used to call it:</p>
  
  <pre><code>try ( CallableStatement cs = connection.prepareCall("{call addProject(?,?)}") )
  {
      cs.setInt( "projectId", projectId );
      cs.setCharacterStream( "projectXml", new StringReader(xmlStr) , xmlStr.length() );
      cs.execute();
  }
  </code></pre>
  
  <p>-------------------- Edit. SIMPLE TEST --------------------</p>
  
  <p>I will use all I learned from your answers. Create simplest table:</p>
  
  <pre><code>create table T1 ( P XMLTYPE );
  </code></pre>
  
  <p>Prepare two CLOBs with XMLs. First with null character, second without.</p>
  
  <pre><code>declare
    P1 clob;
    P2 clob;
    P3 clob;
  begin
  
    P1 := '&lt;a&gt;';
    P2 := '&lt;a&gt;';
    FOR i IN 1..1000 LOOP
      P1 := P1 || '0123456789' || chr(0);
      P2 := P2 || '0123456789';
    END LOOP;
    P1 := P1 || '&lt;/a&gt;';
    P2 := P2 || '&lt;/a&gt;';
  </code></pre>
  
  <p>Check if null is in the first CLOB and not in the second one:</p>
  
  <pre><code>DBMS_OUTPUT.put_line( DBMS_LOB.INSTR( P1, chr(0) ) );
  DBMS_OUTPUT.put_line( DBMS_LOB.INSTR( P2, chr(0) ) );
  </code></pre>
  
  <p>We will get as expected:</p>
  
  <pre><code>14
  0
  </code></pre>
  
  <p>Try to insert first CLOB into XMLTYPE. It will not work. It is not possible to insert such value:</p>
  
  <pre><code>insert into T1 ( P ) values ( XMLTYPE( P1 ) );
  </code></pre>
  
  <p>Try to insert second CLOB into XMLTYPE. It will work:</p>
  
  <pre><code>insert into T1 ( P ) values ( XMLTYPE( P2 ) );
  </code></pre>
  
  <p>Try to read inserted XML into third CLOB. It will work:</p>
  
  <pre><code>select T.P.getClobVal() into P3 from T1 T where rownum = 1;
  </code></pre>
  
  <p>Check if there is null. There is NO null:</p>
  
  <pre><code>DBMS_OUTPUT.put_line( DBMS_LOB.INSTR( P3, chr(0) ) );
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>select T.P.getClobVal() from T1 T;
  </code></pre>
  
  <p>BR,
  JM</p>