Solved

Catch the value on IINSERT on which it fails..

Posted on 2011-09-22
13
282 Views
Last Modified: 2012-05-12
sorry for asking a silly question....
i have about 100 insert statement and it fails on unique constraint, i need to catch the row on which it fails...... here is one statement.... in a insert statement with no cursor or a variable, how to find the value on which it is failing..?
set scan off;
begin
INSERT INTO AQMCD01.tqm_cd_code_values (CDE_VALUE_PK, CDE_TYPE, CDE_VAL, ACTIVE_IND, PARENT_CDE_VALUE_PK, CREATED_PROG_ID, UPDATED_PROG_ID)
VALUES ((aqmcd01.cde_value_pk_seq.NEXTVAL), 'MAT_TYPE', 'CONVERSION', 'Y', null, 'AP322_QMCD_CODES_08112011', 'AP322_QMCD_CODES_08112011');
exception when others then
   rollback;
   RAISE_APPLICATION_ERROR (-20000,'Error'||sqlcode||sqlerrm);
end;
0
Comment
Question by:Rao_S
13 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 376 total points
ID: 36580198
>>> how to find the value on which it is failing..?

you have all the values right there.

(aqmcd01.cde_value_pk_seq.NEXTVAL), 'MAT_TYPE', 'CONVERSION', 'Y', null, 'AP322_QMCD_CODES_08112011', 'AP322_QMCD_CODES_08112011')


if you don't know the sequence value,  you can select it

  aqmcd01.cde_value_pk_seq.curval


my guess, based on the column and sequence names is the cde_value_pk  is being duplicated  probably because your sequence is out of synch with the data already in the table
0
 

Author Comment

by:Rao_S
ID: 36580221
no...it fails on unique constraint.....
the problem is ...there are 100 insert statement... in the block... which insert statement is failing...?
i just gave an example of one insert statement...

exception when others then
   rollback;
   RAISE_APPLICATION_ERROR (-20000,'Error'||sqlcode||sqlerrm);
end;
Error at line 2
ORA-20000: Error-1ORA-00001: unique constraint (AQMCD01.XQM_CD_CODE_VALUES_U01) violated
ORA-06512: at line 6
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 376 total points
ID: 36580234
which insert statement is at line 6 ?

0
 

Author Comment

by:Rao_S
ID: 36580236
maybe i can display the CURVAL of th sequence....? that will let me know which insert statement is failing...?
0
 

Author Comment

by:Rao_S
ID: 36580252
line 6 is nothing but .....'exception when others then'....... inside the block, there is only one insert statement....
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 376 total points
ID: 36580262
add instrumentation  (dbms_output, insert to log table, utl_file, dbms_application_info, etc)

if you want to track execution line-by-line and the oracle line numbers in the error messages aren't working for you then you'll need to track it yourself.

alternately,  since you are using a sequence,  look at the sequence value before you start inserting, then look at it afterward.  Count through your statements according to the increment of the sequence.

for example,  before inserts sequence = 1234
after error sequence = 1302
assuming your sequence increments by 1 every time then your last error occurred on insert (1302-1234) or 68
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.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 376 total points
ID: 36580271
>>>inside the block, there is only one insert statement....

you said there were 100 inserts?

if each insert is wrapped in its own block, you already have your answer.
if you every error message returns the same text, then change it so it gives you useful info.
as with any other instrumentation, error messages must be helpful.

a speedometer that only said 0 or 50 wouldn't be helpful, you need the incremental values for that instrument to tell you anything.
0
 

Author Comment

by:Rao_S
ID: 36580322
>>>inside the block, there is only one insert statement....

inside my example block i took only one insert statement.....
but in the actual block there are about 100 insert statements...
tracking by the sequence number is a good idea... but in the 'exception' i rollback...i wonder if the sequence number is set back...?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 376 total points
ID: 36580329
A simple method of making your error messages more useful is to use

DBMS_UTILITY.format_error_backtrace

also, set the 3rd parameter of raise_application_error to TRUE so it will report the entire error history


 raise_application_error(-20001,DBMS_UTILITY.FORMAT_ERROR_backtrace,true);
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 376 total points
ID: 36580332
>>> .i wonder if the sequence number is set back...?


no - sequences are not transactional
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 62 total points
ID: 36581430
You might try error logging if you are using 10gR2 or better. See this link
http://www.orafaq.com/node/76
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 62 total points
ID: 36581457
This sounds like there is a trigger on the table and the error is coming from the trigger.  I say that because the constraint name looks out of place.  Can we get more information on the constraint?

select * from dba_constraints where owner = 'AQMCD01' and constraint_name = 'XQM_CD_CODE_VALUES_U01';

select * from dba_cons_columns where owner = 'AQMCD01' and constraint_name = 'XQM_CD_CODE_VALUES_U01';
0
 

Author Closing Comment

by:Rao_S
ID: 36598604
thank you.....keeping a watch on sequence worked......
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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

760 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

22 Experts available now in Live!

Get 1:1 Help Now