Catch the value on IINSERT on which it fails..

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;
Rao_SAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
>>> 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
 
Rao_SAuthor Commented:
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
 
sdstuberConnect With a Mentor Commented:
which insert statement is at line 6 ?

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Rao_SAuthor Commented:
maybe i can display the CURVAL of th sequence....? that will let me know which insert statement is failing...?
0
 
Rao_SAuthor Commented:
line 6 is nothing but .....'exception when others then'....... inside the block, there is only one insert statement....
0
 
sdstuberConnect With a Mentor Commented:
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
 
sdstuberConnect With a Mentor Commented:
>>>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
 
Rao_SAuthor Commented:
>>>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
 
sdstuberConnect With a Mentor Commented:
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
 
sdstuberConnect With a Mentor Commented:
>>> .i wonder if the sequence number is set back...?


no - sequences are not transactional
0
 
awking00Connect With a Mentor Commented:
You might try error logging if you are using 10gR2 or better. See this link
http://www.orafaq.com/node/76
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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
 
Rao_SAuthor Commented:
thank you.....keeping a watch on sequence worked......
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.