I have an enormous file of 1.9 GB (more than 650000 records of Host's BD) that I have to import in an Oracle BD and realize calculations with its fields. I have tried to do it with two methods: 1.-Using utl_file to work directly with the file 2.-Using sql*loader to import the file to a table and work from this table. The two works(the second one a bit more rapid than the first one) but turn out to be very slow (1000 policies per minute) being able to be late the whole process more than 10 hours! How I can improve the yield of the process? I attach the procedure of the first method and the trigger of the second one CREATE OR REPLACE PROCEDURE PROPERTY (loc_in in varchar2,file_in in varchar2) IS file_handle UTL_FILE.FILE_TYPE; buffer VARCHAR2(3605); ok BOOLEAN; TYPE vector IS TABLE OF CHAR(2) INDEX BY BINARY_INTEGER; clausula vector; TYPE vector2 IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER; garantia vector2; ut vector2; TYPE vector3 IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; capital vector3; aplicacion CHAR(3); ramo NUMBER(3); version1 NUMBER(4); poliza CHAR(8); codigo_pr CHAR(2); codigo_pos CHAR(5); provincia CHAR(40); etiqueta CHAR(7); porcentaje NUMBER(5,2); coaseguro CHAR(5); coaseguro_n NUMBER(5,2); gar NUMBER(5); unidad_tar NUMBER(5); total_cte NUMBER(15,2) := 0; total_cdo NUMBER(15,2) := 0; total_cte_cdo NUMBER(15,2) := 0; total_pb NUMBER(15,2) := 0; total_cte_ex NUMBER(15,2) := 0; total_cdo_ex NUMBER(15,2) := 0; total_pb_ex NUMBER(15,2) := 0; cap_CTE NUMBER(6) := 0; cap_CDO NUMBER(6) := 0; cap_PB NUMBER(6) := 0; CURSOR seleccion IS SELECT PACT_TARIFA.PLH_SETIQUETA,PACT_TARIFA.PLH_NPORCENTAJE FROM PACT_TARIFA WHERE PACT_TARIFA.PLH_NGARANTIA = gar AND PACT_TARIFA.PLH_NUNIDAD_TARIFA = unidad_tar AND PACT_TARIFA.PLH_NRAMO = ramo AND PACT_TARIFA.PLH_NVERSION = version1; CURSOR localizacion IS SELECT PACT_PROVINCIA.PRO_SNOMBRE_PROVINCIA FROM PACT_PROVINCIA WHERE PACT_PROVINCIA.PRO_SCODIGO_PROVINCIA = codigo_pr; BEGIN ok := FALSE; file_handle := UTL_FILE.FOPEN(loc_in,file_in,'R',3605); -- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION LOOP BEGIN UTL_FILE.GET_LINE(file_handle,buffer); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; aplicacion := SUBSTR(buffer,1,3); ramo := to_number(SUBSTR(buffer,5,3)); version1 := to_number(SUBSTR(buffer,9,4)); poliza := SUBSTR(buffer,14,8); codigo_pr := SUBSTR(buffer,39,2); codigo_pos := SUBSTR(buffer,33,5); provincia := SUBSTR(buffer,42,40); coaseguro := SUBSTR(buffer,83,5); clausula(1) := SUBSTR(buffer,94,1); clausula(2) := SUBSTR(buffer,97,2); clausula(3) := SUBSTR(buffer,100,2); clausula(4) := SUBSTR(buffer,103,2); clausula(5) := SUBSTR(buffer,106,2); clausula(6) := SUBSTR(buffer,109,2); clausula(7) := SUBSTR(buffer,112,2); clausula(8) := SUBSTR(buffer,115,2); clausula(9) := SUBSTR(buffer,118,2); clausula(10) := SUBSTR(buffer,121,2); clausula(11) := SUBSTR(buffer,124,2); clausula(12) := SUBSTR(buffer,127,2); clausula(13) := SUBSTR(buffer,130,2); clausula(14) := SUBSTR(buffer,133,2); clausula(15) := SUBSTR(buffer,136,2); clausula(16) := SUBSTR(buffer,139,2); clausula(17) := SUBSTR(buffer,142,2); clausula(18) := SUBSTR(buffer,145,2); clausula(19) := SUBSTR(buffer,148,2); clausula(20) := SUBSTR(buffer,151,2); garantia(1) := SUBSTR(buffer,154,5); ut(1) := SUBSTR(buffer,160,5); capital(1) := SUBSTR(buffer,207,15); garantia(2) := SUBSTR(buffer,223,5); ut(2) := SUBSTR(buffer,229,5); capital(2) := SUBSTR(buffer,276,15); garantia(3) := SUBSTR(buffer,292,5); ut(3) := SUBSTR(buffer,298,5); capital(3) := SUBSTR(buffer,345,15); garantia(4) := SUBSTR(buffer,361,5); ut(4) := SUBSTR(buffer,367,5); capital(4) := SUBSTR(buffer,414,15); garantia(5) := SUBSTR(buffer,430,5); ut(5) := SUBSTR(buffer,436,5); capital(5) := SUBSTR(buffer,483,15); garantia(6) := SUBSTR(buffer,499,5); ut(6) := SUBSTR(buffer,505,5); capital(6) := SUBSTR(buffer,552,15); garantia(7) := SUBSTR(buffer,568,5); ut(7) := SUBSTR(buffer,574,5); capital(7) := SUBSTR(buffer,621,15); garantia(8) := SUBSTR(buffer,637,5); ut(8) := SUBSTR(buffer,643,5); capital(8) := SUBSTR(buffer,690,15); garantia(9) := SUBSTR(buffer,706,5); ut(9) := SUBSTR(buffer,712,5); capital(9) := SUBSTR(buffer,759,15); garantia(10) := SUBSTR(buffer,775,5); ut(10) := SUBSTR(buffer,781,5); capital(10) := SUBSTR(buffer,828,15); garantia(11) := SUBSTR(buffer,844,5); ut(11) := SUBSTR(buffer,850,5); capital(11) := SUBSTR(buffer,897,15); garantia(12) := SUBSTR(buffer,913,5); ut(12) := SUBSTR(buffer,919,5); capital(12) := SUBSTR(buffer,966,15); garantia(13) := SUBSTR(buffer,982,5); ut(13) := SUBSTR(buffer,988,5); capital(13) := SUBSTR(buffer,1035,15); garantia(14) := SUBSTR(buffer,1051,5); ut(14) := SUBSTR(buffer,1057,5); capital(14) := SUBSTR(buffer,1104,15); garantia(15) := SUBSTR(buffer,1120,5); ut(15) := SUBSTR(buffer,1126,5); capital(15) := SUBSTR(buffer,1173,15); garantia(16) := SUBSTR(buffer,1189,5); ut(16) := SUBSTR(buffer,1195,5); capital(16) := SUBSTR(buffer,1242,15); garantia(17) := SUBSTR(buffer,1258,5); ut(17) := SUBSTR(buffer,1264,5); capital(17) := SUBSTR(buffer,1311,15); garantia(18) := SUBSTR(buffer,1327,5); ut(18) := SUBSTR(buffer,1333,5); capital(18) := SUBSTR(buffer,1380,15); garantia(19) := SUBSTR(buffer,1396,5); ut(19) := SUBSTR(buffer,1402,5); capital(19) := SUBSTR(buffer,1449,15); garantia(20) := SUBSTR(buffer,1465,5); ut(20) := SUBSTR(buffer,1471,5); capital(20) := SUBSTR(buffer,1518,15); garantia(21) := SUBSTR(buffer,1534,5); ut(21) := SUBSTR(buffer,1540,5); capital(21) := SUBSTR(buffer,1587,15); garantia(22) := SUBSTR(buffer,1603,5); ut(22) := SUBSTR(buffer,1609,5); capital(22) := SUBSTR(buffer,1656,15); garantia(23) := SUBSTR(buffer,1672,5); ut(23) := SUBSTR(buffer,1678,5); capital(23) := SUBSTR(buffer,1725,15); garantia(24) := SUBSTR(buffer,1741,5); ut(24) := SUBSTR(buffer,1747,5); capital(24) := SUBSTR(buffer,1794,15); garantia(25) := SUBSTR(buffer,1810,5); ut(25) := SUBSTR(buffer,1816,5); capital(25) := SUBSTR(buffer,1863,15); garantia(26) := SUBSTR(buffer,1879,5); ut(26) := SUBSTR(buffer,1885,5); capital(26) := SUBSTR(buffer,1932,15); garantia(27) := SUBSTR(buffer,1948,5); ut(27) := SUBSTR(buffer,1954,5); capital(27) := SUBSTR(buffer,2001,15); garantia(28) := SUBSTR(buffer,2017,5); ut(28) := SUBSTR(buffer,2023,5); capital(28) := SUBSTR(buffer,2070,15); garantia(29) := SUBSTR(buffer,2086,5); ut(29) := SUBSTR(buffer,2092,5); capital(29) := SUBSTR(buffer,2139,15); garantia(30) := SUBSTR(buffer,2155,5); ut(30) := SUBSTR(buffer,2161,5); capital(30) := SUBSTR(buffer,2208,15); garantia(31) := SUBSTR(buffer,2224,5); ut(31) := SUBSTR(buffer,2230,5); capital(31) := SUBSTR(buffer,2277,15); garantia(32) := SUBSTR(buffer,2293,5); ut(32) := SUBSTR(buffer,2299,5); capital(32) := SUBSTR(buffer,2346,15); garantia(33) := SUBSTR(buffer,2362,5); ut(33) := SUBSTR(buffer,2368,5); capital(33) := SUBSTR(buffer,2415,15); garantia(34) := SUBSTR(buffer,2431,5); ut(34) := SUBSTR(buffer,2437,5); capital(34) := SUBSTR(buffer,2484,15); garantia(35) := SUBSTR(buffer,2500,5); ut(35) := SUBSTR(buffer,2506,5); capital(35) := SUBSTR(buffer,2553,15); garantia(36) := SUBSTR(buffer,2569,5); ut(36) := SUBSTR(buffer,2575,5); capital(36) := SUBSTR(buffer,2622,15); garantia(37) := SUBSTR(buffer,2638,5); ut(37) := SUBSTR(buffer,2644,5); capital(37) := SUBSTR(buffer,2691,15); garantia(38) := SUBSTR(buffer,2707,5); ut(38) := SUBSTR(buffer,2713,5); capital(38) := SUBSTR(buffer,2760,15); garantia(39) := SUBSTR(buffer,2776,5); ut(39) := SUBSTR(buffer,2782,5); capital(39) := SUBSTR(buffer,2829,15); garantia(40) := SUBSTR(buffer,2845,5); ut(40) := SUBSTR(buffer,2851,5); capital(40) := SUBSTR(buffer,2898,15); garantia(41) := SUBSTR(buffer,2914,5); ut(41) := SUBSTR(buffer,2920,5); capital(41) := SUBSTR(buffer,2967,15); garantia(42) := SUBSTR(buffer,2983,5); ut(42) := SUBSTR(buffer,2989,5); capital(42) := SUBSTR(buffer,3036,15); garantia(43) := SUBSTR(buffer,3052,5); ut(43) := SUBSTR(buffer,3058,5); capital(43) := SUBSTR(buffer,3105,15); garantia(44) := SUBSTR(buffer,3121,5); ut(44) := SUBSTR(buffer,3127,5); capital(44) := SUBSTR(buffer,3174,15); garantia(45) := SUBSTR(buffer,3190,5); ut(45) := SUBSTR(buffer,3196,5); capital(45) := SUBSTR(buffer,3243,15); garantia(46) := SUBSTR(buffer,3259,5); ut(46) := SUBSTR(buffer,3265,5); capital(46) := SUBSTR(buffer,3312,15); garantia(47) := SUBSTR(buffer,3328,5); ut(47) := SUBSTR(buffer,3334,5); capital(47) := SUBSTR(buffer,3381,15); garantia(48) := SUBSTR(buffer,3397,5); ut(48) := SUBSTR(buffer,3403,5); capital(48) := SUBSTR(buffer,3450,15); garantia(49) := SUBSTR(buffer,3466,5); ut(49) := SUBSTR(buffer,3472,5); capital(49) := SUBSTR(buffer,3519,15); garantia(50) := SUBSTR(buffer,3535,5); ut(50) := SUBSTR(buffer,3541,5); capital(50) := SUBSTR(buffer,3588,15); -- Filtre excepció polisses RSM IF ramo = '061' and aplicacion = 'RSM' THEN FOR i IN 1..20 LOOP IF clausula(i) IN ('A0','A1') THEN ok := TRUE; END IF; END LOOP; END IF; -- Estandarització nom de província - codi província - codi postal OPEN localizacion; FETCH localizacion INTO provincia; CLOSE localizacion; --procés reducció etiquetes de capital (GPW,GPW++,RSM) IF ok = FALSE THEN FOR i IN 1..50 LOOP gar := garantia(i); unidad_tar := ut(i); OPEN seleccion; FETCH seleccion INTO etiqueta,porcentaje; CLOSE seleccion; IF etiqueta = 'CTE' THEN total_cte := total_cte + (to_number(capital(i))/100); ELSIF etiqueta = 'CDO' THEN total_cdo := total_cdo + (to_number(capital(i))/100); ELSIF etiqueta = 'CTE/CDO' THEN total_cte_cdo := total_cte_cdo + (to_number(capital(i))/100); ELSE total_pb := total_pb + (to_number(capital(i))/100) * porcentaje; END IF; END LOOP; IF total_cte = 0 and total_cdo = 0 THEN IF ramo IN (95,98) THEN total_cte := total_cte_cdo; ELSIF ramo IN (41,42,65,66,92,96,97) THEN total_cdo := total_cte_cdo; END IF; ELSE total_cte := (total_cte/(total_cte + total_cdo)) * total_cte_cdo + total_cte; total_cdo := (total_cdo/(total_cte + total_cdo)) * total_cte_cdo + total_cdo; END IF; IF total_cte < 600 AND total_cte > 0 THEN total_cte := 600; END IF; IF total_cdo < 600 AND total_cdo > 0 THEN total_cdo := 600; END IF; IF total_pb < 600 AND total_pb > 0 THEN total_pb := 600; END IF; IF total_cte < 0 THEN total_cte := 0; END IF; IF total_cdo < 0 THEN total_cdo := 0; END IF; IF total_pb < 0 THEN total_pb := 0; END IF; total_cte_ex := total_cte * (to_number(coaseguro)/100)/100; total_cdo_ex := total_cdo * (to_number(coaseguro)/100)/100; total_pb_ex := total_pb * (to_number(coaseguro)/100)/100; coaseguro_n := to_number(coaseguro)/100; IF total_cte > 0 THEN cap_CTE := cap_CTE + 1; ELSIF total_cdo > 0 THEN cap_CDO := cap_CDO + 1; ELSIF total_pb > 0 THEN cap_PB := cap_PB + 1; END IF; INSERT INTO PACT_SALIDA(PLH_SRAMO,PLH_SPOLIZA,PLH_SCODIGO_PROVINCIA,PLH_SCODIGO_POSTAL,PLH_SPROVINCIA,PLH_ISUMA_ASEGURADA_CTE,PLH_ISUMA_EXPUESTA_CTE,PLH_ISUMA_ASEGURADA_CDO,PLH_ISUMA_EXPUESTA_CDO,PLH_ISUMA_ASEGURADA_PB,PLH_ISUMA_EXPUESTA_PB,PLH_NCOASEGURO) VALUES(ramo,poliza,codigo_pr,codigo_pos,provincia,total_cte,total_cte_ex,total_cdo,total_cdo_ex,total_pb,total_pb_ex,coaseguro_n); END IF; COMMIT; END LOOP; UTL_FILE.FCLOSE(file_handle); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN RAISE_APPLICATION_ERROR(-20100,'INVALID PATH'); WHEN UTL_FILE.INVALID_MODE THEN RAISE_APPLICATION_ERROR(-20101,'INVALID MODE'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN RAISE_APPLICATION_ERROR(-20102,'INVALID FILEHANDLE'); WHEN UTL_FILE.INVALID_OPERATION THEN RAISE_APPLICATION_ERROR(-20103,'INVALID OPERATION -- MAY SIGNAL A FILE LOCKED BY THE OS'); WHEN UTL_FILE.READ_ERROR THEN RAISE_APPLICATION_ERROR(-20104,'READ ERROR'); WHEN UTL_FILE.WRITE_ERROR THEN RAISE_APPLICATION_ERROR(-20105,'WRITE ERROR'); WHEN UTL_FILE.INTERNAL_ERROR THEN RAISE_APPLICATION_ERROR(-20106,'INTERNAL ERROR'); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20107,'NO DATA FOUND'); WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR(-20108,'VALUE ERROR'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20109,'UNKNOWN UTL_FILE ERROR'); END PROPERTY; / CREATE OR REPLACE TRIGGER TR_FILTRO AFTER INSERT ON PRINCIPAL FOR EACH ROW DECLARE ok BOOLEAN; TYPE vector IS TABLE OF CHAR(2) INDEX BY BINARY_INTEGER; clausula vector; TYPE vector2 IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER; garantia vector2; ut vector2; TYPE vector3 IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; capital vector3; aplicacion CHAR(3); ramo CHAR(3); provincia CHAR(40); etiqueta CHAR(7); porcentaje NUMBER(5,2); coaseguro_n NUMBER(5,2); gar NUMBER(5); unidad_tar NUMBER(5); total_cte NUMBER(15,2) := 0; total_cdo NUMBER(15,2) := 0; total_cte_cdo NUMBER(15,2) := 0; total_pb NUMBER(15,2) := 0; total_cte_ex NUMBER(15,2) := 0; total_cdo_ex NUMBER(15,2) := 0; total_pb_ex NUMBER(15,2) := 0; cap_CTE NUMBER(6) := 0; cap_CDO NUMBER(6) := 0; cap_PB NUMBER(6) := 0; CURSOR seleccion IS SELECT PACT_TARIFA.PLH_SETIQUETA,PACT_TARIFA.PLH_NPORCENTAJE FROM PACT_TARIFA WHERE PACT_TARIFA.PLH_NGARANTIA = gar AND PACT_TARIFA.PLH_NUNIDAD_TARIFA = unidad_tar AND PACT_TARIFA.PLH_NRAMO = :NEW.plh_nramo AND PACT_TARIFA.PLH_NVERSION = :NEW.plh_nversion; CURSOR localizacion IS SELECT PACT_PROVINCIA.PRO_SNOMBRE_PROVINCIA FROM PACT_PROVINCIA WHERE PACT_PROVINCIA.PRO_SCODIGO_PROVINCIA = :NEW.PLH_SCODIGO_DE_PROVINCIA; BEGIN ok := FALSE; clausula(1) := :NEW.plh_sclausula1; clausula(2) := :NEW.plh_sclausula2; clausula(3) := :NEW.plh_sclausula3; clausula(4) := :NEW.plh_sclausula4; clausula(5) := :NEW.plh_sclausula5; clausula(6) := :NEW.plh_sclausula6; clausula(7) := :NEW.plh_sclausula7; clausula(8) := :NEW.plh_sclausula8; clausula(9) := :NEW.plh_sclausula9; clausula(10) := :NEW.plh_sclausula10; clausula(11) := :NEW.plh_sclausula11; clausula(12) := :NEW.plh_sclausula12; clausula(13) := :NEW.plh_sclausula13; clausula(14) := :NEW.plh_sclausula14; clausula(15) := :NEW.plh_sclausula15; clausula(16) := :NEW.plh_sclausula16; clausula(17) := :NEW.plh_sclausula17; clausula(18) := :NEW.plh_sclausula18; clausula(19) := :NEW.plh_sclausula19; clausula(20) := :NEW.plh_sclausula20; garantia(1) := :NEW.plh_ncodgar1; ut(1) := :NEW.plh_ncodut1; capital(1) := :NEW.plh_icapital1; garantia(2) := :NEW.plh_ncodgar2; ut(2) := :NEW.plh_ncodut2; capital(2) := :NEW.plh_icapital2; garantia(3) := :NEW.plh_ncodgar3; ut(3) := :NEW.plh_ncodut3; capital(3) := :NEW.plh_icapital3; garantia(4) := :NEW.plh_ncodgar4; ut(4) := :NEW.plh_ncodut4; capital(4) := :NEW.plh_icapital4; garantia(5) := :NEW.plh_ncodgar5; ut(5) := :NEW.plh_ncodut5; capital(5) := :NEW.plh_icapital5; garantia(6) := :NEW.plh_ncodgar6; ut(6) := :NEW.plh_ncodut6; capital(6) := :NEW.plh_icapital6; garantia(7) := :NEW.plh_ncodgar7; ut(7) := :NEW.plh_ncodut7; capital(7) := :NEW.plh_icapital7; garantia(8) := :NEW.plh_ncodgar8; ut(8) := :NEW.plh_ncodut8; capital(8) := :NEW.plh_icapital8; garantia(9) := :NEW.plh_ncodgar9; ut(9) := :NEW.plh_ncodut9; capital(9) := :NEW.plh_icapital9; garantia(10) := :NEW.plh_ncodgar10; ut(10) := :NEW.plh_ncodut10; capital(10) := :NEW.plh_icapital10; garantia(11) := :NEW.plh_ncodgar11; ut(11) := :NEW.plh_ncodut11; capital(11) := :NEW.plh_icapital11; garantia(12) := :NEW.plh_ncodgar12; ut(12) := :NEW.plh_ncodut12; capital(12) := :NEW.plh_icapital12; garantia(13) := :NEW.plh_ncodgar13; ut(13) := :NEW.plh_ncodut13; capital(13) := :NEW.plh_icapital13; garantia(14) := :NEW.plh_ncodgar14; ut(14) := :NEW.plh_ncodut14; capital(14) := :NEW.plh_icapital14; garantia(15) := :NEW.plh_ncodgar15; ut(15) := :NEW.plh_ncodut15; capital(15) := :NEW.plh_icapital15; garantia(16) := :NEW.plh_ncodgar16; ut(16) := :NEW.plh_ncodut16; capital(16) := :NEW.plh_icapital16; garantia(17) := :NEW.plh_ncodgar17; ut(17) := :NEW.plh_ncodut17; capital(17) := :NEW.plh_icapital17; garantia(18) := :NEW.plh_ncodgar18; ut(18) := :NEW.plh_ncodut18; capital(18) := :NEW.plh_icapital18; garantia(19) := :NEW.plh_ncodgar19; ut(19) := :NEW.plh_ncodut19; capital(19) := :NEW.plh_icapital19; garantia(20) := :NEW.plh_ncodgar20; ut(20) := :NEW.plh_ncodut20; capital(20) := :NEW.plh_icapital20; garantia(21) := :NEW.plh_ncodgar21; ut(21) := :NEW.plh_ncodut21; capital(21) := :NEW.plh_icapital21; garantia(22) := :NEW.plh_ncodgar22; ut(22) := :NEW.plh_ncodut22; capital(22) := :NEW.plh_icapital22; garantia(23) := :NEW.plh_ncodgar23; ut(23) := :NEW.plh_ncodut23; capital(23) := :NEW.plh_icapital23; garantia(24) := :NEW.plh_ncodgar24; ut(24) := :NEW.plh_ncodut24; capital(24) := :NEW.plh_icapital24; garantia(25) := :NEW.plh_ncodgar25; ut(25) := :NEW.plh_ncodut25; capital(25) := :NEW.plh_icapital25; garantia(26) := :NEW.plh_ncodgar26; ut(26) := :NEW.plh_ncodut26; capital(26) := :NEW.plh_icapital26; garantia(27) := :NEW.plh_ncodgar27; ut(27) := :NEW.plh_ncodut27; capital(27) := :NEW.plh_icapital27; garantia(28) := :NEW.plh_ncodgar28; ut(28) := :NEW.plh_ncodut28; capital(28) := :NEW.plh_icapital28; garantia(29) := :NEW.plh_ncodgar29; ut(29) := :NEW.plh_ncodut29; capital(29) := :NEW.plh_icapital29; garantia(30) := :NEW.plh_ncodgar30; ut(30) := :NEW.plh_ncodut30; capital(30) := :NEW.plh_icapital30; garantia(31) := :NEW.plh_ncodgar31; ut(31) := :NEW.plh_ncodut31; capital(31) := :NEW.plh_icapital31; garantia(32) := :NEW.plh_ncodgar32; ut(32) := :NEW.plh_ncodut32; capital(32) := :NEW.plh_icapital32; garantia(33) := :NEW.plh_ncodgar33; ut(33) := :NEW.plh_ncodut33; capital(33) := :NEW.plh_icapital33; garantia(34) := :NEW.plh_ncodgar34; ut(34) := :NEW.plh_ncodut34; capital(34) := :NEW.plh_icapital34; garantia(35) := :NEW.plh_ncodgar35; ut(35) := :NEW.plh_ncodut35; capital(35) := :NEW.plh_icapital35; garantia(36) := :NEW.plh_ncodgar35; ut(36) := :NEW.plh_ncodut36; capital(36) := :NEW.plh_icapital36; garantia(37) := :NEW.plh_ncodgar36; ut(37) := :NEW.plh_ncodut37; capital(37) := :NEW.plh_icapital37; garantia(38) := :NEW.plh_ncodgar38; ut(38) := :NEW.plh_ncodut38; capital(38) := :NEW.plh_icapital38; garantia(39) := :NEW.plh_ncodgar39; ut(39) := :NEW.plh_ncodut39; capital(39) := :NEW.plh_icapital39; garantia(40) := :NEW.plh_ncodgar40; ut(40) := :NEW.plh_ncodut40; capital(40) := :NEW.plh_icapital40; garantia(41) := :NEW.plh_ncodgar41; ut(41) := :NEW.plh_ncodut41; capital(41) := :NEW.plh_icapital41; garantia(42) := :NEW.plh_ncodgar42; ut(42) := :NEW.plh_ncodut42; capital(42) := :NEW.plh_icapital42; garantia(43) := :NEW.plh_ncodgar43; ut(43) := :NEW.plh_ncodut43; capital(43) := :NEW.plh_icapital43; garantia(44) := :NEW.plh_ncodgar44; ut(44) := :NEW.plh_ncodut44; capital(44) := :NEW.plh_icapital44; garantia(45) := :NEW.plh_ncodgar45; ut(45) := :NEW.plh_ncodut45; capital(45) := :NEW.plh_icapital45; garantia(46) := :NEW.plh_ncodgar46; ut(46) := :NEW.plh_ncodut46; capital(46) := :NEW.plh_icapital46; garantia(47) := :NEW.plh_ncodgar47; ut(47) := :NEW.plh_ncodut47; capital(47) := :NEW.plh_icapital47; garantia(48) := :NEW.plh_ncodgar48; ut(48) := :NEW.plh_ncodut48; capital(48) := :NEW.plh_icapital48; garantia(49) := :NEW.plh_ncodgar49; ut(49) := :NEW.plh_ncodut49; capital(49) := :NEW.plh_icapital49; garantia(50) := :NEW.plh_ncodgar50; ut(50) := :NEW.plh_ncodut50; capital(50) := :NEW.plh_icapital50; -- Filtre excepció polisses RSM IF :NEW.plh_nramo = '061' and :NEW.plh_saplicacion = 'RSM' THEN FOR i IN 1..20 LOOP IF clausula(i) IN ('A0','A1') THEN ok := TRUE; END IF; END LOOP; END IF; -- Estandarització nom de província - codi província - codi postal OPEN localizacion; FETCH localizacion INTO provincia; CLOSE localizacion; --procés reducció etiquetes de capital (GPW,GPW++,RSM) IF ok = FALSE THEN FOR i IN 1..50 LOOP gar := garantia(i); unidad_tar := ut(i); OPEN seleccion; FETCH seleccion INTO etiqueta,porcentaje; CLOSE seleccion; IF etiqueta = 'CTE' THEN total_cte := total_cte + (to_number(capital(i))/100); ELSIF etiqueta = 'CDO' THEN total_cdo := total_cdo + (to_number(capital(i))/100); ELSIF etiqueta = 'CTE/CDO' THEN total_cte_cdo := total_cte_cdo + (to_number(capital(i))/100); ELSE total_pb := total_pb + (to_number(capital(i))/100) * porcentaje; END IF; END LOOP; IF total_cte = 0 and total_cdo = 0 THEN IF :NEW.ramo IN (95,98) THEN total_cte := total_cte_cdo; ELSIF :NEW.ramo IN (41,42,65,66,92,96,97) THEN total_cdo := total_cte_cdo; END IF; ELSE total_cte := (total_cte/(total_cte + total_cdo)) * total_cte_cdo + total_cte; total_cdo := (total_cdo/(total_cte + total_cdo)) * total_cte_cdo + total_cdo; END IF; IF total_cte < 600 AND total_cte > 0 THEN total_cte := 600; END IF; IF total_cdo < 600 AND total_cdo > 0 THEN total_cdo := 600; END IF; IF total_pb < 600 AND total_pb > 0 THEN total_pb := 600; END IF; IF total_cte < 0 THEN total_cte := 0; END IF; IF total_cdo < 0 THEN total_cdo := 0; END IF; IF total_pb < 0 THEN total_pb := 0; END IF; total_cte_ex := total_cte * (to_number(:NEW.plh_nporcentaje_coaseguro)/100)/100; total_cdo_ex := total_cdo * (to_number(:NEW.plh_nporcentaje_coaseguro)/100)/100; total_pb_ex := total_pb * (to_number(:NEW.plh_nporcentaje_coaseguro)/100)/100; coaseguro_n := :NEW.plh_nporcentaje_coaseguro; IF total_cte > 0 THEN cap_CTE := cap_CTE + 1; ELSIF total_cdo > 0 THEN cap_CDO := cap_CDO + 1; ELSIF total_pb > 0 THEN cap_PB := cap_PB + 1; END IF; INSERT INTO PACT_SALIDA(PLH_SRAMO,PLH_SPOLIZA,PLH_SCODIGO_PROVINCIA,PLH_SCODIGO_POSTAL,PLH_SPROVINCIA,PLH_ISUMA_ASEGURADA_CTE,PLH_ISUMA_EXPUESTA_CTE,PLH_ISUMA_ASEGURADA_CDO,PLH_ISUMA_EXPUESTA_CDO,PLH_ISUMA_ASEGURADA_PB,PLH_ISUMA_EXPUESTA_PB,PLH_NCOASEGURO) VALUES(ramo,poliza,codigo_pr,codigo_pos,provincia,total_cte,total_cte_ex,total_cdo,total_cdo_ex,total_pb,total_pb_ex,coaseguro_n); ELSE DELETE FROM PRINCIPAL WHERE ramo = '061' AND aplicacion = 'RSM'; END IF; END; Thanks