[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-09-14
14
Medium Priority
?
311 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 78

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 78

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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

834 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