Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Implicit Cursor (Oracle 10G)
icon10.gif  Problem with Implicit Cursor [message #301251] Tue, 19 February 2008 17:21 Go to next message
gecs
Messages: 5
Registered: December 2007
Location: Lima
Junior Member

Embarassed Hi everybody, I have a problem with Implicit Cursors. My Java App call a Function into a While. When the bucle is in the row 946, it shows the Exception:
--------------------------------------------------------------
Error SQLException: ORA-01000: maximum open cursors exceeded
--------------------------------------------------------------

*)This is the JAVA code:
==========================
while(rs.next()){
resFunction = Util.callSpecialLogicDW(Constantes.SP_LOGICA_EMPLEADO_DW,
rs.getString("CodigoPersonal"), conn); // here call the Function
resFunction, diferenciador);
sbfFila = new StringBuffer();
sbfFila.append(rs.getString("DocumentoIdentidad") + 
Util.getTipDocIdenSAPToBCP(rs.getString("TipoDocumento")) + 
resFunction +
rs.getString("MatriculaTrabajador") + 
rs.getString("Secuencial") + rs.getString("DescripcionParentesco") + 
Util.formatearFechaAAAAMMDD(rs.getString("FechaProceso"),"/") + "\n");
bwArchivo.write(sbfFila.toString());
contFilas++;
Archivo.LogWF("Fila Numero: " + contFilas, diferenciador);
}

*)Here is code Function (Oracle):
================================
FUNCTION fn_ObtenerCodigoEmpleadoDW(codEmpSap CHAR) 
RETURN CHAR
IS

codDW CHAR(9);
correlativo INT := 0;

BEGIN

-- Se busca el codigo DW
SELECT CODEMPLEADODW INTO codDW FROM GAHR_EMPLEADO_SAP WHERE CODEMPLEADOSAP = codEmpSap;

RETURN codDW;

EXCEPTION 

WHEN NO_DATA_FOUND THEN
-- Se genera el nuevo codigo
SELECT SEQ_EMPLEADO_DW.NEXTVAL INTO correlativo FROM SYS.DUAL;

-- Se arma el codigo DW
codDW := 'DW' || LPAD(correlativo,7,'0');

-- Se inserta el codSAP y el nuevo codigo DW
INSERT INTO GAHR_EMPLEADO_SAP VALUES(codEmpSap, codDW);

COMMIT;

RETURN codDW;

WHEN TOO_MANY_ROWS THEN
return 'too_many';

WHEN OTHERS THEN
return 'others';
END;

-------------------------------------------------------------------------------------
It has to do something with the database, the connection?.....please help me!!! Sad
If someone can help me. I'll be very grateful.
Re: Problem with Implicit Cursor [message #301303 is a reply to message #301251] Wed, 20 February 2008 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to close the cursors (in your application) from time to time if you don't want to exceed the limit.

Regards
Michel
Re: Problem with Implicit Cursor [message #301328 is a reply to message #301303] Wed, 20 February 2008 01:22 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
This belongs within the Java-thread/forum

But as Michel suggest: you'll need to close:
- ResultSet
- Statement
- Connection

Each time you're finished with it.
Closing the Connection doesn't necessarily mean that the actual connection to the database is closed, but merely you'll return the Connection as available to the Connection Pool (if I'm not mistaken)

ResultSet rs ;
PreparedStatement stmt ;
while ( rs.next() ) {
...
}
rs.close() ;
stmt.close() ;
Re: Problem with Implicit Cursor [message #301474 is a reply to message #301251] Wed, 20 February 2008 16:48 Go to previous message
gecs
Messages: 5
Registered: December 2007
Location: Lima
Junior Member

Thanks, Marcs and Michel you're rigth. I forgot close the CallableStatement object ( in my App )..... Embarassed
Previous Topic: ORA-00904: Entity.Record_type_code: invalid Identifier
Next Topic: Conditional full outer join
Goto Forum:
  


Current Time: Thu Dec 08 06:27:17 CST 2016

Total time taken to generate the page: 0.09180 seconds