TLOBLocator.CreateTemporary and Ora 22922 on Oracle 10

[Lars J]

I have got a java-routine on my server that generates text, and returns it in a CLOB to my DOA-application. When trying to access the return value, it runs successfully on Oracle 9.2.0.6.0, but fails on Oracle 10.2.0.3.0 with ORA 22922.

I have been able to create a minimal testcase:

The serverside-routine is:

create or replace package test_lob is
procedure return_clob(output clob) as
language java name 'test_java.returnClob(oracle.sql.CLOB)';
end test_lob;

create or replace package body test_lob is
end test_lob;

create or replace and compile java source named test_java as
import java.io.*;

public class test_java
{
public static void returnClob(oracle.sql.CLOB Output) throws Exception
{
Writer out = Output.getCharacterOutputStream();
out.write("Successful return from DB-routine");
out.close();
}
}

A Delphi-routine to test this is:

procedure TForm1.TestTemporary(UseDOATemp: boolean);
var
l_outputLobLocator: TLobLocator;
l_oracleQuery: TOracleQuery;
begin
l_outputLobLocator := nil;
l_oracleQuery := nil;

try
if (UseDOATemp) then
l_outputLobLocator := TLOBLocator.CreateTemporary(FOracleSession, otClob, False)
else
l_outputLobLocator := TLOBLocator.Create(FOracleSession, otClob);

l_oracleQuery := TOracleQuery.Create(nil);
l_oracleQuery.Session := FOracleSession;

if (UseDOATemp) then
l_oracleQuery.SQL.Text := 'begin test_lob.return_cloboutput); end;'
else
l_oracleQuery.SQL.Text := 'declare l_temp clob; begin dbms_lob.createtemporary(l_temp, true, dbms_lob.session); test_lob.return_clob(l_temp); utput := l_temp; end;';

l_oracleQuery.DeclareVariable('output', otClob);
l_oracleQuery.SetComplexVariable('output', l_outputLobLocator);

l_oracleQuery.Execute;

ShowMessage(l_outputLobLocator.AsString);
finally
l_outputLobLocator.Clear;
l_outputLobLocator.Free;
l_oracleQuery.Free;
end;
end;

When I run this routine against Oracle 9.2, it runs successfully in both cases, but when I run it against Oracle 10.2, it fails when I try to access the value of the LOB-locator after execution when I use CreateTemporary to create the temporary LOB. Creating the temporary LOB in the SQL-string, and accessing it as a normal LOB does not create an error.

I can reproduce the error in PL/SQL-Developer on Oracle 10.2. If I in PL/SQL-Developer test the serverside-routine, and use a temporary CLOB as return value, it fails. On Oracle 9.2 it runs OK.

I have only been able to test this with Oracle Client 9.2 so it might have something to do with the OCI-version.

Regards
Lars J

 

[Lars J]

Sorry, but I finally found the reason for my problem. It should not be possible to change the content of a parameter from a Java-procedure unless it is an array, so my Java-procedure should look like this:

public static void returnClob(oracle.sql.CLOB Output[]) throws Exception
{
Writer out = Output[0].getCharacterOutputStream();
out.write("Successful return from DB-routine");
out.close();
}

and the package:

create or replace package test_lob is
procedure return_clob(output in out clob) as
language java name 'test_java.returnClob(oracle.sql.CLOB[])';
end test_lob;

For some reason this was accepted in Oracle 9.2.

Regards
Lars J