Solved

Pass large Delphi XML Document into Oracle XMLType field

Posted on 2011-09-30
15
2,800 Views
Last Modified: 2012-05-12
Hi Experts,

I'm trying to find a decent way to get a large (> 32K) XML document from Delphi into an Oracle 10g XMLType field using the OraOleDB driver. I've tried to do this is several ways but each one so far failed. I first tried to send the XML document as a clob to Oracle and have Oracle convert it to a XMLType:

procedure TForm2.Button2Click(Sender: TObject);
var
  url : string;
  stream : TMemoryStream;
  cmd : TADOCommand;
begin
  url := 'http://some_xml_feed';
  stream := TMemoryStream.Create;
  IdHTTP1.Get(url, stream);
  cmd := TADOCommand.Create(Self);
  cmd.Connection := ADOConnection1;
  cmd.CommandText := 'insert into txmldocs (xml) values (XMLType(:XML))';
  cmd.Parameters.ParamByName('XML').LoadFromStream(stream, ftMemo);
  cmd.Execute;
end;

Open in new window

But this fails with Oracle error ORA-01461: ora-01461 can bind a long value only for insert into a long column';

My second try was to send it to an oracle stored procedure to force the conversion from clob to XMLType in pl/sql:

create or replace
PROCEDURE CREATE_XMLDOC (
  l_clob IN CLOB)
IS
BEGIN
  INSERT INTO TXMLDOCS (XML) VALUES (XMLTYPE(l_clob));
END;

Open in new window


procedure TForm2.Button2Click(Sender: TObject);
var
  url : string;
  stream : TMemoryStream;
  cmd : TADOCommand;
begin
  url := 'http://some_xml_feed';
  stream := TMemoryStream.Create;
  IdHTTP1.Get(url, stream);
  cmd := TADOCommand.Create(Self);
  cmd.Connection := ADOConnection1;
  cmd.CommandText := 'begin create_xmldoc(:XML); end;';
  cmd.Parameters.ParamByName('XML').LoadFromStream(stream, ftMemo);
  cmd.Execute;
end;

Open in new window

But this fails with 'ora-01460 unimplemented or unreasonable conversion requested'.

Is there a proper way to do this. My code is part of a larger project so i cannot switch to another provider.

Thanks for any suggestion!

Gertjan
0
Comment
Question by:keeskeeskees
  • 7
  • 6
  • 2
15 Comments
 
LVL 9

Expert Comment

by:rinfo
ID: 36896154
I think i have read somewhere the proper way of handling xmltype is something like this
create table XmlTest( id number
                    , data_xml XMLType)
    XmlType data_xml STORE AS CLOB;
and you can insert values by using
INSERT INTO XmlTest2 VALUES (XMLType(xmlfilecontents);
and acess the values using
SELECT x.getCLobVal() from XmlTest2 x;
0
 

Author Comment

by:keeskeeskees
ID: 36896534
That's right. That is also the way i do this in PL/SQL, but the problem here is that i need to pass the xml to oracle from a Delphi Client using the OraOLeDB provider. What you suggest is what i try in attempt 1 above and then i get ORA-01461.


Gertjan
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 36897972
What version of Delphi are you using?  What version of Oracle?  I would suspect a character type mismatch (1 or 2 byte characters on either side).  I have never had an issue sending large values to Oracle but I have not used the OraOleDB driver either.
0
 
LVL 9

Expert Comment

by:rinfo
ID: 36898692
Yes i can see that its almost simillar.
However why you are getting ORA-1461 which is for long column value mismatch.
I think the table might have not been created properly .
Why not try and create a table using the following statement exactly as it is and then try to insert the xml values.
Problem surely is with the misrepresentation of  table structure.
create table XmlTest( id number
                    , data_xml XMLType)
    XmlType data_xml STORE AS CLOB;
 
create table XmlTest( id number
                    , data_xml XMLType)
    XmlType data_xml STORE AS CLOB;

Open in new window

0
 

Author Comment

by:keeskeeskees
ID: 36899117
Hi Rinfo,

I use this table definition:
CREATE TABLE TXMLDOCS (
  ID INTEGER NOT NULL,
  PATH VARCHAR2(1024),
  CLOBDOC CLOB,
  XMLDOC XMLTYPE,
  RECORDSTATE NUMBER(1,0) DEFAULT 1 NOT NULL,
  RECORDAANMAAKDATUM DATE DEFAULT SYSDATE NOT NULL,
  RECORDWIJZIGDATUM DATE DEFAULT SYSDATE NOT NULL)
  XMLTYPE XMLDOC STORE AS CLOB
/

Open in new window

when i store the xml in the clobfield using
  com.CommandText := 'insert into txmldocs (path, clobdoc) values (:PATH, :XML)';
it works ok, but when i try to store it in the XMLtype field using
  com.CommandText := 'insert into txmldocs (path, xmldoc) values (:PATH, XMLTYPE(:XML))';
i get ORA-01461.

I now use the workaround below but it is so ugly i'm embarrassed to show it. The clob field is only used as a temp store until its contents are copied to the xmltype field.

procedure TForm2.Button2Click(Sender: TObject);
var
  url : string;
  Mstream : TMemoryStream;
  xmldoc : TXMLDocument;
  com : TADOCommand;
begin
  url := 'http://some_xml_feed';
  Mstream := TMemoryStream.Create;
  IdHTTP1.Get(url, Mstream);
  com := TADOCommand.Create(Self);
  com.Connection := ADOConnection1;
  com.CommandText := 'insert into txmldocs (path, clobdoc) values (:PATH, :XML)';
  com.Parameters.ParamByName('PATH').Value := 'xmlfeeds/VIND';
  com.Parameters.ParamByName('XML').LoadFromStream(Mstream, ftMemo);
  com.Execute;
  com.CommandText := 'update txmldocs set xmldoc = XMLType(clobdoc), clobdoc = NULL where PATH = :PATH';
  com.Parameters.ParamByName('PATH').Value := 'xmlfeeds/VIND';
  com.Execute;
end;

Open in new window


@developementguru: i use
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod / OraOLEDB10.dll
Delphi 2010

Gertjan
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 36904008
Have you tried using streaming (instead of text assignment) to get the text into the field?
0
 

Author Comment

by:keeskeeskees
ID: 36904208
I use a stream in delphi to pass the xml parameter to the Oracle ole DB provider. I've no idea how i could use streaming on the oracle side...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 21

Expert Comment

by:developmentguru
ID: 36904465
In Delphi you would do something like:


var
  XMLField : TBlobField;

begin
  XMLField := TBlobField(MyDataSet.Fields[WhateverField]);
  XMLField.LoadFromStream(String stream, memory stream, file stream, etc);
end;

Keep in mind that the blob field type only works with blob type fields (normally any blob, clob, etc... unknown length large field type).  This mechanism works WITH the database driver to handle the streaming correctly on the other end.
0
 

Author Comment

by:keeskeeskees
ID: 36904856
Hi Developmentguru,

This is exaclty what i do (see first message) and that works ok for clob fields. The problem is that i can't get it into an xmltype column which is wat i need because in the next step of the procedure i want to invoke XMLTRASNSFORM wich requires an XMLType.
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 36904873
I am unfamiliar with the second parameter you pass as I have never needed to use a second parameter.  I checked just before writing this and the normal Delphi call has no second parameter.  Could that be messing things up?
0
 
LVL 21

Expert Comment

by:developmentguru
ID: 36904929
Ah, I see the difference.  What you are doing is VERY different from what I suggested.  The way I have handled things was to retrieve the record and then set the field using a stream.  You are trying to set a parameter of an ADO command.  I don't even know how to start on all the ways that the two are not the same.  Try an experiment for me.

1) write a new record without the XML (using a dataset descendant).
2) Edit the record using the same dataset you just used
3) Using a blob field cast of the XML field, load from stream
4) post.

If that works then you can try to do the append and try step 3 and 4 (without needing to post the record before loading the XML).
0
 

Author Comment

by:keeskeeskees
ID: 36905366
Hi developmentguru,

I wrote this procedure:

procedure OracleStoreXMLDocFRomStream (AConn : TADOConnection; APath : string; AStream : TStream);
var
  XMLField : TBlobField;
begin
  with TADODataset.Create(nil) do
  begin
    Connection := AConn;
    CommandText := 'SELECT * FROM TXMLDOCS';
    Open;
    Insert;
    FieldByName('PATH').AsString := APath;
    Post;
    Edit;
    XMLField := TBlobField(FieldByName('XMLDOC'));
    XMLField.LoadFromStream(AStream);
    Post;
    Close;
    Free;
  end;
end;

Open in new window


I'm not sure if this is correct code as i have never used datasets this way. I get error 'ROW-00004 Invalid column datatype' on both the insert an th edit part...
0
 
LVL 21

Accepted Solution

by:
developmentguru earned 500 total points
ID: 36906341
This looks like it should work.  Evidently the driver differentiates between BLOB and XML and argues over it.  Many people run into the exact same error and I have found little help for it in my own research.  This link shows that they (Embarcadero) have been made aware of the issue.

http://qc.embarcadero.com/wc/qcmain.aspx?d=16324


What are your options for the second field you originally tried to use?  It is possible that the second parameter will allow the database to see it in the right way if the right second parameter is used.

Another approach is mentioned in this link:
http://forums.devshed.com/oracle-development-96/converting-xmltype-to-other-datatypes-161162.html

You would need to have a stored procedure that takes as parameters the field values to insert or update (making sure the XML is passed as CLOB).  Then you would need to use Oracle's own commands to convert it like this:
xmltype.createxml(CLOBXML)

This would be done as part of the insert or update that you do and allow 1) Delphi to speak to the database as CLOB and 2) Oracle to make the necessary adjustment.
0
 

Author Comment

by:keeskeeskees
ID: 36908213
This is the way i'm going now (it is shown in my 2nd message). I now have a clobfield and a xmlfield in the oracle table and do the following steps:

1) copy the xml as clobstream to oracle into the clobfield
'insert into txmldocs (path, clobdoc) values (:PATH, :XML)' where :XML is the clob parameter
2) tell oracle to copy the clobfield to the xmlfield
'update txmldocs set xmldoc = XMLType(clobdoc), clobdoc = NULL where PATH = :PATH'

I cannot combine the to steps in one stored procedure and call that because that throws ora-01460. It does work if i put the second step in a before trigger in Oracle but only when there are no other before triggers for a reason i don't understand. In my case there are other before triggers so i can't use that trick.
@developmentguru (and rinfo) thanks for your help. I will mark you message as accepted solution,

Gertjan
0
 

Author Closing Comment

by:keeskeeskees
ID: 36908219
I guess the solution is as good as it can get. The real solution should come from Oracle and or Delphi ;-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now