Hi Marco,
0) Database Configuration
Code:
Using
Home: OraClient11g_home1
DLL: D:\Oracle\product.2.0\client\bin\oci.dll
OCI: version 11.1
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Character Sets
Character size: 4 byte(s)
CharSetID: 873
NCharSetID: 2000
Unicode Support: True
NLS_LANG: DUTCH_BELGIUM.AL32UTF8
NLS_CHARACTERSET: AL32UTF8
NLS_NCHAR_CHARACTERSET: AL16UTF16
1) Create table
Code:
drop table lob_tab;
create table lob_tab (Id number(8), xmlcol xmltype);
insert into lob_tab
(id, xmlcol)
values
(1, null);
commit;
2) Create package
Code:
create or replace package Test_DOA_XMLType is
procedure Change_Test_Xml (p_Id in lob_tab.Id%type,
p_Xml_Data in clob);
end Test_DOA_XMLType;
/
create or replace package body Test_DOA_XMLType is
procedure Change_Test_Xml (p_Id in lob_tab.Id%type,
p_Xml_Data in clob) is
begin
update lob_tab
set Xmlcol = xmltype.Createxml(p_Xml_Data, null, 1, 1)
where Id = p_Id;
commit;
end Change_Test_Xml;
end Test_DOA_XMLType;
/
3) Now open a delphi project and add a TOracleSession, a TOracleQuery, a TMemo and 4 TButton:
OracleQuery: (Copy and paste on a TForm)
Code:
object OracleQuery1: TOracleQuery
SQL.Strings = (
'begin'
' test_doa_xmltype.change_test_xml(p_id => :p_id,'
' p_xml_data => :p_xml_data);'
'end;')
Session = OracleSession1
Optimize = False
Variables.Data = {
04000000020000000A0000003A0050005F004900440003000000000000000000
0000160000003A0050005F0058004D004C005F00440041005400410070000000
0000000000000000}
Left = 296
Top = 280
end
Variables are defined as: (in case the Variables.Data is not readable)
P_ID: integer
P_XML_DATA: CLOB
4) Create Test XML's
Small
Code:
-- SMALL XML
SELECT xmlElement("MyRoot",
XMLAgg((select xmlElement("MyList",
XMLAgg(XMLElement(evalname(t.OBJECT_NAME), t.OWNER || t.OBJECT_NAME))).extract('/*')
from all_objects t
where Not (t.OBJECT_NAME like '%$%')
and Not (t.OBJECT_NAME like '%=%')
and Not (t.OBJECT_NAME like '%#%')
and Not (t.OBJECT_NAME like '%/%')
and Not (t.OBJECT_NAME like '% %')
and rownum < 10
)))
FROM dual
CONNECT BY LEVEL <= 2
BIG
Code:
-- BIG XML
SELECT xmlElement("MyRoot",
XMLAgg((select xmlElement("MyList",
XMLAgg(XMLElement(evalname(t.OBJECT_NAME), t.OWNER || t.OBJECT_NAME))).extract('/*')
from all_objects t
where Not (t.OBJECT_NAME like '%$%')
and Not (t.OBJECT_NAME like '%=%')
and Not (t.OBJECT_NAME like '%#%')
and Not (t.OBJECT_NAME like '%/%')
and Not (t.OBJECT_NAME like '% %')
)))
FROM dual
CONNECT BY LEVEL <= 30
Save these 2 CLOB's to disk as xml (with PL/SQL Developer (PSD) )
Name them "BigXml.xml" and "SmallXml.xml"
TESTS
=====
A) Small XML
1) Filling LobLocator with "AsString"
Button Click:
Code:
procedure TForm1.btnSmallAsStringClick(Sender: TObject);
var
mLobLocator1: TLOBLocator;
mStringStream: TStringStream;
begin
with OracleQuery1 do
begin
mStringStream := TStringStream.Create;
mLobLocator1 := TLobLocator.CreateTemporary(OracleSession1, otClob, true);
try
mStringStream.LoadFromFile('SmallXML.xml');
mLobLocator1.AsString := mStringStream.ReadString(mStringStream.size);
mLobLocator1.SaveToFile('SmallXML_After.xml');
SetVariable(':P_ID', 1);
SetComplexVariable(':P_XML_DATA', mLobLocator1);
Execute;
// Curious test
mStringStream.LoadFromFile('SmallXML_After.xml');
mLobLocator1.AsString := mStringStream.ReadString(mStringStream.size);
mLobLocator1.SaveToFile('SmallXML_AfterBis.xml');
if Assigned(mLobLocator1) then
mLobLocator1.Free;
if Assigned(mStringStream) then
mStringStream.Free;
except On E:Exception do
begin
memo1.Lines.add(E.Message);
if assigned(mLoblocator1) then
try
mLobLocator1.Free;
except On E:Exception do
memo1.Lines.add(E.Message);
end;
if assigned(mStringStream) then
mStringStream.Free;
end;
end;
end;
end;
Results:
- The Small Xml was written correctly in the database. Querying and exporting this file (with PSD) gives the same file.
- The SaveToFile from the Loblocator "SmallXML_After.xml" generated a different file that's also wrong.
- The SaveToFile from the Loblocator "SmallXML_AfterBis.xml" generated a different file again that's completely garbage.
2) Filling LobLocator with "CopyFrom"
Button Click:
Code:
procedure TForm1.btnSmallCopyFromClick(Sender: TObject);
var
mLobLocator1: TLOBLocator;
mStringStream: TStringStream;
begin
with OracleQuery1 do
begin
mStringStream := TStringStream.Create;
mLobLocator1 := TLobLocator.CreateTemporary(OracleSession1, otClob, true);
try
try
mStringStream.LoadFromFile('SmallXml.xml');
mLobLocator1.CopyFrom(mStringStream, mStringStream.Size);
mLobLocator1.SaveToFile('SmallXml_After.xml');
SetVariable(':P_ID', 1);
SetComplexVariable(':P_XML_DATA', mLobLocator1);
Execute;
except On E:Exception do
begin
memo1.Lines.add(E.Message);
if assigned(mLoblocator1) then
try
mLobLocator1.Free;
except On E:Exception do
memo1.Lines.add(E.Message);
end;
if assigned(mStringStream) then
mStringStream.Free;
end;
end;
finally
mLobLocator1.Free;
mStringStream.Free;
end;
end;
end;
Results:
- The xml in the database resembles chinese.
- The SaveToFile from the Loblocator "SmallXML_After.xml" generated a different file that's missing a carriage return. No big problem an sich, but I was expecting the same file
When I was checking the contents of the "SmallXML_After.xml" I noticed that there was no BOM in this file. Although generated from PSD!
Retrying this function with the same XML but with the BOM added gived the following result:
UTF-16:
- The XML in the database looked allright. But when saving this file to disk, I noticed that there were 2 BOM's inside it
- The SaveToFile from the Loblocator "SmallXML_After.xml" generated the same file as the original! => CORRECT
UTF-8:
- The XML in the database resembles chinese.
- The SaveToFile from the Loblocator "SmallXML_After.xml" generated the same file as the original! => CORRECT
B) Big XML (> 18Mb)
1) Filling LobLocator with "AsString"
Button Click:
Code:
procedure TForm1.btnBigXmlAsStringClick(Sender: TObject);
var
mLobLocator1: TLOBLocator;
mStringStream: TStringStream;
begin
with OracleQuery1 do
begin
mStringStream := TStringStream.Create;
mLobLocator1 := TLobLocator.CreateTemporary(OracleSession1, otClob, true);
try
mStringStream.LoadFromFile('BigXML_2.xml');
mLobLocator1.AsString := mStringStream.ReadString(mStringStream.size);
mLobLocator1.SaveToFile('BigXML_After.xml');
SetVariable(':P_ID', 1);
SetComplexVariable(':P_XML_DATA', mLobLocator1);
Execute;
if Assigned(mLobLocator1) then
mLobLocator1.Free;
if Assigned(mStringStream) then
mStringStream.Free;
except On E:Exception do
begin
memo1.Lines.add(E.Message);
if assigned(mLoblocator1) then
try
mLobLocator1.Free;
except On E:Exception do
memo1.Lines.add(E.Message);
end;
if assigned(mStringStream) then
mStringStream.Free;
end;
end;
end;
end;
Result: Error: "Project Project1.exe raised exception class EOracleError with message 'ORA-24817: Kan de opgegeven chunk niet toewijzen aan de huidige LOB-bewerking.
'."
on "mLobLocator1.AsString := mStringStream.ReadString(mStringStream.size);" line
2) Filling LobLocator with "CopyFrom"
Code:
procedure TForm1.btnBigCopyFromClick(Sender: TObject);
var
mLobLocator1: TLOBLocator;
mStringStream: TStringStream;
begin
with OracleQuery1 do
begin
mStringStream := TStringStream.Create;
mLobLocator1 := TLobLocator.CreateTemporary(OracleSession1, otClob, true);
try
try
mStringStream.LoadFromFile('BigXML.xml');
mLobLocator1.CopyFrom(mStringStream, mStringStream.Size);
mLobLocator1.SaveToFile('BigXML_After.xml');
SetVariable(':P_ID', 1);
SetComplexVariable(':P_XML_DATA', mLobLocator1);
Execute;
except On E:Exception do
begin
memo1.Lines.add(E.Message);
if assigned(mLoblocator1) then
try
mLobLocator1.Free;
except On E:Exception do
memo1.Lines.add(E.Message);
end;
if assigned(mStringStream) then
mStringStream.Free;
end;
end;
finally
mLobLocator1.Free;
mStringStream.Free;
end;
end;
end;
Results:
NO BOM:
-------
- Couldn't read the contents out of the database. Waiting indefinitely and forcing me to kill PSD.
- The SaveToFile from the Loblocator "SmallXML_After.xml" generated a different file that's copying some part of the original. This results in garbage.
UTF-8 added
-----------
- Couldn't read the contents out of the database. Waiting indefinitely and forcing me to kill PSD.
- The SaveToFile from the Loblocator "SmallXML_After.xml" generated a different file that's copying some part of the original. This results in garbage.
UTF-16 added
------------
- Database gives
- The SaveToFile from the Loblocator "SmallXML_After.xml" generated a different file that's copying some part of the original. This results in garbage.
Conclusion:
-----------
A) Storing in the database
I have found 2 ways to store SMALL xml's in the database. Using the AsString is prefered because this way it seems to keep the original contents.
I couldn't get a big xml in the database.
B) Using SaveToFile
This worked only when the original file has a BOM of UTF-16. All the other cases it produced something else.
So, what am I doing wrong ;-[
This took me an awful lot of work. This in the hope that this long standing issue will soon be solved!
See also
LobLocator.LoadFromFile problem
and
Problem with fetching huge clob as single piece
clob_variable) function. This can be used in an insert or update statement to pass the value to the XMLTYPE column, but instead of passing an XMLTYPE instance from the client you pass a temporary CLOB with the XML text.