Solved

How do I figure out what is causing an error in a procedure that I am executing

Posted on 2011-02-21
22
856 Views
Last Modified: 2012-05-11

here is the section of the procedure that I receive the error from:

l_insert_filePath := 'INSERT INTO ';
l_insert_filePath := l_insert_filePath || targetSchema;
l_insert_filePath := l_insert_filePath || '.MULTI_PAGES (SITE, PDF_NAME, PDF_PAGE_FILE_PATH, BH_ID, DIR_NUM, DB_INSERTED, DB_UPDATED) ';

l_insert_filePath := l_insert_filePath || ' SELECT  REPLACE( REPLACE(NVL(ORG_PERFORMING_BCO, ''N/A''), ''-BCO''), ''-SNIM''), ID || ''_sti_document'' , FILE_NAME, ';

l_insert_filePath := l_insert_filePath || iCounter || ', 0, sysdate, NULL FROM '
;

l_insert_filePath := l_insert_filePath || sourceSchema;
l_insert_filePath := l_insert_filePath || '.WS_STI_RECORD WHERE id = ' || stiRec
ordId ;

execute immediate l_insert_filePath;



commit;
END;


There are 2 triggers on the multi_pages table. One trigger populates the ID field when a record is added to the table using nextval. The other triggers populate other fields such as date and foreign key values and one trigger that creates a record in another table. I don't know what is causing the below error. The procedure is not trying to insert a value into the ID field and the trigger that populates that field is functioning properly as far as I can tell.

SQL Error: ORA-00001: unique constraint (TEMS_ADM_ORIG.PK_MP) violated
ORA-06512: at "TEMS_WS_TEST.COPYSTIRECORDTOBIBHOLDINGS", line 145
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.

Could anyone help me to figure out what could be the problem and  how to resolve it?
0
Comment
Question by:sikyala
  • 8
  • 5
  • 4
  • +3
22 Comments
 
LVL 4

Expert Comment

by:pinkuray
ID: 34948604
Is your insert is good to run?

Is the below query is one what you are tying to insert from source to target:

 
insert
INTO <<target_sechma>>.MULTI_PAGES
  (
    SITE,
    PDF_NAME,
    PDF_PAGE_FILE_PATH,
    BH_ID,
    DIR_NUM,
    DB_INSERTED,
    DB_UPDATED
  )
SELECT REPLACE( REPLACE(NVL(ORG_PERFORMING_BCO, 'N/A'), '-BCO'), '-SNIM'),
  ID
  || '_sti_document' ,
  FILE_NAME,
  0,
  0,
  sysdate,
  null
from <<SOURCE_SCHEMA>>.WS_STI_RECORD
WHERE id = <<stiRecordId>>

Open in new window

0
 
LVL 4

Expert Comment

by:pinkuray
ID: 34948611
What I see that your error is unique constraint which is referring to the ID...

I think you need to check the query 1st if you are inserting the values and data properly.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34948706
The Best Thing is to add few debug messages to your trigger code and see where exactly it is failing. As the error message says, for some reason duplicates values are being inserted/updated and hence the cause.

SELECT  REPLACE( REPLACE(NVL(ORG_PERFORMING_BCO, ''N/A''), ''-BCO''), ''-SNIM''), ID || ''_sti_document'' , FILE_NAME, ';
l_insert_filePath := l_insert_filePath || iCounter || ', 0, sysdate, NULL FROM '
;
l_insert_filePath := l_insert_filePath || sourceSchema;
l_insert_filePath := l_insert_filePath || '.WS_STI_RECORD WHERE id = ' || stiRec
ordId ;

Just the select part of the insert statement, how many records do you expect to return ? 1 or more than 1 ( by any chance if more than 1 is causing the error ) ?
0
 

Author Comment

by:sikyala
ID: 34950007
where would I put a debug message in the procedure
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34950017
use dbms_output.put_line(..) or insert log messages into log/debug table whichever you prefer and easy for you.
0
 

Author Comment

by:sikyala
ID: 34950055
what would be the syntax for using dbms_output.put_line
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34950067
like this :

dbms_output.put_line('this is a debug message');
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34950082
also add exception handling sections to your pl/sql code.

Can you give your trigger code here to check for any obvious errors ?
0
 
LVL 32

Expert Comment

by:sarabande
ID: 34950718
it is a primary key constraint what means that either the insert trigger isn't enabled or the sequence for the primary keys creates already existing keys.

you can select the "current value" of sequence by sequence_name.currval in a select statement.

Sara
0
 
LVL 47

Expert Comment

by:schwertner
ID: 34950723




PROCEDURE syncr (p_imp_exp VARCHAR2) IS
  v_count           INTEGER;
  v_count_start     INTEGER;
 
BEGIN

  dbms_output.enable(100000);

------  PUT YOUR CODE HERE !!!!!!
 
     EXCEPTION
  WHEN OTHERS THEN
     dbms_output.put_line('Errors by the SYNCR encountered.');
     dbms_output.put_line(substr(sqlerrm,1,254));

END syncr;

Run it so:


SQL> set serveroutput on

SQL> execute syncr
0
 

Author Comment

by:sikyala
ID: 34951104
only 1 record is being inserted
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34951164
What everyone is hinting at is capture the output of the dynamic SQL and see if it runs from sqlplus.

This will help you narrow down what the problem is.  Add the dbms_ouptut above the execute immediate.


...
dbms_ouptut.put_line(l_insert_filePath);
execute immediate l_insert_filePath;
...


0
 

Author Comment

by:sikyala
ID: 34954508
I tried a regular insert to see if the procedure was really the problem and I got the same error. So I disabled all the triggers and reinabled the trigger that inserts the ID value and I got the error. Here is the trigger

BEFORE INSERT ON MULTI_PAGES
FOR EACH ROW
DECLARE
    iCounter MULTI_PAGES.ID%TYPE;
    cannot_change_counter EXCEPTION;
BEGIN
        Select SEQ_MP.NEXTVAL INTO iCounter FROM Dual;
        :new.ID := iCounter;
EXCEPTION
     WHEN cannot_change_counter THEN
 raise_application_error(-20000, 'Cannot Change MULTI_PAGES Value');
END;

When I select nexval from dual I get 13086
when I select max(id) value I get 590964

I thought that maybe the numbers are off because I loaded the data from production and the sequence and trigger were functioning off old data before I deleted it and loaded in the production data. Could someone help me to figure out how to resolve this?


0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 34954610
As hinted at by sarabande, it appears your sequence is generating duplicate keys.

Just figure out a new 'max' val, subtract the current val, alter the sequence, get a new val, alter the sequence back:

if you want the 'next' sequence to be: 590965 then (assuming the 'nextval' is still the same):

ALTER SEQUENCE SEQ_MP INCREMENT BY 590964-13086;
select SEQ_MP.NEXTVAL from dual;
ALTER SEQUENCE SEQ_MP INCREMENT BY 1;

0
 

Author Comment

by:sikyala
ID: 34954882
I dropped the sequence

drop sequence SEQ_MP;

I got the max value from the table

select max(id) from multi_pages;

then I created it again

create sequence SEQ_MP start with 590964;

and I still get the same error. What should I do differently?

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34955039
Since we do not understand your system I'm not sure how we can assist with this.

You need to figure out why that insert is causing a duplicate key.

Please post the insert that fails.  What is the primary key for the table?
0
 

Author Comment

by:sikyala
ID: 34955136
the primary key is ID

I am working with someone else who created the procedure and attempted to execute it after my change and received this error

call copystirecordtobibholdings ('TEMS_ADM_ORIG', 'TEMS_WS_TEST', 1972515)
SQL Error: ORA-00001: unique constraint (TEMS_ADM_ORIG.PK_MP) violated
ORA-06512: at "TEMS_WS_TEST.COPYSTIRECORDTOBIBHOLDINGS", line 145
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.

*Action:   Either remove the unique restriction or do not insert the key.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34955240
We understand the error.  That's not the issue.

The issue is us understanding your system.  Since we cannot do that, please post the insert statement you captured and ran in sqlplus that reproduced the error.

>>after my change and received this error

What change?  If it worked before and doesn't now, you've introduced the problem.  
0
 

Author Comment

by:sikyala
ID: 34955397
basically a record is being copied from a table in tems_ws_test schema with record id value of 1972515 to the multi_pages table in the tems_adm_orig schema where there is a trigger executed before inserting a record that gives the value of the id field from a sequence
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34955920
Again, we get that.  What is your reluctance to respond to my questions?

To troubleshoot this, we need to break it all down to the smallest pieces.  To do this, we need to see the insert statement.

For now, let's forget about the dynamic pl/sql code.  That is why we wanted you to capture the output.

If you will not respond to our requests, we'll be unable to help you.
0
 
LVL 32

Assisted Solution

by:sarabande
sarabande earned 250 total points
ID: 34960126
create sequence SEQ_MP start with 590964;

and the maximum was 590963 ?

and no one adds records parallel?

you may try with

  create sequence SEQ_MP start with 1000000;

to make sure it is new key.

Sara
0
 

Author Closing Comment

by:sikyala
ID: 34981180
I did this
ALTER SEQUENCE SEQ_MP INCREMENT BY <I manually subtracted this and used the answer 590964-13086>;
select SEQ_MP.NEXTVAL from dual;
ALTER SEQUENCE SEQ_MP INCREMENT BY 1;

and ran procedure successfully
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
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.
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

705 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

18 Experts available now in Live!

Get 1:1 Help Now