Stored procedure out parameter returning null

1.2k views Asked by At

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
0

There are 0 answers