Hi all,
I am trying to retrieve some data using global temporary tables and odp.net. Here is my environment information:
select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Now , I create the following tables :
create global temporary table gtt_test1
(
gt_one_id number(10,0) NOT NULL
)
on commit delete rows;
create global temporary table gtt_test2
(
gt_two_id number (10,0) NOT NULL
)
on commit delete rows;
create table regular_table_test
(
someid\_1 number(10,0),
someid\_2 number(10,0),
somecolval varchar2(50)
);
commit;
Now I create a procedure inside a package as follows:
PROCEDURE getdatafromtemptables (res1 OUT SYS\_REFCURSOR,
res2 OUT SYS\_REFCURSOR)
IS
BEGIN
DELETE FROM gtt\_test1;
DELETE FROM gtt\_test2;
INSERT INTO gtt\_test1
VALUES (1);
INSERT INTO gtt\_test1
VALUES (2);
INSERT INTO gtt\_test1
VALUES (3);
INSERT INTO gtt\_test1
VALUES (4);
INSERT INTO gtt\_test2
VALUES (1);
INSERT INTO gtt\_test2
VALUES (2);
INSERT INTO gtt\_test2
VALUES (3);
INSERT INTO gtt\_test2
VALUES (4);
insert into regular\_table\_test values (1,2,'blah1');
insert into regular\_table\_test values (1,5,'blah2');
insert into regular\_table\_test values (1,8,'blah3');
insert into regular\_table\_test values (1,3,'blah4');
insert into regular\_table\_test values (2,2,'blah5');
open res1 for
select \* from regular\_table\_test rtt,
gtt\_test1 gt1
where
rtt.someid\_1 = gt1.gt\_one\_id;
open res2 for
select \* from regular\_table\_test rtt,
gtt\_test2 gt2
where
rtt.someid\_2 = gt2.gt\_two\_id;
END;
I then create a ORM for this stored procedure using ODP.NET . The oracle dataaccess.dll version I am using is as shown in below snapshot:
The code for the same is as follows:
namespace TableAdapters
{
using System;
using System.Data;
using System.Collections.Generic;
using Oracle.DataAccess.Client;
using System.Data.Common;
using Oracle.DataAccess.Types;
\[System.Serializable()\]
public partial class GetDataFromTempTablesAdapter
{
public virtual void Execute(ref GetDataFromTempTables\_Params oParams)
{
int i = 0;
try
{
OracleConnection conn = new OracleConnection("User Id=someuser;Password=somepass; Data Source=some\_data\_source;Connection Lifetime=300;enlist=true;");
using (OracleCommand oracle = new OracleCommand())
{
oracle.Parameters.Add("RES1", OracleDbType.RefCursor,ParameterDirection.Output);
oracle.Parameters.Add("RES2", OracleDbType.RefCursor, ParameterDirection.Output);
oracle.CommandText = "SOMAPACKAGE.GETDATAFROMTEMPTABLES";
oracle.CommandType = CommandType.StoredProcedure;
oracle.Connection = conn;
conn.Open();
i = oracle.ExecuteNonQuery();
if (true)
{
OracleDataReader or = ((OracleRefCursor)oracle.Parameters\["RES1"\].Value).GetDataReader();
List\<GetDataFromTempTablesRES1> listGetDataFromTempTablesRES1 =
new List\<GetDataFromTempTablesRES1>();
while (or.Read())
{
GetDataFromTempTablesRES1 objGetDataFromTempTablesRES1 = new GetDataFromTempTablesRES1();
if ((or\["SOMEID\_1"\] != DBNull.Value))
{
objGetDataFromTempTablesRES1.Someid1 = Convert.ToDouble(or\["SOMEID\_1"\]);
}
else
{
objGetDataFromTempTablesRES1.Someid1 = null;
}
if ((or\["SOMEID\_2"\] != DBNull.Value))
{
objGetDataFromTempTablesRES1.Someid2 = Convert.ToDouble(or\["SOMEID\_2"\]);
}
else
{
objGetDataFromTempTablesRES1.Someid2 = null;
}
if ((or\["SOMECOLVAL"\] != DBNull.Value))
{
objGetDataFromTempTablesRES1.Somecolval = Convert.ToString(or\["SOMECOLVAL"\]);
}
else
{
objGetDataFromTempTablesRES1.Somecolval = String.Empty;
}
if ((or\["GT\_ONE\_ID"\] != DBNull.Value))
{
objGetDataFromTempTablesRES1.GtOneId = Convert.ToDouble(or\["GT\_ONE\_ID"\]);
}
else
{
objGetDataFromTempTablesRES1.GtOneId = null;
}
listGetDataFromTempTablesRES1.Add(objGetDataFromTempTablesRES1);
}
or.Close();
oParams.Res1 = listGetDataFromTempTablesRES1;
or = ((OracleRefCursor)oracle.Parameters\["RES2"\].Value).GetDataReader();
List\<GetDataFromTempTablesRES2> listGetDataFromTempTablesRES2 =
new List\<GetDataFromTempTablesRES2>();
while (or.Read())
{
GetDataFromTempTablesRES2 objGetDataFromTempTablesRES2 = new GetDataFromTempTablesRES2();
if ((or\["SOMEID\_1"\] != DBNull.Value))
{
objGetDataFromTempTablesRES2.Someid1 = Convert.ToDouble(or\["SOMEID\_1"\]);
}
else
{
objGetDataFromTempTablesRES2.Someid1 = null;
}
if ((or\["SOMEID\_2"\] != DBNull.Value))
{
objGetDataFromTempTablesRES2.Someid2 = Convert.ToDouble(or\["SOMEID\_2"\]);
}
else
{
objGetDataFromTempTablesRES2.Someid2 = null;
}
if ((or\["SOMECOLVAL"\] != DBNull.Value))
{
objGetDataFromTempTablesRES2.Somecolval = Convert.ToString(or\["SOMECOLVAL"\]);
}
else
{
objGetDataFromTempTablesRES2.Somecolval = String.Empty;
}
if ((or\["GT\_TWO\_ID"\] != DBNull.Value))
{
objGetDataFromTempTablesRES2.GtTwoId = Convert.ToDouble(or\["GT\_TWO\_ID"\]);
}
else
{
objGetDataFromTempTablesRES2.GtTwoId = null;
}
listGetDataFromTempTablesRES2.Add(objGetDataFromTempTablesRES2);
}
or.Close();
oParams.Res2 = listGetDataFromTempTablesRES2;
}
conn.Close();
}
}
catch (System.Exception ex)
{
// Handle any Exception.
// log error
throw;
}
}
}
}
Now, the moment i try to read the ref cursor value on line 32 while(or.read()) , I get an exception stating :
Oracle.DataAccess.Client.OracleException ORA-08103: object no longer exists.
Now after some research it turns out the temp table when created with on commit delete rows does an internal commit somewhere while opening the ref cursors. So the obvious solution is to create it with on commit preserve rows. But our company DBA mandates that all global temporary tables be created with ON COMMIT DELETE ROWS due to performance issues. So what can be done in this case? It is necessary to read both the ref cursors in the same procedure.
Any help appreciated.
Thanks.