Solved

Uniqueidentifier equivalent in Oracle

Posted on 2004-04-26
9
4,079 Views
Last Modified: 2007-11-27
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
0
Comment
Question by:Ztrain2100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 6

Expert Comment

by:danths
ID: 10922193
0
 

Author Comment

by:Ztrain2100
ID: 10922310
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-XXXXXXXXXXXX

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.
0
 
LVL 6

Assisted Solution

by:slyckboy
slyckboy earned 100 total points
ID: 10922398
Have you looked at the Oracle built in function called SYS_GUID?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Ztrain2100
ID: 10922497
No, I haven't. Please provide more information. Is there a recommended way of storing a GUID in Oracle?
0
 
LVL 6

Accepted Solution

by:
danths earned 200 total points
ID: 10922498
SYS_GUID generates and returns a globally unique identifier (RAW value) made up
of 16 bytes. On most platforms, the generated identifier consists of a host
identifier and a process or thread identifier of the process or thread invoking
the function, and a nonrepeating value (sequence of bytes) for that process or
thread.

Examples
The following example adds a column to the sample table hr.locations, inserts
unique identifiers into each row, and returns the 32-character hexadecimal
representation of the 16-byte RAW value of the global unique identifier:

ALTER TABLE <table> ADD (guid_col RAW(32));

UPDATE <table> SET guid_col = SYS_GUID();

When RAW data in an Oracle table is converted to a character string in a program, the data is represented in hexadecimal character code. Each byte of the RAW data is returned as two characters that indicate the value of the byte, from '00' to 'FF'. If you want to input a character string in your program to a RAW column in an Oracle table, you must code the data in the character string using this hexadecimal code.

SELECT location_id, uid_col FROM locations;
0
 

Author Comment

by:Ztrain2100
ID: 10931091
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?
0
 

Assisted Solution

by:mvanzan
mvanzan earned 200 total points
ID: 10963005
I'm not extremely knowledgeable on RAW data types, but I have seen many examples and a couple time used the builtin function rawtohex() to work with raw values.  

If you could create the table with a raw datatype as the primary key, then you could do the following:

Select * from table where rawtohex(table.primary_key_column) = specified_value;

Good luck,
MVZ
0
 

Expert Comment

by:mvanzan
ID: 10963022
Whoops, one more thing.  The specified_value needs to be in single quotes.

-MVZ
0
 

Author Comment

by:Ztrain2100
ID: 10966087
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.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question