• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 871
  • Last Modified:

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


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
sikyala
Asked:
sikyala
  • 8
  • 5
  • 4
  • +3
2 Solutions
 
pinkurayCommented:
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
 
pinkurayCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
sikyalaSenior Database AdministratorAuthor Commented:
where would I put a debug message in the procedure
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
use dbms_output.put_line(..) or insert log messages into log/debug table whichever you prefer and easy for you.
0
 
sikyalaSenior Database AdministratorAuthor Commented:
what would be the syntax for using dbms_output.put_line
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
like this :

dbms_output.put_line('this is a debug message');
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
also add exception handling sections to your pl/sql code.

Can you give your trigger code here to check for any obvious errors ?
0
 
sarabandeCommented:
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
 
schwertnerCommented:




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
 
sikyalaSenior Database AdministratorAuthor Commented:
only 1 record is being inserted
0
 
slightwv (䄆 Netminder) Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sarabandeCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now