Hello I have a method that calls a stored procedure, but I'm getting the next error:
"GRAVE [cl.auter.sictrav.daos.sictrav.ControladorDao] (MSC service thread 1-1) java.sql.SQLException: ORA-08103: object no longer exists : java.sql.SQLException: ORA-08103: object no longer exists"
referring to this line ResultSet rs = (ResultSet)cStmt.getObject(3);
My code is
public List obtenerBitsEstadoDao(Controlador controlador) {
DBSictrav conn = new DBSictrav();
List bitsestado = new ArrayList();
Connection connection = null;
try {
connection = conn.getConection("");
String query = "{ call pkg_plan_sic.pg_obt_bit_est_crc_ctl(?,?,?,?,?)}";
try (CallableStatement cStmt = connection.prepareCall(query)) {
cStmt.setString(1, "CT");
cStmt.setInt(2, controlador.getId());
cStmt.registerOutParameter(3, OracleTypes.CURSOR);
cStmt.registerOutParameter(4, OracleTypes.NUMBER);
cStmt.registerOutParameter(5, OracleTypes.VARCHAR);
cStmt.executeQuery();
if (cStmt.getInt(4) == 0) {
try
{
ResultSet rs = (ResultSet)cStmt.getObject(3);
while (rs.next()) {
BitEstado bc = new BitEstado(rs.getString("BIT_CRUCE"));
bc.setFaseReal(rs.getString("BIT_CONTROLADOR"));
bc.setPosicion(rs.getInt("POSICION"));
bc.setControlador(controlador);
bitsestado.add(bc);
}
} catch (Exception e) {
logger.log(Level.SEVERE, e.toString(), e);
}
}
else
{
throw new SictravDaoException((String) cStmt.getObject(5), cStmt.getInt(4));
}
} catch (Exception e) {
logger.log(Level.SEVERE, e.toString(), e);
}
} catch (Exception e) {
logger.log(Level.SEVERE, e.toString(), e);
} finally {
try {
connection.close();
} catch (SQLException ex) {
Logger.getLogger(PlanDao.class.getName()).log(Level.SEVERE, null, ex);
}
}
return bitsestado;
}
The Stored Procedure
procedure pg_obt_bit_crc_ctl(
p_tipo in varchar2,
p_id in number,
p_cur_lis out cur_ref,
p_cod_err out number,
p_des_err out varchar2) is
--
v_string varchar2(32000);
v_ind_whe boolean := false;
v_num_lin varchar2(10);
--
cursor bit_por_cr is
select distinct c.id_cruce,
t.id_masccontrol,
16 - instr(dec2bin(bit00), '1') + 1 F1
c.codigo codigo
from tbl_mascara_control m,
tbl_controlador t,
tbl_cruce c
where m.id_masccontrol = t.id_masccontrol
and c.id_controlador = t.id_controlador
and c.id_cruce = p_id;
--
cursor bit_por_ct is
select t.id_controlador id_cruce,
t.id_masccontrol,
16 - instr(dec2bin(bit00), '1') + 1 F1,
16-instr(dec2bin(bit08),'1')+1 RR
//more similar lines
from tbl_mascara_control m,
tbl_controlador t
where m.id_masccontrol = t.id_masccontrol
and t.id_controlador = p_id;
--
v_fase_ini number;
v_num_ctl number;
v_num_fase number;
v_ind_fase_check number;
posIni number;
posFin number;
v_idx_pal number;
v_cnt number;
v_num_paralelo number;
--
begin
-->Borramos los datos de la tabla temporal orientada a la transaccion...
commit;
--
if p_tipo = 'CT' then
--
for c in bit_por_ct loop
if (c.f1 is not null) then
insert into tbl_tmp_bits_cruce(id_cruce, bit_cruce, bit_controlador, posicion) values(c.id_cruce, 'F1', 'F1', c.F1);
end if;
//More similar lines ...
if (c.PR1 is not null) then
insert into tbl_tmp_bits_cruce(id_cruce, bit_cruce, bit_controlador, posicion) values(c.id_cruce, 'PR1', 'PR1', c.PR1);
end if;
if (c.PR2 is not null) then
insert into tbl_tmp_bits_cruce(id_cruce, bit_cruce, bit_controlador, posicion) values(c.id_cruce, 'PR2', 'PR2', c.PR2);
end if;
if (c.PR3 is not null) then
insert into tbl_tmp_bits_cruce(id_cruce, bit_cruce, bit_controlador, posicion) values(c.id_cruce, 'PR3', 'PR3', c.PR3);
end if;
if (c.PR4 is not null) then
insert into tbl_tmp_bits_cruce(id_cruce, bit_cruce, bit_controlador, posicion) values(c.id_cruce, 'PR4', 'PR4', c.PR4);
end if;
--
if (c.RR is not null) then
insert into tbl_tmp_bits_cruce(id_cruce, bit_cruce, bit_controlador, posicion) values(c.id_cruce, 'RR', 'RR', c.RR);
end if;
--
// More similar ifs
--
end loop;
end if;
--
open p_cur_lis for
select b.id_cruce, b.bit_cruce, b.bit_controlador, b.posicion,c.codigo
from tbl_tmp_bits_cruce b,tbl_cruce c
where b.id_cruce=c.id_cruce(+)
order by b.posicion ;
--
--> ahora recorremos el cursor...
v_num_lin := '60';
p_cod_err := 0;
p_des_err := '';
--
exception
when others then
p_cod_err := -20000;
p_des_err := 'Error al obtener Bits por cruce o controlador';
end pg_obt_bit_crc_ctl;
I'm getting this error and I dont know why or how to fix it. I tried with ojdbc6 and ojdbc14 same result. some suggestion?
The problem is that the cursor returned by your stored procedure is valid only within the same transaction in which the SP itself runs. If your connection is operating in autocommit mode then that transaction ends before the executeQuery() method returns. Out parameters of other types survive the end of the transaction, but the cursor ceases to be valid.
You can address this problem by taking manual control of the transaction boundaries. To do so, you must ensure that the connection is not in autocommit mode, and you must be certain to manually commit the transaction or roll it back before your method returns. Here's an outline:
try {
Connection connection = conn.getConnection();
try {
CallableStatement cStmt;
connection.setAutoCommit(false);
cStmt = connection.prepareCall(query);
// ... register parameters ...
try {
cStmt.executeQuery();
// ... extract and process SP out parameters ...
connection.commit();
} catch (Exception e) {
connection.rollback();
// ... other handling ...
}
} finally {
connection.close();
}
} catch (SQLException e) {
// handle it ...
}
Also, programming in full defensive mode would call for restoring the original connection autocommit state before returning, so as to avoid interfering with other uses of the underlying Connection managed by your datasource. I have not modeled that above because the datasource should not require it, but this would be a defense against a plausible current or future bug in the DS.