ORA-08103 error on procedure

Question

I have a procedure on Oracle that works perfectly 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 form my .Net app (using ODP.NET), I get the error:

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 thrown on the dr.read but If I check the dr object, on the hasRows property I can see the ORA-08103: object no longer exists error.

What can be wrong? One detail is that I have a similar procedure that follows pretty much the same logic (of returing a cursor) and works fine.

Answer 1

Does the query for the returned cursor involve temporary tables? You can shoot yourself in the foot if you return a cursor involving temporary tables with the ON COMMIT DELETE ROWS options and then commit the transaction before you have retrieved the cursor data.

The COMMIT easily happens because ODP.NET by default works in auto commit mode.

To fix it,

• either turn off auto commit,
• or use temporary tables with the ON COMMIT PRESERVE ROWS options (instead of ON COMMIT DELETE ROWS),
• or use regular tables.

Answer 2

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

It will also keep the commits from happening before you retrieve all the data.