Ztrain2100
asked on
Uniqueidentifier equivalent in Oracle
Greetings experts,
I may need to migrate an existing SQL Server database to Oracle. The database makes heavy use of the uniqueidentifier data type for primary keys. I read from an older post that there is not a uniqueidentifier equivalent in Oracle. Is this still true? If so, is there any indication this data type may be available in a future version?
I am assuming at this point that I will need to use the char or varchar2 data type to represent this column since I have to synchronize data from the SQL Server system to the Oracle system. Besides the extremely large indexes this will create, do you see any problems with doing this?
If you have any other approaches to this problem, I would love to hear from you.
Thanks!
Ztrain
I may need to migrate an existing SQL Server database to Oracle. The database makes heavy use of the uniqueidentifier data type for primary keys. I read from an older post that there is not a uniqueidentifier equivalent in Oracle. Is this still true? If so, is there any indication this data type may be available in a future version?
I am assuming at this point that I will need to use the char or varchar2 data type to represent this column since I have to synchronize data from the SQL Server system to the Oracle system. Besides the extremely large indexes this will create, do you see any problems with doing this?
If you have any other approaches to this problem, I would love to hear from you.
Thanks!
Ztrain
ASKER
Danths, thanks for responding, unfortunately, that's not what I'm looking for. In SQL Server the uniqueidentifier data type stores values as GUIDs (globally unique identifiers). They are displayed in the following format: XXXXXXXX-XXXX-XXXX-XXXX-XX XXXXXXXXXX
GUIDs are easy to generate - that's not the issue. The problem is how to represent this in an Oracle database as efficiently as possible. I have to synchronize data in the SQL Server database with the Oracle database. All the other data types I'm using match up pretty well.
GUIDs are easy to generate - that's not the issue. The problem is how to represent this in an Oracle database as efficiently as possible. I have to synchronize data in the SQL Server database with the Oracle database. All the other data types I'm using match up pretty well.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, I haven't. Please provide more information. Is there a recommended way of storing a GUID in Oracle?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't know much about the RAW data type. Can it be used as a primary key? Any limitations or performance issues with using it as a foreign key to other tables with referential integrity setup? How do you query for a GUID value when it's in a RAW column?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Whoops, one more thing. The specified_value needs to be in single quotes.
-MVZ
-MVZ
ASKER
Thanks to everyone who posted responses. It may not be the exact answer to the question, but I think it's leading in a direction that may turn out to work.
http://www.techonthenet.com/oracle/sequences.htm