Oracle TopLink, the ORA-00900 error and stored procedures

I was introduced to Oracle’s TopLink ORM package during my second internship at Oracle. Although the API (because TopLink actually became the reference implementation for the JPA 2.0) seemed a bit overwhelming at first, I’ve managed to get a grasp of it quite fast, allowing me to finish the assigned projects in time. The familiarity with the API and with Toplink Workbench, a graphical tool designed to help you map database tables into Java classes (basically JavaBeans), made me use this combination for my diploma project (which I’ve been talking about more or less on my previous posts).

Working on a parser that would extract objects out of some very large text files (about 1.8 million lines) and would then persist them into a DB, I was required to eliminate duplicates. I had two options:

1. parse the file, create an object, verify if the object was persisted by doing a SELECT query in the DB after its attributes (actually TopLink handled this part) and if the result was null persist the new object;
2. parse the file, create objects, persist them, eliminate duplicates after all objects have been parsed using a stored procedure.

While method 1 uses only the sequence values needed for the operation (I’m using sequences for my primary keys), for each INSERT a SELECT has to be made before, which would induce a performance fault. Method 2 on the other hand uses more sequence values than the first one by inserting more rows than actually needed, but even with this issue in mind it turned out to work faster. So there you go, we have a winner.

Using stored procedures has some advantages and as well some disadvantages. The advantages are that nothing is quicker than native database code and this option allows you to write complex operations using a simple implementation language (SQL, PL/SQL) which would be quite hard to write using programming languages. The disadvantages come in the application layer, because these stored procedures aren’t portable: if you change your database engine, let’s say from MySQL to Oracle, chances are that 99.5% you must rewrite those procedures.

In my case the stored procedure doesn’t return any rows, it actually deletes duplicates:

CREATE OR REPLACE
PROCEDURE procedure_name AS
BEGIN
  DELETE FROM table_name a WHERE a.id > (
  SELECT MIN(b.id)
  FROM table_name b
  WHERE
    b.field1 = a.field1 AND
    b.field2 = a.field2 AND
    b.field3 = a.field3
  );
END procedure_name;

The Java code used for calling this procedure is simple:

public static void deleteDuplicates() {
    Session session = ToplinkSession.getToplinkSession();
    DataModifyQuery dmq = new DataModifyQuery();
    StoredProcedureCall spc = new StoredProcedureCall();
    spc.setProcedureName("procedure_name");
    dmq.setCall(spc);
    session.executeQuery(dmq);
}

where ToplinkSession is a singleton class with a static method for returning a Session (or DatabaseSession) object. Note the highlighted line: if you would use a ReadAllQuery instead of DataModifyQuery you’d get a java.sql.SQLException nested in an oracle.toplink.DatabaseException together with an ORA-00900 error message, even if the generated SQL doesn’t contain errors. This is because the ReadAllQuery expects data to return, while the other doesn’t. So the correct way of calling stored procedures that don’t return data is with the help of DataModifyQuery.

I hope that by having all this cleared out you won’t deal with this error any more.

You might also like: