Error ORA-08103 in the procedure

Question

I have an Oracle procedure that works fine if I call it from SQL Developer using this code:

VARIABLE x REFCURSOR exec MY_PROCEDURE('par1', 'par2', 'par3', 'par4' ,:x); PRINT x; 

If I try to call it the form of my .Net application (using ODP.NET), I get an error message:

 Oracle.DataAccess.Client.OracleException ORA-08103: object no longer exists 

This is the code I use to call it:

 OracleConnection con = new OracleConnection(); con.ConnectionString = dbConnectionString; //string with the connectio. It is fine because I can connect OracleCommand cmd = new OracleCommand("MY_PROCEDURE", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; cmd.Parameters.Add(new OracleParameter("par1", OracleDbType.Varchar2)).Value = var1; cmd.Parameters.Add(new OracleParameter("par2", OracleDbType.Varchar2)).Value = var2; cmd.Parameters.Add(new OracleParameter("par3", OracleDbType.Varchar2)).Value = var3; cmd.Parameters.Add(new OracleParameter("par4", OracleDbType.Varchar2)).Value = var4; OracleParameter ref_cursor = new OracleParameter(); ref_cursor.OracleDbType = OracleDbType.RefCursor; ref_cursor.Direction = ParameterDirection.Output; cmd.Parameters.Add(ref_cursor); con.Open(); OracleDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { ... } 

The cmd.ExecuteReader command actually "works", the application exception is dr.read in dr.read , but if I check the dr object, I can see the error ORA-08103: object no longer exists in the hasRows property.

What could be wrong? One detail is that I have a similar procedure that pretty much matches the same logic (to save the cursor) and works fine.

Answer 1

Does the query for the returned cursor have temporary tables? You can shoot in the foot if you return the cursor with temporary tables with ON COMMIT DELETE ROWS parameters, and then complete the transaction before you get the cursor data.

COMMIT happens easily because ODP.NET defaults to auto-commit.

To fix this

• either disable auto-commit
• or use temporary tables with ON COMMIT PRESERVE ROWS parameters (instead of ON COMMIT DELETE ROWS),
• or use regular tables.

Answer 2

You can also create a transaction for your connection and set up the transaction in the OracleCommand object.

It will also prevent commits before you retrieve all the data.