Solved

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

Posted on 2011-09-14
14
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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