jueves, 4 de noviembre de 2010

Insertar dinamicamente en Tabla Temporal ORACLE

Las tablas temporales en ORACLE no son como en SQLServer.
La definición de una tabla temporal se almacena en el sistema, como con la de las tablas permanentes, pero los datos permanecerán sólo durante la sesión o transacción, lo cual podemos definir mediante la clausula:


/*Los datos no se eliminan mientras no acabe la sesión:*/
ON COMMIT PRESERVE ROWS

/*O bien:
*Los datos se eliminan al finalizar la transacción*/
ON COMMIT DELETE ROWS

Cabe señalar que, aunque el mismo usuario se conecte dos veces simultaneamente o mientras uno de ellos no termine la sesión, ambas sesiones serán tratadas independientemente,de esta forma lo que un usuario haga en la tabla no le afecta al otro.
Hay buena información en este post:
Tablas temporales en las bases de datos Oracle
Creando una tabla Temporal cuyos datos no se eliminarán mientras la sesión no termine:


CREATE GLOBAL TEMPORARY TABLE TEMP_TABLA
(
NOMBRE VARCHAR2(50),
APELLIDO VARCHAR2(100)
)
ON COMMIT PRESERVE ROWS

Creando una tabla temporal idéntica a una tabla existente:


CREATE GLOBAL TEMPORARY TABLE TEMP_TABLA
ON COMMIT PRESERVE ROWS
AS SELECT * FROM TABLA_EXISTENTE WHERE 1=0;


Insertando dinamicamente en la tabla temporal:
Un stored procedure que recorre una tabla LIST_TABLAS que contiene en su columna NOM_TABLA el nombre de tabla existente de la cual se extraeran los datos a insertar en la tabla temporal.Recordar que la tabla temporal es identica a la tabla que se consultará por los datos.Finalmente se hace un select en la tabla temporal:


/*LA TABLA LIST_TABLAS tiene las columnas:
*NOM_TABLA y FECHA_CREACION*/

CREATE OR REPLACE PROCEDURE SP_INSERTINTEMTABLA
(
fechaIni IN DATE,
fechaFin IN DATE,
curQuery IN OUT SYS_REFCURSOR
)
AS
querySQL varchar2(1000) :=''; nomTabla varchar2(100) := '';
cursor_ Integer; numeroMovidos INTEGER;
CURSOR CUR_LIST_TABLA IS
SELECT NOM_TABLA FROM LIST_TABLA WHERE TRUNC(FECHA_CREACION) BETWEEN TRUNC(fechaIni) AND TRUNC(fechaFin);
BEGIN
FOR TA IN CUR_LIST_TABLA
LOOP
nomTabla := LTRIM(RTRIM(TA.NOM_TABLA));
querySQL := 'INSERT INTO TEMP_TABLA ' || 'SELECT * FROM ' || nomTabla;
cursor_ := dbms_sql.open_cursor;
dbms_sql.parse (cursor_, querySQL, dbms_sql.native);
numeroMovidos := dbms_sql.execute (cursor_);
dbms_sql.close_cursor (cursor_);
END LOOP;
OPEN curQuery FOR
SELECT * FROM TEMP_TABLA;
END;

4 comentarios:

  1. interesante la información se agradece.

    ResponderEliminar
  2. borthr y como elimino una tabla temporal

    ResponderEliminar
    Respuestas
    1. Amigo no entiendo para qué querrías eliminar la tabla temporal ya que estas tablas en Oracle se comportan diferente a las de MSSQL. A estas tablas de Oracle no se les necesita borrar los registros, como explico en el post y como está mejor explicado en el link que pongo. Hace tiempo que no trabajo con Oracle y lamento no poder ayudarte, aunque he hecho una búsqueda y lo que he encontrado es esto:
      http://stackoverflow.com/questions/7932977/dropping-a-global-temporary-table
      quizás sea de ayuda.

      Eliminar
  3. Cómo se invoca ese procedimiento, desde plsql?

    ResponderEliminar