Insert Fails - though Insert if valid

[DBohner-(db)]

I'm getting a 'Failed to Commit' error in a P4210 Transaction - even though the 'source' of the failed to commit appears to be valid.

Inside the debug log - the insert into the F42420 'appears' to fail. However, if I open Aqua (my favorite query/sql tool) and cut/paste the Insert statement ... whala - it inserts (gophigure).

Why is the insert unsuccessful from within E1 - while the exact same statement is successful externally (cut/paste).

(db)

This client is:
Solaris | Oracle 10g | OAS | 8.12 | 8.96.2.0

PS - My wife cut a Christmass album, feel free to download at:
http://www.existinglight.net/music/2007ChristmasAlbum.htm

=================================
Snipped from the Ranks of the JDE Debug Log:

Dec 17 23:52:47.175240 - 2836/2608 WRK:Starting jdeCallObject Entering JDB_InsertTable(Table F42420)
Dec 17 23:52:47.175241 - 2836/2608 WRK:Starting jdeCallObject ORACLE DBInitReq conn=22CE2C60 requ=22DB26C8 ux103 (jde812pd) new [ 4]
Dec 17 23:52:47.175242 - 2836/2608 WRK:Starting jdeCallObject INSERT INTO PRODDTA.F42420 (ALKCOO, ALDOCO, ALDCTO, ALLNID, ALCORD, ALUPMJ, ALTDAY, ALUSER, ALRFRV, ALAPSR, ALAPPV, ALAPPJ, ALATIM, ALSFXO, ALMCU, ALLITM, ALUORG, ALUOM, ALUPRC, ALAEXP, ALFUP, ALFEA, ALLOCN, ALLOTN, ALDRQJ, ALDSC1, ALLTTR, ALNXTR, ALUOM4, ALSOQS, ALSOBK, ALSOCN, ALPDDJ, ALPPDJ, ALCNDJ, ALRSDJ, ALUNCS, ALECST, ALFUC, ALFEC, ALPRP5, ALVEND, ALSHAN, ALDSC2, ALTAX1, ALWTUM, ALITWT, ALRYIN, ALINMG, ALMOT, ALDTYS, ALCARS, ALLOB, ALEUSE, ALEMCU, ALUPC1, ALUPC2, ALUPC3, ALASN, ALPRGR, ALPTC, ALPRIO, ALRCD, ALCADC, ALGLPT, ALSBL, ALSBLT, ALSRP1, ALSRP2, ALSRP3, ALSRP4, ALSRP5, ALAFT, ALSHCM, ALSHCN, ALFRAT, ALROUT, ALSTOP, ALZON, ALITVL, ALVLUM, ALACOM, ALEXR1, ALTXA1, ALSQOR, ALUOM2, ALMCLN, ALXDCK, ALXPTY, ALDRQT, ALPDTT, ALOPTT, ALADTM, ALPMDT, ALPSTM, ALRLNU, ALPSIG, ALRLDJ, ALRLTM, ALXKCO, ALXORN, ALXCTO, ALXLLN, ALXSFX, ALPID, ALSPATTN, ALPRAN8, ALPRCIDLN, ALCCIDLN, ALSHCCIDLN, ALOPPID, ALOSTP, ALUKID, ALCATNM) VALUES ('00010',1000067.000000,'SF',4060.000000,0.000000,107351,234852.000000,'DBOHNE',' ',' ',' ',0,0.000000,'000',' 1001','900454 ',10.000000,'KT',0.000000,0.000000,0.000000,0.000000,' ',' ',107357,'KIT, HYB CONTROLS, 30RXN ','520','522','KT',10.000000,0.000000,0.000000,107345,107345,107365,107345,511356.000000,0.000000,0.000000,0.000000,' ',0.000000,128133.000000,' ','Y','LB',0.000000,'C',' ',' ',' ',100418.000000,' ',' ',' 1000',' ',' ',' ','STANDARD',' ','N30','0',' ',0.000000,'RE30',' ',' ','100','201','301',' ',' ','Y',' ',' ',' ',' ',' ',' ',0.000000,'GA',' ','S ','V390915000',10.000000,'KT',0.000000,' ',0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,' ',' ',0,0.000000,' ',0.000000,' ',0.000000,' ','P4210',' ',0.000000,0.000000,0.000000,0.000000,0.000000,' ',0.000000,' ')
Dec 17 23:52:47.175243 - 2836/2608 WRK:Starting jdeCallObject OCI0000178 - Unable to execute - INSERT INTO PRODDTA.F42420 (ALKCOO, ALDOCO, ALDCTO, ALLNID, ALCORD, ALUPMJ, ALTDAY, ALUSER, ALRFRV, ALAPSR, ALAPPV, ALAPPJ, ALATIM, ALSFXO, ALMCU, ALLITM, ALUORG, ALUOM, ALUPRC, ALAEXP, ALFUP, ALFEA, ALLOCN, ALLOTN, ALDRQJ, ALDSC1, ALLTTR, ALNXTR, ALUOM4, ALSOQS, ALSOBK, ALSOCN, ALPDDJ, ALPPDJ, ALCNDJ, ALRSDJ, ALUNCS, ALECST, ALFUC, ALFEC, ALPRP5, ALVEND, ALSHAN, ALDSC2, ALTAX1, ALWTUM, ALITWT, ALRYIN, ALINMG, ALMOT, ALDTYS, ALCARS, ALLOB, ALEUSE, ALEMCU, ALUPC1, ALUPC2, ALUPC3, ALASN, ALPRGR, ALPTC, ALPRIO, ALRCD, ALCADC, ALGLPT, ALSBL, ALSBLT, ALSRP1, ALSRP2, ALSRP3, ALSRP4, ALSRP5, ALAFT, ALSHCM, ALSHCN, ALFRAT, ALROUT, ALSTOP, ALZON, ALITVL, ALVLUM, ALACOM, ALEXR1, ALTXA1, ALSQOR, ALUOM2, ALMCLN, ALXDCK, ALXPTY, ALDRQT, ALPDTT, ALOPTT, ALADTM, ALPMDT, ALPSTM, ALRLNU, ALPSIG, ALRLDJ, ALRLTM, ALXKCO, ALXORN, ALXCTO, ALXLLN, ALXSFX, ALPID, ALSPATTN, ALPRAN8, ALPRCIDLN, ALCCIDLN, ALSHCCIDLN, ALOPPID, ALOSTP, ALUKID, ALCATNM) VALUES BND1,:BND2,:BND3,:BND4,:BND5,:BND6,:BND7,:BND8,:BND9,:BND10,:BND11,:BND12,:BND13,:BND14,:BND15,:BND16,:BND17,:BND18,:BND19,:BND20,:BND21,:BND22,:BND23,:BND24,:BND25,:BND26,:BND27,:BND28,:BND29,:BND30,:BND31,:BND32,:BND33,:BND34,:BND35,:BND36,:BND37,:BND38,:BND39,:BND40,:BND41,:BND42,:BND43,:BND44,:BND45,:BND46,:BND47,:BND48,:BND49,:BND50,:BND51,:BND52,:BND53,:BND54,:BND55,:BND56,:BND57,:BND58,:BND59,:BND60,:BND61,:BND62,:BND63,:BND64,:BND65,:BND66,:BND67,:BND68,:BND69,:BND70,:BND71,:BND72,:BND73,:BND74,:BND75,:BND76,:BND77,:BND78,:BND79,:BND80,:BND81,:BND82,:BND83,:BND84,:BND85,:BND86,:BND87,:BND88,:BND89,:BND90,:BND91,:BND92,:BND93,:BND94,:BND95,:BND96,:BND97,:BND98,:BND99,:BND100,:BND101,:BND102,:BND103,:BND104,:BND105,:BND106,:BND107,:BND108,:BND109,:BND110,:BND111,:BND112,:BND113,:BND114)
Dec 17 23:52:47.175245 - 2836/2608 WRK:Starting jdeCallObject OCI0000179 - Error - ORA-00001: unique constraint (PRODDTA.F42420_PK) violated
Dec 17 23:52:47.192000 - 2836/2608 WRK:Starting jdeCallObject JDB9900401 - Failed to execute db request
Dec 17 23:52:47.192002 - 2836/2608 WRK:Starting jdeCallObject JDB3400009 - Failed to perform Insert for F42420
Dec 17 23:52:47.192004 - 2836/2608 WRK:Starting jdeCallObject JDB9901232 - Canceling transaction because: TC052 InsertTable: Insert failed
Dec 17 23:52:47.192006 - 2836/2608 WRK:Starting jdeCallObject Cancelling Transaction : 2177257482_2836_3856_240765189
Dec 17 23:52:47.192007 - 2836/2608 WRK:Starting jdeCallObject Exiting JDB_InsertTable with Failure(Table F42420)

 

[johndanter]

unique constraint (PRODDTA.F42420_PK) violated

means you have violated the unique key on that table.

you're trying to enter the same key data record twice. You can't do that, each must be unique

 

[mdalton]

Hey Daniel,

The log says there's a primary key violation. I'd bounce services to see if the data is cached on the web client. If that doesn't work, turn off auditing on the P4210, and see if the record commits to the database.

All the Best,

Mark

 

[Larry_Jones]

Daniel,

here's my guess.

The clue is the "Error - ORA-00001: unique constraint (PRODDTA.F42420_PK) violated " message.

I'm assuming that transaction processing is turned on, so that multiple statements / DMLs are included within the transaction. What I believe to be happening is that the duplicate key (unique constraint) error is occurring because a prior insert WITHIN THE SAME TRANSACTION has the same key/constraint value(s). In other words the program is attempting to insert the same values twice.

Your insert via your SQL tool worked because when the commit failed the duplicate row already in the database had its insert rolled back.

Make sense?

 

[DBohner-(db)]

Larry,

I keep forgetting that you are a NorthWest Local!

'turn off auditing on the p4210' - ?? Intrigued - I'm unaware if/how this is done. Can you share?

note: I plugged in the SQL statement at the same time the error popped up on the log (while debugging). To my knowledge, an opportunity for a rollback in any sub-functions had not taken place, yet.

Anyone ever caught this as a tools or corrupt indexes issue?

(db)

 

[Larry_Jones]

I don't have a clue as to what the auditing statement was about either.
Unless you have a trigger of some kind firing that is writing entries into a separate table - and that's the actual cause of the failure? Doesn't happen in 8.9 I know.

Regarding the external SQL insert statement, since it's outside the program's transaction scope, no error would occur. The program inserted record is not truly inserted in the database until the transaction commits, therefore its only visible inside the scope of the current transaction. So I stick with my original guess.

Question: have you done any substantial mods here that would affect database I-O?

 

[mdalton]

F42420 is the audit log transaction file. There is a processing option for Audit Log. Blank means auditing is not used for orders, 1 means audit log is used for orders. If the processing option is set to 1 then sold to billing instructions function is called, as well as B4205102.