Home » SQL & PL/SQL » SQL & PL/SQL » Merge error. bug or not?
Merge error. bug or not? [message #217849] Mon, 05 February 2007 20:23 Go to next message
coast
Messages: 2
Registered: February 2007
Junior Member
Hello,

I want to consult the problems which occurred in database, the problems are as follows:

programmed logic:

the program interpreting the document, and then extract 50,000 pieces to enter database for processing,but error happends when using the "merge" sql

error sq1 as follows:


Merge into account.T_ryt_Fee M using account.t_ryt_stateinfo_tmp C
on (M.SubScrbID=C.SubScrbID )
when matched then
update set M.Balance=C.fee,M.ChgDate=sysdate
when not matched then
insert (SubScrbID,SrvgNum,Balance,ChgDate)
values (C.subscrbid,C.msisdn,to_number(C.fee),sysdate) );


erros as follows:

sqlca.sqlcode : -1 ,sqlca.sqlerrm.sqlerrmc : ORA-00001: unique constraint (ACCOUNT.PK_T_RYT_FEE) violated
sqlca.sqlerrd[2] : 0 , sqlca.sqlerrd[4] :0

causes of fallacious inference:

First, It showed errors when the sql "MERGE" was processed, but the conflicting data was not found in the table according to the errors.

In addition, when rerun the MERGE, the errors maybe change to other place, that is, the former errors can be perform successfully ,and for sure that's not because the repeatint record cause the error!

second, After removing the unique key of the table(account.T_ryt_Fee:SUBSCRBID), it indeed found that there were 2 pieces conflicting record after performing.But this can not be convinced according to the theory: It should not occur repeating data because there is sole index in the original table. The conflicting data had not been found according to the errors occurred in the original document.and that the errors happened every day after i dispose the data ;

so i quite sure not data problem,but the sql of "Merge",I even doubt whether it is the bug of oracle.


Could you please to offer some information or tracing methods? It will be highly appreciated if you can help me!


Hopefully you can understand chinese's english and understand what i mean!


Waiting for your reply!





table structure as follows:

-- Create table
create table ACCOUNT.T_RYT_FEE
(
SUBSCRBID CHAR(10) not null,
SRVGNUM VARCHAR2(20) not null,
BALANCE NUMBER(10) not null,
CHGDATE DATE not null,
CREATEDATE DATE
);

-- Create/Recreate primary, unique and foreign key constraints
alter table ACCOUNT.T_RYT_FEE
add constraint PK_T_RYT_FEE primary key (SUBSCRBID);

-- Create/Recreate indexes
create index ACCOUNT.IDX_RYT_FEE_SRVG on ACCOUNT.T_RYT_FEE (SRVGNUM);



-- Create table
create table ACCOUNT.T_RYT_STATEINFO_TMP
(
SUBSCRBID CHAR(10) not null,
MSISDN VARCHAR2(20),
PROVIDER VARCHAR2(20),
STATE VARCHAR2(6),
FIRSTCALL CHAR(1),
FEE NUMBER(10),
EXPDATE DATE,
SRVGPLNID CHAR(7),
SRVGPLNIDTIMER VARCHAR2(20),
FILENAME VARCHAR2(40),
OLDSTATE2 CHAR(1)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ACCOUNT.T_RYT_STATEINFO_TMP add primary key (SUBSCRBID) ;
-- Grant/Revoke object privileges
grant select on ACCOUNT.T_RYT_STATEINFO_TMP to PUBLIC;

Re: Merge error. bug or not? [message #217869 is a reply to message #217849] Mon, 05 February 2007 22:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If the row already exists in the target table, then it should be UPDATED. This means you should not get get ORA-00001.

But what about when you have 2 rows in the source with the same key? They will both attempt to insert, and the second one will fail.

Check your source table for duplicates.

Ross Leishman
Re: Merge error. bug or not? [message #218008 is a reply to message #217869] Tue, 06 February 2007 06:26 Go to previous messageGo to next message
coast
Messages: 2
Registered: February 2007
Junior Member
Thank you for your help~~

but i quite sure not data problem~~

because the source talbe has the " Unique index "

"alter table ACCOUNT.T_RYT_STATEINFO_TMP
add primary key (SUBSCRBID)"

that's i can't understand..
Re: Merge error. bug or not? [message #218107 is a reply to message #218008] Tue, 06 February 2007 19:21 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why are you using CHAR(10)? Why not VARCHAR2(10)?

Make them VARCHAR2 and see if it still happens.
Previous Topic: UTL_FILE
Next Topic: Date function
Goto Forum:
  


Current Time: Mon Dec 05 09:17:14 CST 2016

Total time taken to generate the page: 0.07612 seconds