I've got a stored procedure, and I'm calling it using the standard syntax (I say that, but I've never called a procedure before).
The stored procedure, when called through Toad or SQLDeveloper, it does return the correct value (which is a number). The stored procedure pulls data from one table and inserts it into another. The procedure does work, and is working when I call it, it's simply not returning the correct value.
However, when I use the below code, I get 0 when using getInt (which is the equivalent of null):
String testCallable = "{call PKG_RING_EDIT_CORE_TABLES.move_core_to_preload(?,?)}";
cstmt = con.prepareCall(testCallable);
cstmt.setInt(1, 196);
cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.NUMBER);
Integer rows = cstmt.executeUpdate();
System.out.println("Rows affected " + rows);
Integer resultingId = cstmt.getInt(2);
System.out.println("Pulled record from core table. New pre_record_load_id = " + resultingId);
If anyone could give me any guidance as to why this is returning null, then it would be greatly appreciated. I appreciate it may be difficult without seeing the procedure code (it's rather long, so am not posting it), however any guidance is great.
Thanks.
Edit:
Here is the declaration of the procedure:
procedure move_core_to_preload (p_encounter_id_i_nr in encounters.encounter_id%type
, p_pre_rec_ld_id_o_nr out pre_record_load.pre_record_load_id%type)
Here is the section that returns the value from the proecure:
p_pre_rec_ld_id_o_nr := lv_PRE_RECORD_LOAD_ID_nr; -- pass out PK id for inserted record