Oracle Stored Proc with Global Temp Tables

Posts   
 
    
ianvink
User
Posts: 393
Joined: 15-Dec-2006
# Posted on: 10-Apr-2021 00:30:54   

Hi Guys,

I use the adapter pattern to call Oracle Stored Procedures. They mostly work, but when a stored procedure uses an Oracle global temp table (GTT) I get these kinds of errors:

ORA-01002: fetch out of sequence ORA-08103: object no longer exists

It appears that the data is no longer avilable or something when the cursors try to return the values? When I call the Oracle proc from Toad it works.

I'm looking for a place to start in terms of figuring out why Procs with GTT throws these errors when I call them.

var query2 =RetrievalProcedures.GetShipmentsViaSimpleQueryCallAsQuery(q.UserName, q.OrderNumber, q.TrackingNumber, q.CustomerOrderNumber, ref returnStatus); var reader = await adapter.FetchDataReaderAsync(query2, CommandBehavior.Default, default); items = _mapper.Map<List<Shipment>>(adapter.FetchProjection<DtoShipment>(reader)); reader.NextResult();

ianvink
User
Posts: 393
Joined: 15-Dec-2006
# Posted on: 10-Apr-2021 00:55:37   

Could it be Transaction related?

ianvink
User
Posts: 393
Joined: 15-Dec-2006
# Posted on: 10-Apr-2021 01:05:42   

Yep:

If you just Googled this, to support Oracle Temp Tables in your Procs called by ODP.NET, just wrap the call in a Transaction:

using var adapter = new DataAccessAdapter();
await adapter.StartTransactionAsync(IsolationLevel.ReadCommitted,"TRANS_NEEDED_TO_SUPPORT_TEMP_TABLE");

......
await adapter.CommitAsync(default);