Home » SQL & PL/SQL » SQL & PL/SQL » insert into wrong column sometimes (oracle10.2.0.1.0(64bit),HPUX 11.23)
icon4.gif  insert into wrong column sometimes [message #433571] Thu, 03 December 2009 01:03 Go to next message
w2.doom
Messages: 3
Registered: December 2009
Location: CHN
Junior Member
I have a table as below,my application will insert records into this table,in some situation,data will insert into the wrong column!

for example, the value '102' which should be inserted into column 'retcode' was inserted into column 'retmsg',meanwhile
the retmsg value was inserted into another column 'reqcontent'..

this situation occured in very small probability,for now I cann't get what's the precondition of that. but I use 10046 getting a trace log,which contain 2 times sql execute,first was fine,the second is the error one,I can found oracle already bind
the wrong value,eg, 102 has been bind to Bind#6 which is retcode,
should be bind to Bind#5..

can anybody help me to analyze how to resolve this?thanks in advance!

*env info:
oracle10.2.0.1.0
HPUX 11.23
ojdbc14.jar

*table ddl:
SQL> desc int_cart_log;
Name Type Nullable Default Comments
---------- -------------- -------- ------- --------
REQID NUMBER(14)
CHECKTYPE VARCHAR2(32)
REQTIME DATE Y
REQCONTENT VARCHAR2(4000) Y
RSPTIME DATE Y
RSPCONTENT VARCHAR2(4000) Y
RETCODE VARCHAR2(16) Y
RETMSG VARCHAR2(1024) Y

*insert sql in application:
INSERT INTO int_cart_log (reqid,checktype,reqtime,reqcontent,rsptime,rspcontent,retcode,retmsg) VALUES(?,?,to_date(?,'yyyymmddhh24miss'),?,SYSDATE,?,?,?)

trace file see attachement
you can search this file with keywords 'DNB' to found 2 times insert bind information(I don't know why second one have not shown the sql..)

Re: insert into wrong column sometimes [message #433572 is a reply to message #433571] Thu, 03 December 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First check in your code if you always bind the correct variables.
Once you are sure, raise a SR to Oracle but the first answer will be upgrade to 10.2.0.4.

Regards
Michel

[Updated on: Thu, 03 December 2009 01:09]

Report message to a moderator

Re: insert into wrong column sometimes [message #433575 is a reply to message #433572] Thu, 03 December 2009 01:50 Go to previous messageGo to next message
w2.doom
Messages: 3
Registered: December 2009
Location: CHN
Junior Member
thanks for your quick response!

I'am sure the application is correct as it always use the same sql:
INSERT INTO int_cart_log (reqid,checktype,reqtime,reqcontent,rsptime,rspcontent,retcode,retmsg)
VALUES(?,?,to_date(?,'yyyymmddhh24miss'),?,SYSDATE,?,?,?)

and use
ps.setString(parameterIndex, argValue);
to bind values,

application already output every parameter before do insert,see below:

2009-12-03 10:11:50,182 [WebContainer : 3] DEBUG [199,DNB,20091203101150,%3C%3Fxml+versi
on%3D%221.0%22+encoding%3D%22UTF-8%22%3F%3E%0A%3CDGX%3E%3CSIGNONMSGSRQV1%3E%3CSONRQ%3E%3CDTCLIENT%3E12%2F03%2F2009+10%3A11%3A50+AM%3
C%2FDTCLIENT%3E%3CUSERID%3E577378%3C%2FUSERID%3E%3CUSERPASS%3Etelfort1%3C%2FUSERPASS%3E%3CLANGUAGE%3EEN%3C%2FLANGUAGE%3E%3CFI%3E%3CO
RG%3ETelfort%3C%2FORG%3E%3C%2FFI%3E%3CAPPID%3ECARTBM%28Credit+Acceptance+AND+Registration+Tool+FOR+the+Business+Market%29%3C%2FAPPID
%3E%3CAPPVER%3E0002%3C%2FAPPVER%3E%3C%2FSONRQ%3E%3C%2FSIGNONMSGSRQV1%3E%3CCREDITMSGSRQV2%3E%3CLOOKUPTRNRQ%3E%3CTRNUID%3E199%3C%2FTRN
UID%3E%3CLOOKUPRQ%3E%3CCTRY_CD%3ENL%3C%2FCTRY_CD%3E%3CPRIM_NME%3E%3C%2FPRIM_NME%3E%3CTLCM_NBR%3E%3C%2FTLCM_NBR%3E%3CADR_LINE%3E%3C%2
FADR_LINE%3E%3CNON_POST_TOWN%3E%3C%2FNON_POST_TOWN%3E%3CPOST_TOWN%3E%3C%2FPOST_TOWN%3E%3CPRIM_GEO_AREA%3E%3C%2FPRIM_GEO_AREA%3E%3CPO
ST_CODE%3E%3C%2FPOST_CODE%3E%3CDUNS_NBR%3E%3C%2FDUNS_NBR%3E%3CREGN_NBR%3E66666661%3C%2FREGN_NBR%3E%3CFILE_ID%3E%3C%2FFILE_ID%3E%3CRT
N_MAX%3E%3C%2FRTN_MAX%3E%3CPURC_REAS_CD%3E%3C%2FPURC_REAS_CD%3E%3CMTCH_TYPE%3E%3C%2FMTCH_TYPE%3E%3C%2FLOOKUPRQ%3E%3C%2FLOOKUPTRNRQ%3
E%3C%2FCREDITMSGSRQV2%3E%3C%2FDGX%3E,,102,java.net.ConnectException: Connection refused (errno:239)]
Re: insert into wrong column sometimes [message #433578 is a reply to message #433575] Thu, 03 December 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
and use
ps.setString(parameterIndex, argValue);
to bind values,

Do you ALWAYS bind ALL variables?
If you are sure, there is ONLY one solution: raise a SR to Oracle but as I said the first thing Oracle will reply is that you have to upgrade to 10.2.0.4.

Regards
Michel
Re: insert into wrong column sometimes [message #433579 is a reply to message #433578] Thu, 03 December 2009 02:15 Go to previous message
w2.doom
Messages: 3
Registered: December 2009
Location: CHN
Junior Member
yes,every time bind 7 values.

thanks for your response,I will try to upgrade oracle version,but I think it's not a easy way..Smile
Previous Topic: HELP on How create a STORED PROCEDURE
Next Topic: Sorting logic
Goto Forum:
  


Current Time: Tue Feb 11 10:24:56 CST 2025