Querying a Table with XMLType

[Steve]

I have on my machine

Windows XP Professional
Oracle 10g Release 2 (10.0.2.1), Client And Server
DOA - 4.0.7.1
Delphi 6

I have created a table in Oracle called steve_test

Name Type Nullable Default Comments
----- ----------- -------- ------- --------
ID1 NUMBER Y
BLOB1 SYS.XMLTYPE Y

I have dropped a Toracledataset on a delphi form. which has the Query Select * from steve_test.

I get the following error

ORA-00600 internal error code, arguments : [kolrrdl:0rfc], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_LOB", line 803
ORA-06512: at line 7

Can anybody help with this?

Stephen

 

[Marco Kalter]

Can you use the Oracle Monitor to check which statement is executed when this error occurs?

 

[Steve]

declare
doa_object sys.xmltype;
xmldata clob;
begin
doa_object := :doa_object;
xmldata := doa_object.getclobval();
sys.dbms_lob.trimxmldata, 0);
sys.dbms_lob.appendxmldata, xmldata);
end;

This is what the Oracle Monitor runs when the error Occurs, hope this helps

 

[Marco Kalter]

Okay, we'll check it out.

 

[AlexA]

Hi, this is a big issue for us too so it will be good to get your response. Any idea on when we might expect a solution/patch/work-around??

Alex

 

[Marco Kalter]

We'll first have to find the cause of the problem before we can make any such predictions.

 

[AlexA]

In the meantime Marco, can you suggest any work arounds? Do we go back to a previous version, for example? Any ideas would be very helpful.

thanks
Alex

 

[Marco Kalter]

I assume previous versions have the same problem.

 

[ldsandon]

Oracle bug 4745114 (ORA-600 [KOLRRDL:0RFC] BEING IN IN XML/XLOB PL/SQL BLOCK) says:

The testcase effectively runs the following code twice :
.
OCIDescriptorAlloc( ... OCI_DTYPE_LOB ...
OCILobCreateTemporary( ... OCI_TEMP_CLOB,TRUE,OCI_DURATION_SESSION);

Uses this temp clob in the PLSQL block:

declare
xmldata clob := 'x';
begin
sys.dbms_lob.trimxmldata, 0);
sys.dbms_lob.appendxmldata, xmldata);
end;

OCILobFreeTemporary(svchp,errhp,clob);
OCIDescriptorFree((dvoid *) clob, (ub4) OCI_DTYPE_LOB);

On the second execution the "TRIM" operation hits the ORA-600.

Workaround/s
~~~~~~~~~~~~
The problem only occurs when there has been an OCILobFreeTemporary() of the temp lob so a workaround can be to cache temporary lobs on the client side. eg: Allocate and CreateTemporary of the temp LobLocator once only on the client and reuse that locator in each call rather than keep allocating / freeing.

Hope this helps.