Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

ORACLE 10g: Function - insert unique identifier using sequence

Experts,

I have a function that truncates a table and inserts new data.  The data that is being inserted doesn't have a unique ID nor does it have a field that is unique in records.  Therefore, I want to create a unique id for each row, which has to be numbers only.  Any ideas on how to accomplish this?

Example:
Colum1 (ID)
1
2
3
4
5
etc

Thanks!
0
Maliki Hassani
Asked:
Maliki Hassani
  • 3
  • 3
2 Solutions
 
sdstuberCommented:
if your inserted data is from a select statement, you can simply use ROWNUM

for example...

select rownum, t.* from all_tables t;


to create a sequence object is easy though

create sequence my_sequence;

insert into yourtable( id) values (my_sequence.nextval);'

or

select my_sequence.nextval,t.* from all_tables t;
0
 
DavidSenior Oracle Database AdministratorCommented:
Create a sequence, initialize it, and use the built-in function to increment it.  INSERT into table VALUES (somesequence.nextval)  is the simplest, and doesn't require any trigger.
0
 
sdstuberCommented:
also, if this is 12c,  you can associate a sequence with a column to create an "identity column" like you see in other databases.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Maliki HassaniAuthor Commented:
Great I will give this a try!
0
 
Maliki HassaniAuthor Commented:
I used rownum and that that worked.  Do I need to have a sequence added and applied or just use rownum?
0
 
Maliki HassaniAuthor Commented:
Let me rephrase that..  as for speed is it better to use sequence or row number?  Thanks
0
 
sdstuberCommented:
the difference will be very minor, but rownum should probably be minorly more efficient since it never has to update the data dictionary but a sequence with a large cache value will minimize that.

Best way to verify is to simply run both on your system and test results.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now