Solved

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

Posted on 2011-09-14
14
303 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 77

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 74

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 74

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
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: 36552576
Hi, sorry I was sick friends. sdstuber you can tell me a little more about Some bulk processing
0
 
LVL 77

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 77

Expert Comment

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

What is the error?  The same one or did it change?
0
 

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 74

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 74

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 77

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 74

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

860 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