Home » SQL & PL/SQL » SQL & PL/SQL » Auto Number or unique identifier
Auto Number or unique identifier [message #206236] Wed, 29 November 2006 04:29 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

I have a select query which will be used to write an output file
where i would need a unique identifier to be appended to the file.

Select e.Empno,e.Ename,d.dname
from emp e, dept d
where e.empno=d.dname;

While writing this output to an output, i would need append an unique numerical identifier. I know we can use "sequence" to generate the unique identifiers but jus want to know if there is any other way to accomplish this?

Thanks
Safeeq
Re: Auto Number or unique identifier [message #206238 is a reply to message #206236] Wed, 29 November 2006 04:40 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Use ROWNUM ?
Re: Auto Number or unique identifier [message #206240 is a reply to message #206236] Wed, 29 November 2006 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you fancy crippling the performance of your product in a multi user environment, you could create a table which stores the unique identifiers, and every time you need an identifier, you find the largest Id in the table, insert a new record one larger than that, and use that value.
You'll need to lock the table, to make sure that you don't try to add the same record as someone else, which will make this a bottleneck.

I'd use a sequence, really.
Re: Auto Number or unique identifier [message #206557 is a reply to message #206240] Thu, 30 November 2006 09:19 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have also used sys_guid() to get unique numbers across separate databases.

If you don't mind the numbers getting somewhat big. Wink

select sys_guid(), 
       to_number(sys_guid(),'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ) 
  from dual;

See here for more info :

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:831827028200
Previous Topic: SELECT Count question
Next Topic: PROBEL WITH RPOCEDURE
Goto Forum:
  


Current Time: Thu Dec 08 14:43:26 CST 2016

Total time taken to generate the page: 0.13535 seconds