Version: Oracle 10.2.0.3.0
I have a stored procedure that returns a REF CURSOR as an OUT parameter.
This stored procedure is consumed by a ADO.NET layer with connecting pooling enabled.
The query in the stored procedure uses a Global Temporary Table as a Join condition to fetch the data.
Issue is:
The end user of the ADO.NET is getting duplicate data as output from the Stored Procedure.
Details:
Connection Pooling is enabled in ADO.NET. This is reusing the same sessions and therefore fetching duplicate data into the GTT and therefore getting duplicate rows in result.
If connection pooling is disabled, then the results are fine, no duplicate data!
I created the GTT with the syntax:
ON COMMIT PRESERVE ROWS - retain data for the session
This is fetching duplicate data as the same session is being reused due to connection pooling.
I then changed the syntax of the GTT to be transaction specific.
ON COMMIT DELETE ROWS - retain data for a transaction only
The end user of ADO.NET is getting the following error while opening the REF CURSOR
ora-00600:internal error code
arguments [kcbz_check_objd_typ_1],[0],[0],[1],[],[],[],[]
Has anyone come across this situation?