how to write a oracle function to update tables on different schemas

Question

hello i'm porting a wbsphere application to tomcat, i have to work on two database on the same server and i've integrated tomcat with atomikos transactionessential. This is my first project with jta, and the oracle dba has told me i don't need xa and two phase commit because the schemas are on the same server. So i've used the non-xa approach with atomikos. the following code on a single schema works fine (commit and rollback as expected):

utx.begin();
conn = //get connection 
if (sAzione.equals("1")) 
sql = "UPDATE parametri set valore =to_char(sysdate,'dd/mm/yyyy HH24:MI:ss') where id_parametri= 9 ";
//execute query
sql = "SELECT SEQ_LOTTO.nextval id FROM dual";
    //other operations
sql = "INSERT INTO LOTTO (id_lotto, numero_lotto, id_area, id_stato_lavorazione, id_autore, id_tipo)";
sql = sql + " VALUES (" + id + ", " + numero + ", " + request.getParameter("idArea") + ",1,"+ session.getAttribute("id_anagrafica")+ "," + request.getParameter("idTipo") + ")";
//execute import and release connection
utx.commit();

in another place , the following oracle function gets called and try to change both schemas, and it returns the code 1 . I don't know pl-slq but it appears to me the return value would mean there has been an exception at first delete , yet the second delete gets executed and committed . Someone could explain me the meaning of this function ? below is the function and the code that calls it

    create or replace FUNCTION FN_ELIMINA_RACC (idracc IN NUMBER, idlotto IN NUMBER)
   RETURN NUMBER
IS
   retvalue   NUMBER (1);
BEGIN
   retvalue := 1;

   DELETE FROM npa_collaudo.documento_raccomandata
         WHERE id_raccomandata = idracc;

   retvalue := 2;

   DELETE FROM raccomandata_out
         WHERE id_racc_out = idracc;

   retvalue := 3;

   IF idlotto != 0
   THEN
      UPDATE lotto
         SET numero_racc = numero_racc - 1
       WHERE id_lotto = idlotto;
   END IF;

   retvalue := 0;
   COMMIT;
   RETURN retvalue;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN retvalue;
END;

//the calling code
    utx.begin();
         //get connection
         sql = "FN_ELIMINA_RACC(" + idRacc + ", " + idLotto + ");";
                ret = connessioneDB.eseguiSP(sql);
         if (!(ret == 0)){
    throw new Exception("exception");
    utx.commit();
//since it returns 1 an exception is raised and rollback gets called

thank you in advance for any help

EDIT: investigating further into this (awful) code , and thanks to your answers ,i've found this into the infamous "eseguiSP" :

//strSQL is "FN_ELIMINA_RACC(..." 
DBOracle dbType = new DBOracle();
String SQL = "";
int retValue = 0;
SQL = " DECLARE ";
SQL = SQL + " ret NUMBER; ";
SQL = SQL + " BEGIN ";
SQL = SQL + " ret := " + strSQL; 
SQL = SQL + " END; ";
try {
stmt = conn.prepareCall(SQL);
retValue = stmt.executeUpdate(SQL); 
} catch (SQLException e) {
//retValue = false;
}
return retValue;

And i've changed it to:

c = ds.getConnection();
java.sql.CallableStatement cstmt = c.prepareCall("{?=call FN_ELIMINA_RACC(?,?)}");
cstmt.registerOutParameter(1,java.sql.Types.INTEGER);
cstmt.setInt(2, idRacc);
cstmt.setInt(3, idLotto);
cstmt.execute();
ret = cstmt.getInt(1);

now it works fine (or at least returns 0).Why the old piece of code always returned 1 even if it deleted records from raccomandata_out?

Answer 1

Since the function is returning 1, that would indicate that the first delete is throwing an exception. This causes control to be switched to the EXCEPTION block, which simply returns. None of the other code after the first delete should be executed at all.

The exception handler is horrible, in that it catches any exception, discards it, and returns a flag value that tells you very little about what happened. It's only slightly better than WHEN OTHERS THEN NULL;. As this is written, you have no way of knowing what exception occurred. The exception handler should either be removed (so that the calling code can catch and deal with the exception in some way), or rewritten to at least log the actual exception message (SQLERRM) somehow.

The most obvious guess is that the exception is being raised because the schema in which the code is executed does not have delete access to the table in the other schema. One Oracle quirk which might be relevant is that stored PL/SQL code (such as this function) cannot take advantage of access granted via a role. Any access to other schemas' objects must be granted directly to the user.

Answer 2

The exception handler in this procedure is not particularly useful. It is completely hiding the error message that Oracle is throwing. If you eliminate the exception handler entirely, what is the error stack?

My guess is that the owner of the procedure doesn't have privileges to delete rows from the npa_collaudo.documento_raccomandata table. But it's impossible to know that without knowing what exception is actually being raised.

Answer 3

How do you know the function is returning 1? The exception you're throwing isn't reporting the ret value. The call itself may be broken - try removing the trailing ; from the sql string. Although you ought to get a more helpful exception from eseguiSP(sql) if that's the case, but it might be hidden elsewhere in your code (maybe something further up is adding something that makes it looks like a 1 was returned?); and neither delete should take effect, unless it's trying to treat it as two commands and only complaining when it sees the second is null. That sounds unlikely but you never know, so I'd try removing the semi-colon anyway.

Also, you should probably be using bind parameters for the call, not embedding the values in sql.

You also said rollback would be called on exception, and you have utx.commit(), but that's redundant with a commit in the function too.