Solved

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

Posted on 2011-09-14
14
300 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:enrique_aeo
  • 5
  • 5
  • 4
14 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36540059
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 36540071
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 36540079
also, you may want to consider rewriting your sql to avoid the loops altogether and do some bulk processing
0
 

Author Comment

by:enrique_aeo
ID: 36552576
Hi, sorry I was sick friends. sdstuber you can tell me a little more about Some bulk processing
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36552584
I'm not sdstuber but check the docs and Google for 'bulk collect' and 'forall'.
0
 

Author Comment

by:enrique_aeo
ID: 36562427
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36562443
>>but I get an error

What is the error?  The same one or did it change?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:enrique_aeo
ID: 36564352
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 36564397
>>> 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
 

Author Comment

by:enrique_aeo
ID: 36575787
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 36575856
are you sure that error is coming from this procedure?

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

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36575873
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
 

Author Comment

by:enrique_aeo
ID: 36577869
ok my friend, this sp is called from a shell, attached to the shell
SH01-SIGA-Interface-PVU.txt
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36582580
so, the error was not in this procedure?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now