CODEWITHSUNDEEP
c#.netoracle-databaseguid
Raf
Raf

Reputation: 683

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

We have noticed some strange behavior in our production data concerning the GUIDs we are storing from a .Net DataSet into an Oracle RAW column.

We have the following set up:

The ResponsibleObjectId column is filled with the ID of table A when it is created in function of Table A (not always the case, so there is no FK on the column).

This happens in our .Net server layer. The two tables are in 2 separate DataSets. These 2 datasets are then saved to an oracle DB (11g).

Most of the times this works, but in a few cases (like 1/10.000.000 rows) the resulting guids are not entirely the same:

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

They match, expect for the last 4 characters.

We save the GUIDs using a 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 have no idea what is causing this strange behavior. We've seen it happen in one similar case on 2 different columns in our Table A as well, so it does not seem related specifically to table B.

If any extra information is needed, please ask and I'll update the question.

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

Edit As asked in the comments, the assignment of the ResponsibleObjectId column very simple, like this:

dataSetB.ResponsibleObjectId = dataSetA.Id

Also the two datasets are saved in the same transaction.

Upvotes: 6

Views: 1719

Answers (2)

Raf
Raf

Reputation: 683

We have some very strong indications that these problems occured because their database setup was not according to our requirements. The oracel db is not running on a dedicated machine and did not have sufficient memory.

The incidents could in alot of cases be matched to problems on their RACs.

We'll try to convice the customer now to upscale their db server.

Upvotes: 0

RMAN Express
RMAN Express

Reputation: 496

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

dataSetA.Id = idGuid;

dataSetB.ResponsibleObjectId = idGuid;

/* don't do dataSetB.ResponsibleObjectId = dataSetA.Id

don't do any data type conversions - keep it binary/raw */

Upvotes: 3

Related Questions