TXMLTYPE OCI-215000

[stevesco_30]

I have installed the following software

Oracle 10g version 2 on the Server
Oracle 10g Release 2 Instant Client
Direct Oracle Access v4.0.7.1

The following code worked before upgrading to new version of Oracle 10g Release 2 and New Version of Direct Oracle Access.

lqInsBlob := TOracleQuery.Create(self);
with lqInsBlob do
begin
session := sysfdmodule.database;
sql.Clear;
sql.add('Insert into XMLBLOB');
sql.add('(Blb_blob, Blb_id, cat_code)');
sql.add('values Blb_blob, :Blb_id, :cat_code)');
// sql.add('returning Blb_blob into :Blb_blob');

lvLob := TXMLType.Create(sysFDModule.database, lsXML);
DeclareVariable('Blb_id', otinteger);
DeclareVariable('cat_code', otstring);
DeclareVariable('blb_blob', otobject);

SetComplexVariable('Blb_blob', lvLob);
SetVariable('Blb_id', uvBlb_id);
SetVariable('cat_code', uvblb_catcode);

Execute;

On the Line

lvLob := TXMLType.Create(sysFDModule.database, lsXML);

It falls over, with a OCI-21500 internal error.

Can you help me solve this problem?

 

[Marco Kalter]

Is the Instant Client the only Oracle Client om this PC?

 

[stevesco_30]

Should be! It used to have a Orcale 9i Client on it, which I think I un-installed, (could have missed out something on the machine, though!)

I will investigate further and get back to you

 

[stevesco_30]

I have uninstalled Oracle 9i through the oracle uninstall routine, but have noticed a few Oracle 9i settings in the Registry, It seems Oracle un-install for Oracle 9i does not do a clean un-install. Can you help be recommending what settings need to be removed to get the TXMLTYPE.create working correctly

 

[Marco Kalter]

You could try to rename the hkey_local_machine\software\oracle registry section. This effectively disables all other Oracle Client software.

 

[ldsandon]

I am having the same problem when trying to write an XML field. I have it on a client machine with 10.2.0.2 (was upgraded from 9i, 9i uninstalled, Windows 2000 Server SP4) installed. Server is 10.2.0.2 (Windows 2003 SP1).

The complete error is:

Oracle error 21500, OCI-21500: internal error code, parameters: [58], [], [], [], [], [], [], []
OCI-21500: internal error code, parameters: [kghfrh:ds], [0x146D3D8], [], [], [], [], [], []

Strangely it does not happen in these situations:
9.2.0.6 -> 9.2.0.6
9.2.0.6 -> 10.2.0.2
making me think it's a client issue. Metalink has bug 3362205, but it doesn't look to apply in this situation.

Doa is 4.0.7.1, Delphi 7. I have the full client installed.

I could submit a sample application and xml file, if needed.

 

[ldsandon]

I've tracked down the error in TXMLType.SetXML().

It fails when it invokes the query calling:

:doa_object := sys.xmltype.createxml(:xmldata);

The OCI call returns OCI_ERROR. It happens only with a 10g client, I upgraded my 9.2.0.6 machine and the error appeared there too.

 

[ldsandon]

I tried a similar code in sqlplus, with the same XML that gives the error, and in sqlplus it works:

declare
  C CLOB;
  S VARCHAR2(4000);
  X sys.xmltype;
begin
  dbms_lob.createtemporary(C, TRUE);
  S :=
   '<?xml version="1.0" encoding="windows-1252" '||
   ' standalone="yes"?><CHAT><PACKET command="CHG" direction="O">' ||
   '<DATA key="command">CHG</DATA>' ||
   '<DATA key="TrID">12</DATA>' ||
   '<DATA key="2">IDL</DATA>' ||
   '<DATA key="3">32</DATA>' ||
  '</PACKET>' ||
  '<PACKET command="CHG" direction="I">' ||
  '  <DATA key="command">CHG</DATA>' ||
  ' <DATA key="TrID">12</DATA>' ||
  '  <DATA key="2">IDL</DATA>' ||
  '  <DATA key="3">32</DATA>' ||
  '</PACKET>' ||
  '<PACKET command="" direction="I"/></CHAT>';
  dbms_lob.Write(C, Length(S), 1, S);
  X := sys.xmltype.createxml(C);
  dbms_output.put_line(X.getStringVal);
  dbms_lob.freetemporary(C);
end;

I think 10g does not like somehow the object passed in :doa_object, but OCI programming is beyond my scope and it's just a guess.

 

[Marco Kalter]

That is indeed the difference. In Direct Oracle Access the XML object is a bind variable passed from the client to the server and back. In the SQL*Plus example it's all PL/SQL on the server.

 

[riverlee]

This error is Oracle Client 10's bug!

 

[ldsandon]

Marco, can you confirm it is a 10g bug?
My code was to test where the problem is, this way I was able to send the XML as a CLOB and write it to the database, a workaround is better than nothing - especially when I have only two weeks before the customer's acceptance test

 

[riverlee]

Hello,ldsandon
I found a method to solve this bug!

 

[riverlee]

lqInsBlob := TOracleQuery.Create(self);
with lqInsBlob do
begin
session := sysfdmodule.database;
sql.Clear;
sql.add('Insert into XMLBLOB');
sql.add('(Blb_blob, Blb_id, cat_code)');
sql.add('values (sys.xmltype.createxmlBlb_blob), :Blb_id, :cat_code)');

lvLob := TLOBLocator.CreateTemporary(sysFDModule.database, otCLOB, True);
DeclareVariable('Blb_id', otinteger);
DeclareVariable('cat_code', otstring);
DeclareVariable('blb_blob', otclob);

SetComplexVariable('Blb_blob', lvLob);
SetVariable('Blb_id', uvBlb_id);
SetVariable('cat_code', uvblb_catcode);

Execute;

 

[ldsandon]

Thanks Riverlee, I've done something alike

 

[riverlee]

No Thanks!