Global Temp Tables and resultset
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Global Temp Tables and resultset

  1. #1
    Join Date
    Nov 2001
    Posts
    15
    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
    )
    AS
    begin
    insert into T#TEMP_MERGETABLE (seqkey) values(1);
    open po_resultcur for
    select * from T#TEMP_MERGETABLE;
    end;
    /

    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.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287

  3. #3
    Join Date
    Nov 2001
    Posts
    15
    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.

    Please help.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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)

  5. #5
    Join Date
    Nov 2001
    Posts
    15
    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?


  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    OleDB not works in this case.
    I have described more serious internal oracle problem, as possible cause.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width