Retrieving data from stored procedures in Toplink

Objective: do some hard stuff in a stored procedure and make sure that Toplink cache is aware of the modified entities.

The solution is quite obvious: return a cursor to a temporary table which contains entity names and IDs which were modified. Easier said than done, Oracle does not allow creation of temporary local tables in procedures for some reason (I’ve done that many times within MySQL for example). There is some rudimentary type which they call ARRAY and this type can be used to achieve the desired outcome. I’ve spent a lot of time and effort (and consulted many of our Oracle gurus for that matter, now I understand why these guys are so grim) to make it work: you cannot just add a value to a list - you will have to write a function which merges 2 arrays and track the array pointer all the time :) then you’ll have to dance woodoo dances to return the values correctly to Toplink. Here is how it went on…

So to add IDs and table names we will need an array:

create or replace
TYPE OIDS AS TABLE OF VARCHAR(255);

OIDS is an array type (don’t let the table keyword fool you, it’s not a table, its an old school array), which we return from the procedure. To add redords to this array here is a merger function:

create or replace
FUNCTION APPEND_TO_ARRAY(source in oids, destination in out nocopy oids, i in number) return number as
new_index number := i;
BEGIN
  IF (source is not null and source.COUNT > 0)
  THEN
      destination.EXTEND(source.COUNT);
      FOR ind IN source.FIRST .. source.LAST
      LOOP
        destination(i + ind) := source(ind);
      END LOOP;
      new_index := i + source.LAST;
  END IF;
  return new_index;
END APPEND_TO_ARRAY;

It copies elements from source to destination, and i is an index of last element from destination.

And here comes the data cruncher, which produces output:

create or replace
procedure myproc(modified_oids OUT NOCOPY oids) is

temp_oids oids;
v_indx number:=0;

// do something

update mytable t 
set t.mycolumn = 'bla-bla-bla'
returning t.ID || '_mypackage.MyEntity' bulk collect into temp_oids; v_indx := APPEND_TO_ARRAY(temp_oids, modified_oids, v_indx);

// this is how every updated looks like

Oracle has a nice feature, you can return affected rows from an update, we utilize this when returning table names and modified record IDs into a temporary table, then we merge the temporary table with the output table every time we modify another table. Unfortunately this is the only reasonable way for retrieving output data that I am aware of.

And finally the Java code to invoke procedure and retrieve the data:

    StoredProcedureCall call = new StoredProcedureCall();
    call.setProcedureName("MYPROC");
    call.addNamedOutputArgument("modified_oids", "modified_oids", Types.ARRAY, "OIDS");

    DatabaseQuery query = new ValueReadQuery();
    query.setCall(call);

    Object result = toplinkSession.executeQuery(query);
    Object[] resultArray = (Object[])((Array)result).getArray();

In response array we get Strings.

First published on the old anydoby.com blog on 2009-02-02.