Uso Tabla Temporal en bloque PL/SQL
Hola, necesitamos crear una tabla temporal desde un procedimiento almacenado en Oracle para luego insertar determinados registros y hacer alguna select sobre tal tabla.
Gracias.
Gracias.
1 Respuesta
Respuesta de thetaurus
1
OK!
Ya te doy una instrucción de pl/sql que crea a parte detablas funciones!
OK!
Ya te doy una instrucción de pl/sql que crea a parte detablas funciones!
OK!
CREATE OR REPLACE PROCEDURE clear_baruta
IS
V_DINAMIC_SQLS VARCHAR2(10000);
BEGIN
--
V_DINAMIC_SQLS := ' truncate table ACTIVIDAD ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table CONTRIBUYENTE ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table MOVIMIENTO ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table M_INMUEBLE ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table M_I_C ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table M_VEHICULO ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table SINPATENTE ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table TEMPORY_TRANSFERENCE ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table TEMPORY_TRANSFERENCE_BACKUP ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table TMP_CLA_ACT_IC ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' TRUNCATE TABLE CORTE_SALDOS_IC ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE(V_DINAMIC_SQLS);
--
V_DINAMIC_SQLS := ' TRUNCATE TABLE CORTE_SALDOS_INM ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE(V_DINAMIC_SQLS);
--
V_DINAMIC_SQLS := ' TRUNCATE TABLE CORTE_SALDOS_SIN ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE(V_DINAMIC_SQLS);
--
V_DINAMIC_SQLS := ' TRUNCATE TABLE CORTE_SALDOS_VEHI ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE(V_DINAMIC_SQLS);
--
end ;
/
IS
V_DINAMIC_SQLS VARCHAR2(10000);
BEGIN
--
V_DINAMIC_SQLS := ' truncate table ACTIVIDAD ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table CONTRIBUYENTE ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table MOVIMIENTO ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table M_INMUEBLE ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table M_I_C ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table M_VEHICULO ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table SINPATENTE ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table TEMPORY_TRANSFERENCE ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table TEMPORY_TRANSFERENCE_BACKUP ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' truncate table TMP_CLA_ACT_IC ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
--
V_DINAMIC_SQLS := ' TRUNCATE TABLE CORTE_SALDOS_IC ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE(V_DINAMIC_SQLS);
--
V_DINAMIC_SQLS := ' TRUNCATE TABLE CORTE_SALDOS_INM ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE(V_DINAMIC_SQLS);
--
V_DINAMIC_SQLS := ' TRUNCATE TABLE CORTE_SALDOS_SIN ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE(V_DINAMIC_SQLS);
--
V_DINAMIC_SQLS := ' TRUNCATE TABLE CORTE_SALDOS_VEHI ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE(V_DINAMIC_SQLS);
--
end ;
/
V_DINAMIC_SQLS := ' create TABLE tempory_transference(ramo number '||
',municipio number '||
',urbanizacion number '||
',cuenta number '||
',checksum varchar2(1) '||
',catastro number(16) '||
',ced_rif varchar2(10) '||
',nom_razon_negocio varchar2(100) '||
',dir varchar2(100) '||
',apertura date '||
',ingreso number '||
', AFORO NUMBER '||
',status varchar2(2) '||
',fecha_reg date '||
',fecha_cambio date '||
',fecha_adq date '||
',tipo_suelo varchar2(10) '||
',valor number '||
',valor_act number '||
',tipo_inm varchar2(2) '||
',placa varchar2(6) '||
',uso varchar2(2) '||
',clase varchar2(20) '||
',tipo varchar2(20) '||
',MARCA varchar2(20) '||
',V_COLOR varchar2(20) '||
',MODELO varchar2(20) '||
',ANO NUMBER '||
',PESO NUMBER '||
',CAPACIDAD NUMBER '||
',PUESTOS NUMBER '||
',SER_MOTOR varchar2(15) '||
',SER_CARROCERIA varchar2(20) '||
',CUENTA_ENLACE VARCHAR2 (20) '||
',tabla varchar2(10) '||
',CODIGO_SAMI NUMBER) ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE('create TABLE tempory_transference');
',municipio number '||
',urbanizacion number '||
',cuenta number '||
',checksum varchar2(1) '||
',catastro number(16) '||
',ced_rif varchar2(10) '||
',nom_razon_negocio varchar2(100) '||
',dir varchar2(100) '||
',apertura date '||
',ingreso number '||
', AFORO NUMBER '||
',status varchar2(2) '||
',fecha_reg date '||
',fecha_cambio date '||
',fecha_adq date '||
',tipo_suelo varchar2(10) '||
',valor number '||
',valor_act number '||
',tipo_inm varchar2(2) '||
',placa varchar2(6) '||
',uso varchar2(2) '||
',clase varchar2(20) '||
',tipo varchar2(20) '||
',MARCA varchar2(20) '||
',V_COLOR varchar2(20) '||
',MODELO varchar2(20) '||
',ANO NUMBER '||
',PESO NUMBER '||
',CAPACIDAD NUMBER '||
',PUESTOS NUMBER '||
',SER_MOTOR varchar2(15) '||
',SER_CARROCERIA varchar2(20) '||
',CUENTA_ENLACE VARCHAR2 (20) '||
',tabla varchar2(10) '||
',CODIGO_SAMI NUMBER) ';
EXECUTE IMMEDIATE V_DINAMIC_SQLS;
dbms_output.PUT_LINE('create TABLE tempory_transference');
