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
utl_fileand see how the contents look like? Can you also tryselect XMLType.getClobVal(PROJECT) from PROJECTS;? (Nothing functionally different though) – Predella