ORA-08103 Using global temporary table DML(insert, update, select) inside a procedure, select cursor to temp-table shows error object doesn't exists

Question

Basically I need to modify data and store it in a temp table, delete the data every time the procedure is executed after exposing the data. The problem is that the temporary table used in a cursor as result of a procedure shows error

object doesn't exist

I'm trying to get data from a global temporary table with a stored procedure, then truncate the data inside the stored procedure. I'm getting the "no longer exists" error from Oracle db, I tried 3 different ways:

1. Temporary table with ON COMMIT DELETE ROWS; statement, and COMMIT; at the end of procedure.

2. Temporary table with ON COMMIT PRESERVE ROWS; statement, and TRUNCATE TABLE at the end of procedure

3. Regular table with EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_NAME' statement at the end of procedure

All of these options show the same error.

I have 2 tables and I am merging that 2 tables in global temporary table changing some values of records.

Then I am creating a cursor to return a SELECT to the temporary table.

It's here when the error appears.

So, I created the following tables:

CREATE GLOBAL TEMPORARY TABLE REM_ORDENSECCIONES_TPM (
    SE_CONSEC   NUMBER(38,0),
    SE_NOMBRE   VARCHAR2(250 BYTE),
    ET_CONSEC   NUMBER(38,0),
    SE_ORDENS   NUMBER(38,0),
    DI_CONSEC   NUMBER(38,0)
)
ON COMMIT DELETE ROWS;
--
CREATE GLOBAL TEMPORARY TABLE REM_ORDENSECCIONES (
    SE_CONSEC   NUMBER(38,0),
    SE_NOMBRE   VARCHAR2(250 BYTE),
    ET_CONSEC   NUMBER(38,0),
    SE_ORDENS   NUMBER(38,0),
    DI_CONSEC   NUMBER(38,0)
)
ON COMMIT PRESERVE ROWS;
--
CREATE TABLE REM_ORDENSECCIONESP (
    SE_CONSEC   NUMBER(38,0),
    SE_NOMBRE   VARCHAR2(250 BYTE),
    ET_CONSEC   NUMBER(38,0),
    SE_ORDENS   NUMBER(38,0),
    DI_CONSEC   NUMBER(38,0)
)

And I have this stored procedure:

CREATE OR REPLACE PROCEDURE SP_OBTENER_SECCION_AMBULATORIO(etConsec NUMBER DEFAULT NULL,
                                                    diConsec NUMBER DEFAULT NULL,
                                                    cursorParam OUT SYS_REFCURSOR)
AS
BEGIN
    --
    -- SE TRAEN TODAS LAS SECCIONES QUE ESTÁN EN PREGEVEN Y LAS QUE COINCIDAN
    -- CON LA ETAPA Y EL DIAGNÓSTICO EN SECCPREG
    -- SE GUARDAN EN LA TABLA TEMPORAL PARA PODER MODIFICAR EL ORDEN MÁS ADELANTE.

    --DEBUG
    -- SELECT * FROM REM_ORDENSECCIONES_TPM;
    -- SELECT * FROM REM_SECCPREG WHERE SE_CONSEC = 217;
    -- UPDATE REM_PREGEVEN SET SE_ORDEN = 11 WHERE SE_CONSEC = 217;
    INSERT INTO REM_ORDENSECCIONESP (SE_CONSEC, SE_NOMBRE, ET_CONSEC, SE_ORDENS)
        SELECT SE_CONSEC, SE_NOMBRE, ET_CONSEC, SE_ORDENS
        FROM REM_SECCPREG  
        WHERE SE_CONSEC IN 
                        (SELECT DISTINCT SE_CONSEC
                         FROM REM_PREGEVEN 
                         WHERE ET_CONSEC = etConsec AND DI_CONSEC = diConsec AND PR_ESTADO <> 'I') 
        OR ET_CONSEC = etConsec and Di_Consec = diConsec
        ORDER BY SE_ORDENS;
    --        
    -- ACTUALIZA LA TABLA TEMPORAL DE LAS SECCIONES CON EL SE_ORDEN
    -- DE LA TABLA DE REM_PREGEVEN.
    --

    --DEBUG
    -- SELECT * FROM REM_ORDENSECCIONES_TPM;
    -- COMMIT;
    UPDATE REM_ORDENSECCIONESP TMP
        SET TMP.SE_ORDENS = (SELECT DISTINCT PREGE.SE_ORDEN 
                             FROM REM_PREGEVEN PREGE 
                             WHERE ET_CONSEC = etConsec AND DI_CONSEC = diConsec AND PR_ESTADO <> 'I'
                             AND TMP.SE_CONSEC = PREGE.SE_CONSEC)
    WHERE EXISTS (SELECT 1
                    FROM REM_PREGEVEN PREGE
                    WHERE TMP.SE_CONSEC = PREGE.SE_CONSEC AND SE_ORDEN IS NOT NULL);

 OPEN cursorParam FOR 
  SELECT SE_CONSEC, SE_NOMBRE, ET_CONSEC FROM REM_ORDENSECCIONESP;
--COMMIT; --COMMIT NECESARIO PARA ELIMINAR LOS DATOS TEMPORALES


  EXECUTE IMMEDIATE 'TRUNCATE TABLE REM_ORDENSECCIONESP';-- || TBL_NAME;
END;

Any suggestion? I don't have any more paths to try.

I'm not Oracle expert, I used temporary tables with SQL Server and all was good.

Thanks! :)

Answer 1

Temporary Tables in SQL Server are different from Global Temporary Tables in Oracle. Specifically, Global Temporary Table are permanent data structures it's just the data which is temporary. Data in a GTT is restricted to the session which inserted it, and will be wiped at the end of the transaction or session, depending on how the table is defined.

The other thing about your procedure is that it passes an opened ref cursor to the calling program. A cursor is not a data set, it is a pointer to a query: the calling program then fetches the data, which means it executes the query and processes its result set. The problem is, immediately after opening the query your procedure executes EXECUTE IMMEDIATE 'TRUNCATE TABLE REM_ORDENSECCIONESP';. Consequently, when the calling program attempts to fetch records form the cursor the cupboard is bare.

Remove the truncate table statement. It is breaking your process. One of the problems with GTTs is that that issuing DDL is more troublesome than with normal tables: for instance we can't drop a GTT if there is an any session which has used it is still open. Fortunately truncation is almost certainly unnecessary, given the behaviour of global temporary tables. If you really think you need something, just delete from GTT at the start of the process; this will clear any lingering data from earlier in your session (transaction?) before you populate it afresh.

Here is a demo on db<>fiddle.