ORACLE: insert all records that can be regardless of whether there is an error

Good afternoon, I have the following process:
 1. Data stored in a temporary table
 2. I have a sp that reads information from the temporary table and records in other tables, the problem is that not all records are processed. For example: I have 1000 records and there is an error in the log 501 and 502, then the record just inserted to the 500 and the process is canceled.

 As I can do? for though no errors insert all records that are missing, ie continue with registration 503, 504, etc.

 Below is the sp
home--error-no-hace-commit.txt
enrique_aeoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
If you have the exception trapped properly it will not.

Since we don't have your tables we cannot create and debug your procedure.

Below is a simple test case that shows this is possible.
drop table tab1 purge;
create table tab1(col1 char(1) primary key);

drop table tab2 purge;
create table tab2(col1 char(1));


insert into tab2 values('a');
insert into tab2 values('b');
insert into tab2 values('b');
insert into tab2 values('c');
commit;

begin
	for i in (select col1 from tab2) loop
		begin
			insert into tab1 values(i.col1);
			exception when others then null;
		end;
	end loop;
end;
/

select * from tab1;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
On mobile and cannot read all the code but check out nested blocks.

Something like:

... Your code...
Begin
   Insert into table values('a');
   Exception when others
     Then null;
End;
... The rest of the loop/code
0
 
sdstuberCommented:
wrap your inner insert with a begin/exception/end block



                 begin    
                     INSERT INTO SIGAT_ACUERDO
                          (N_TIPO_ACUERDO, V_MSISDN, D_FEC_ACUERDO, N_IDACUERDO, N_MOTIVO_ACTUALIZACI, V_NRO_CONTRATO,
                          D_FEC_CONTRATO, N_TIPO_CLTE, V_NRO_PCS, N_IDPLAZO_ACUERDO, N_IDPLAN, V_IDCAMPANA,
                          ...
                          V_DESCRIPCION_PLAN, V_RAZON_SOCIAL, V_DIRECCION_CLIENTE)
                      VALUES
                        (VL_TIPOACUERDO, V_NUMERO_TELEFONO, SYSTIMESTAMP, VL_CODACUERDO,
                         NULL, V_NUMERO_CONTRATO, V_FECHA_CONTRATO, V_TIPO_CLIENTE,
                           ...
                         V_CLIENTE, V_PLAN2, V_RAZON_SOCIAL, V_DIRECCION);
                 exception
                     when others then
                          VL_MENSAJE:='ERROR: GENERAR ACUERDO SAP' + SQLERRM;
                            PKG_SIGA_TRANSACCIONES.SP_REGISTRAR_LOG(VL_MENSAJE,VG_COD_ERROR,V_NUMERO_TELEFONO,VL_TIPOACUERDO,V_CO_ID,
                            V_CUSTOMER_ID,TO_DATE(SYSDATE,'DD/MM/YYYY'),P_USUARIO,VL_PROCESO,VL_OBJETO);
                     
       
                            UPDATE SIGAT_ZPV_CONTRATO_TMP
                                SET C_FLAG_CARGA = '1'
                             WHERE C_NUMERO_CONTRATO = V_NUMERO_CONTRATO;
                 end;
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
also, you may want to consider rewriting your sql to avoid the loops altogether and do some bulk processing
0
 
enrique_aeoAuthor Commented:
Hi, sorry I was sick friends. sdstuber you can tell me a little more about Some bulk processing
0
 
slightwv (䄆 Netminder) Commented:
I'm not sdstuber but check the docs and Google for 'bulk collect' and 'forall'.
0
 
enrique_aeoAuthor Commented:
I made the change requested but I get an error, I am attaching all the sp for better help, thanks
SI-ACUERDO-SAP.txt
0
 
slightwv (䄆 Netminder) Commented:
>>but I get an error

What is the error?  The same one or did it change?
0
 
enrique_aeoAuthor Commented:
does not compile
is the location of the end begin sdstuber suggested me, so I have attached all the source code to show me where I need to control. The problem is I have 1000 rows, inserted to the 800, then falls and does not insert, the idea is to continue inserting
0
 
sdstuberCommented:
>>> The problem is I have 1000 rows, inserted to the 800,

if it doesn't compile,  how did it run to insert 800 rows?


if it didn't compile,  what is the error?

if it did compile and run, what was the error when it failed?
0
 
enrique_aeoAuthor Commented:
this is the error
ORA-00001: unique constraint (BD_SIGA.UK_SIGAT_ACDO) violated) N_TIPO_ACUERDO, V_MSISDN, D_FEC_ACUERDO

I wish is to continue processing until the last record
0
 
sdstuberCommented:
are you sure that error is coming from this procedure?

if so,  where in the procedure is the error coming from?

0
 
enrique_aeoAuthor Commented:
ok my friend, this sp is called from a shell, attached to the shell
SH01-SIGA-Interface-PVU.txt
0
 
sdstuberCommented:
so, the error was not in this procedure?
0
All Courses

From novice to tech pro — start learning today.