If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

-- 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