Result of Strange Oracle XMLType.getClobVal ()

Question

I am using Oracle 11g (on Red Hat). I have a simple regular table with an XMLType column:

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

Using Oracle SQL Developer (on Windows), I:

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

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

Then I tried to get the 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 all the text and copy it. But there is a problem. When I copy it to the clipboard, I get only the first 4000 characters. It seems that at position 4000 there are 0x00 characters and the rest of the CLOB is not copied.

To confirm this, I wrote a 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 due to the presence of a null character at position 4000. I could remove this single byte, but that would be a rather strange solution.

I do not use VARCHAR2 there, but maybe this problem is related to limiting VARCHAR2 (4000 bytes) in some way? Any other ideas? Is this an Oracle bug or am I missing something?

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

The value was inserted using the 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(); } 

-------------------- Change. SIMPLE TEST --------------------

I will use everything that I learned from your answers. Create a simple table:

 create table T1 ( P XMLTYPE ); 

Prepare two CLOBs with XML. First with a null character, the 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>'; 

Make sure the null value is in the first CLOB, and not in the second:

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

We will receive as expected:

 14 0 

Try pasting the first CLOB into XMLTYPE. This will not work. Cannot insert such a value:

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

Try pasting the second CLOB into XMLTYPE. It will work:

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

Try reading the inserted XML in the third CLOB. It will work:

 select TPgetClobVal() into P3 from T1 T where rownum = 1; 

Check if there is a null value. NO NO:

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

This means that the database does not have a null internal database, and while we are in the context of PL / SQL, there is no null value. But when I try to use the following SQL in SQL Developer (on Windows) or Java (on Red Hat EE and Tomcat7), I get a null character at position 4000 in all returned CLOB files:

 select TPgetClobVal() from T1 T; 

BR, JM

Answer 1

its not an Oracle error (it stores and retrieves the value \ 0 just fine. Its a client / window error (different clients behave differently with respect to "NUL", like windows)

chr (0) is really not a valid non-blobs character (I'm curious how you ever got XMLType to accept it first, as usual, it didn't parse).

\ 0 is used in C to indicate the end of a line (NUL terminator), and some GUIs will stop processing the line 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> 

but the toad fails in this regard:

sql developer is better, as you can see:

but if you copy it, the clipboard will copy it only up to the null character. this error copying patches is not a SQL developer error, although the problem with the Windows buffer does not allow the NUL to insert correctly.

you just need to replace(T1.PROJECT.getClobVal(), chr(0), null) to get around this when using the SQL / SQL clipboard.

Answer 2

I also experienced this same problem exactly as Mikosz described (seeing the extra "NUL" character around the 4000th character when outputting my XMLType value as Clob). While playing SQLDeveloper, I noticed an interesting workaround. I tried to see the output of my XMLType, but was tired of scrolling to the 4000th character, so I started to wrap the Clob output in substr (...). To my great surprise, the problem really disappeared. I have included this in my Java application and confirmed that the problem no longer exists and that my Clob can be restored without an extra character. I know this is not an ideal workaround, and I still don't know why it works (I would like someone to explain this to me), but here is a shortened example of what I was working on:

 // 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")); ... } 

Answer 3

Error: 14781609 XDB: XMLType.getclobval () returns a temporary LOB when XML is stored in the CLOB. fix in patchset 11.2.0.4

and another solution if read as blob, then no error, for example

 T1.PROJECT.getBlobVal(nls_charset_id('UTF8')) 

Answer 4

It's easy enough to check if there is a call to .getClobVal() or not - run the INSTR test in PL / SQL (not Java) on your resulting CLOB to find out if CHR(0) exists or not.

If this is not the case, I would point your finger at your Oracle client client.