Sending CLOB data from Tibco (Java) to PL/SQL stored procedures

On my current assignment I am working on interfaces between Tibco and Oracle PL/SQL applications. Tibco is a middleware solution that implements a messaging based interface solution between different software systems. For the interfaces I have implemented a generic bridge between Tibco and Oracle. This PL/SQL implementation provides one stored procedure that Tibco can call. This procedure will then determine the message type based on the root element of the incoming XML message. The interface will then lookup, from it’s configuration table, a specific stored procedure that can handle the actual XML message.

The argument of my stored procedure is a CLOB holding the incoming XML message. Then I called this procedure directly from PL/SQL there was no problem. Calling this procedure from Tibco (a Java based application) the call resulted in an "ORA-24805: LOB type mismatch" error. <!–more>This looked strange and google didn’t seem very helpfull in this. The first thing I did with the CLOB data was to create an xmltype variable holding the actual XML data. This way we are able to use all XML DB functionality on the data. The actual call generating the ORA message was:

procedure handleXML(p_xml in clob)
  l_XML xmltype;
begin
  l_XML := xmltype.createXML(p_xml);
end;

I then looked at the description of the DBMS_LOB package and read about lob locators. You can think of a LOB locator as a pointer to the actual location of the LOB value. So it looks like Oracle’s xmtype methods cannot access the CLOB data that is provided by Tibco. The workaround was to copy the clob data in a temporary clob that is available in the stored procedure. This way the lob locator has the correct type. Of course there is some (memory) overhead, but the XML messages are not that long te be any problem. The fixed code looks like:

procedure handleXML(p_xml in clob)
  l_XML xmltype;
  l_temp_XML      clob;
begin
  DBMS_LOB.CREATETEMPORARY(l_temp_XML, false, 2); — 2 makes the temporary only available in this call
  DBMS_LOB.COPY (l_temp_XML, p_xml, dbms_lob.getlength(p_xml),1,1);
  l_XML := xmltype.createXML(l_temp_XML);
end;

The second example can be called perfectly from Tibco.