How to upload to an XML Document to Oracle from Delphi

1
votes

How does one upload an XML document (datum of Oracle type XMLType) into a table on an Oracle database, from a Delphi program using the DBX library?

Example on the server-side

Suppose we have a simple table with a column of type XMLType. We want to insert a new record with an XML document in it. The document, as encoded as a string, is longer than the 4000 character limit for varchar2

create global temporary table XML_UPLOADED_DOC(
  DOC_ID  number  not null,
  CONTENT XMLType not null)
  on commit preserve rows;

alter table XML_UPLOADED_DOC add (
  constraint XML_UPLOADED_DOC_PK primary key (DOC_ID));

create sequence XML_UPLOADED_DOC_SEQ;

create or replace trigger XML_UPLOADED_DOC_BIR 
  before insert on XML_UPLOADED_DOC
  for each row
  begin
    select XML_UPLOADED_DOC_SEQ.NEXTVAL
      into :new.DOC_ID
      from dual;
  end;

What I have tried so far

This Delphi procedure, when run ...

procedure UploadDoc( Con: TSQLConnection; const Doc: IXMLDocument);
var
  Query: TSQLQuery;
  Param: TParam;
begin
  Query := TSQLQuery.Create( nil);
  Query.SQLConnection := Con;
  Query.SQL.Add( 'insert into XML_UPLOADED_DOC ');
  Query.SQL.Add( '(CONTENT) values (XmlType(''<place-marker/>'')) ');
  Query.SQL.Add( 'returning CONTENT into :content');
  Param := Query.ParamByName('content');
  Param.DataType  := ftString;
  Param.ParamType := ptInput;
  Param.AsString  := Doc.XML.Text;
  Query.ExecSQL;
  Query.Close;
  Query.Free;
end;

... results in this error ...

ORA-22816: unsupported feature with RETURNING clause

Tool versions

The server banner returns ...

BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0  Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

The oracle client version is 11g.

The Delphi version is Delphi XE7 + Update 1 (Version 21.0.17707.5020)

Update

I think I have worked out a solution which works for large documents and goes roughly like this: (1) Use stored procedures (and the DBX TSQLProc component) instead of TSQLQuery; (2) Create a temp table with a CLOB column, and upload the text equivalent of the document, by part-by-part after dividing the document into chunks of less than 32k characters. The oracle DBMS_LOB.WriteAppend() function can be used to build up the CLOB part by part. This method works around the driver limitations on clob size. (3) Call one last sproc to convert the clob into an XMLType instance. I will post a solution when it is polished.

1
oracledelphixmltype

1 Answers

0
votes

ORA-22816 Error Cause:

RETURNING clause is currently not supported for object type columns, LONG columns, remote tables, INSERT with subquery, and INSTEAD OF Triggers.

Try to cast to varchar2.

 declare 
      content varchar2(4000);
       begin
        insert  
      into XML_UPLOADED_DOC
      (CONTENT) values (XmlType('<place-marker/>'))
      returning CAST(content AS VARCHAR2(4000)) into content;
      end;

You will not have error in that way.