Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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;
0
Rao_S
Asked:
Rao_S
8 Solutions
 
sdstuberCommented:
>>> 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
 
sdstuberCommented:
which insert statement is at line 6 ?

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
sdstuberCommented:
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
 
sdstuberCommented:
>>>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
 
sdstuberCommented:
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
 
sdstuberCommented:
>>> .i wonder if the sequence number is set back...?


no - sequences are not transactional
0
 
awking00Commented:
You might try error logging if you are using 10gR2 or better. See this link
http://www.orafaq.com/node/76
0
 
johnsoneSenior 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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now