title: ORA-08103 Using global temporary table DML(insert, update, select) inside a procedure, select cursor to temp-table shows error object doesn't exists
body: <p>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 </p>
  
  <blockquote>
    <p>object doesn't exist</p>
  </blockquote>
  
  <p>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:</p>
  
  <ol>
  <li><p>Temporary table with <code>ON COMMIT DELETE ROWS;</code> statement, and <code>COMMIT;</code> at the end of procedure.</p></li>
  <li><p>Temporary table with <code>ON COMMIT PRESERVE ROWS;</code> statement, and <code>TRUNCATE TABLE</code> at the end of procedure</p></li>
  <li><p>Regular table with <code>EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_NAME'</code> statement at the end of procedure</p></li>
  </ol>
  
  <p>All of these options show the same error.</p>
  
  <p>I have 2 tables and I am merging that 2 tables in global temporary table changing some values of records.</p>
  
  <p>Then I am creating a cursor to return a <code>SELECT</code> to the temporary table.</p>
  
  <p>It's here when the error appears.</p>
  
  <p>So, I created the following tables:</p>
  
  <pre><code>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)
  )
  </code></pre>
  
  <p>And I have this stored procedure:</p>
  
  <pre><code>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 &lt;&gt; '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 &lt;&gt; '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;
  </code></pre>
  
  <p>Any suggestion? I don't have any more paths to try.</p>
  
  <p>I'm not Oracle expert, I used temporary tables with SQL Server and all was good.</p>
  
  <p>Thanks! :)</p>