Geek Answers Handbook

GUID / RAW stored in oracle almost match except for last 4 characters

We noticed some strange behavior in our production data regarding the GUIDs that we store from the .Net DataSet in the Oracle RAW column.

We have the following setup:

  • Table A has a RAW Id column
  • Table B has a RAW ResponsibleObjectId column

The column ResponsibleObjectId is populated with the identifier of table A when it is created in the function of table A (not always so, so there is no FK in the column).

This happens at our .Net server level. Two tables are in two separate data sets. These 2 data sets are then stored in the oracle DB (11g).

In most cases, this works, but in some cases (for example, 1 / 10.000.000 lines), the resulting hints are not exactly the same:

ResponsibleObjectId ID 665B8FFDE5A04163A96D96A25702665B 665B8FFDE5A04163A96D96A257023065 325C8AB000434503B8D2F980B33B325C 325C8AB000434503B8D2F980B33B4B58 AF831B5207E04D2ABE0E3ADAC802AF83 AF831B5207E04D2ABE0E3ADAC8023FA1 88DD5AF244DC4EA08075DB53CA1988DD 88DD5AF244DC4EA08075DB53CA19072E 75A5E5111DEE4021BA6EB016178775A5 75A5E5111DEE4021BA6EB01617876E66

They match, the last 4 characters are expected.

We save the GUID using the stored procedure as follows:

.Net DAL

 OracleCommand cmdSpInsert = (OracleCommand)database.GetStoredProcCommand("PKG_TableB.InsertTableB"); database.AddInParameter(cmdSpInsert, "PAR_ResponsibleObjectId", DbType.StringFixedLength, 36, "ResponsibleObjectId", DataRowVersion.Current);

Oracle SP

 PROCEDURE InsertTableB ( ... PAR_ResponsibleObjectId IN CHAR, ... ) IS BEGIN INSERT INTO StockMov ( ... HEXTORAW(REPLACE(UPPER(PAR_ResponsibleObjectId), '-','')), ... ); END InsertTableB;

We do not know what causes this strange behavior. We saw that this happened in one similar case on two different columns in our table A, so it does not seem to be related specifically to table B.

If you need more information, please ask and I will update the question.

Any help is greatly appreciated in solving our mystery! :)

Edit As pointed out in the comments, assigning a ResponsibleObjectId column is very simple, for example:

 dataSetB.ResponsibleObjectId = dataSetA.Id

Also, two data sets are stored in one transaction.

+6
source share
2 answers

byte [] idGuid = Guid.NewGuid (). ToByteArray ();

dataSetA.Id = idGuid;

dataSetB.ResponsibleObjectId = idGuid;

/ * do not execute dataSetB.ResponsibleObjectId = dataSetA.Id

do not do any data type conversions - store it in binary / raw * /

+3
source

We have very strong indications that these problems arose because their database setup did not meet our requirements. Oracel db does not work on a dedicated machine and does not have enough memory.

Cases of incidents in many cases can be correlated with the problems of their RAC.

We will now try to convect the client to improve the quality of our db server.

0
source

More articles:


All Articles