Java: Calling a stored procedure in an oracle database

Question

EDIT: While some of the answers in this question may help others with different problems, the solution was actually related to some bug with the auto-commit feature on a database connection! Forcing a commit after executing the query caused the database to reflect the changes, thus the code shown below IS the correct way to call a stored procedure of this type

I'm trying to call a simple stored procedure in an oracle database.

The procedure looks like this:

procedure clear_orderProcDtlByOrdId(p_order_id in order_header.order_id%type,
                                    p_transaction_id in sl_order_processing_dtl.transaction_id%type DEFAULT NULL,
                                    p_item_action_id in sl_order_processing_dtl.item_action_id%type DEFAULT NULL )
...

The java code I'm having trouble with looks like this

    try 
    {
        CallableStatement storedProc = conn.prepareCall("{call PKG_PI_FRAUD.clear_orderProcDtlByOrdId(?)}");
        storedProc.setString(1, orderID);
        storedProc.execute();
    } 
    catch (SQLException e) 
    {
        e.printStackTrace();
    }

I'm not receiving any errors at all, however there are no database changes being reflected. When I run the procedure in SQL Developer I see results. I thought it might be because of a commit issue, but the connection I have established is in auto-commit mode.

Any help would be appreciated!

Answer 1

While some of the answers in this question may help others with different problems, the solution was actually related to some bug with the auto-commit feature on a database connection! Forcing a commit after executing the query caused the database to reflect the changes, thus the code shown in the question IS the correct way to call a stored procedure of this type!