I get following error when a C++ code calls a stored procedure in oracle which is returning resultset from a GTT (Gloabl temp table):
ORA-08103: object no longer exists.
The procedure is:
create or replace procedure tmptest
po_resultcur OUT ETS_PKG.refcur,
po_returnstatus OUT NUMBER -- 0 = OK, -1 = Error
insert into T#TEMP_MERGETABLE (seqkey) values(1);
open po_resultcur for
select * from T#TEMP_MERGETABLE;
T#TEMP_MERGETABLE is a GTT. The GTT table is defined as transaction-specific(default).
If I create it as non-GTT(ie.regular) table type, the procedure call works returning one record.
Please help. I want to use GTT as the table-type, from where the data will be returned.
I referred to the link given. However, in that issue, a COMMIT was being done before returning the resultset, invalidating the ref cursor.
But, in the current scenario, a COMMIT/ROLLBACK is not done in the procedure. I also tried doing a COMMIT before the "insert into GTT" (to clean up the existing records in GTT), but no luck.
I tried using the session-specific GTT and it is working, but, the problem there is that the data in the GTT never gets deleted (as there is no "delete from GTT"), since the procedure is called several times in the same session. If I do the "delete from GTT" (session-specific GTT), things work. But deleting from GTT would be time-consuming. Basically, I want to replicate the way hash-tables work in sql server.
I don't sure about next analize:
-- Question: do u use dedicated server mode?
-- ur main connect use main usual (tcp/ip) entry in listener.ora and tnsnames.ora files
-- ur connect to external c++ (pro*c) function use EXTPROC entry.
-- when u create context for GTT, it place in PGA area (for main connect entry!!!)
Probably connecttion to pro*c function create another context in its own PGA area because it use another listener entry.
U can reserch this situation if try to get information about user processes in memory:
1. before execution pro*c function
2. when pro*c function execute
> ps -lf -u oracle (this is for linux)
Seems like OLEDB driver in oracle is doing an implicit autocommit, invalidating the cursor. Is there a way to not have OraOLEDB do an implicit commit?
OleDB not works in this case.
I have described more serious internal oracle problem, as possible cause.