CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Mohammed Shuaib
Mohammed Shuaib

Reputation: 23

Generating a unique number based on records

I have a situation here. I have a table which I query from my application. We don't have a unique identifier in this table.

I just need to generate unique number based on the contents of the record like ORA_HASH. I came to know that ORA_HASH returns repeatable values. So I want to know if any other option is available.

I want this because in my application we load data from the table to grid. Let's say users make some selections in grid and then sort. I want the selections to be retained. I tried using ROW_NUM as identifier. But it changes with every sort. So it'll help me a lot if I can find another way.

Thanks.

Upvotes: 1

Views: 528

Answers (2)

Radagast81
Radagast81

Reputation: 3016

Every table in oracle databases has a pseudocolumn with the name ROWID that stores a unique key for the record of the table. You should use this key for your purpose:

SELECT ROWID, d.* FROM dual d

This will give you the following Data as Result:

ROWID                DUMMY
------------------   -------
AAAACOAABAAAAWBAAA   X

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143023

You keep mentioning "data that repeats" (are those duplicates, then?) and that being the reason for not having a unique identifier in the table.

If possible (why not?), alter table and add a new column, let's call it "ID". Populate its value now for existing rows, and create a database trigger which would take care about future inserts.

Here's an example:

SQL> create table test as select ename, job, sal from emp;

Table created.

SQL> alter table test add id number;

Table altered.

SQL> create sequence seq_id;

Sequence created.

SQL> update test set id = seq_id.nextval;

12 rows updated.

SQL> create or replace trigger trg_bi_test
  2    before insert on test
  3    for each row
  4  begin
  5    :new.id := nvl(:new.id, seq_id.nextval);
  6  end;
  7  /

Trigger created.

SQL> insert into test (ename, job, sal) values ('Littlefoot', 'loser', 100);

1 row created.

SQL> select * from test order by id;

ENAME      JOB              SAL         ID
---------- --------- ---------- ----------
SMITH      CLERK            800          1
ALLEN      SALESMAN        1600          2
WARD       SALESMAN        1250          3
JONES      MANAGER         2975          4
MARTIN     SALESMAN        1250          5
BLAKE      MANAGER         2850          6
CLARK      MANAGER         2450          7
KING       PRESIDENT       5000          8
TURNER     SALESMAN        1500          9
JAMES      CLERK            950         10
FORD       ANALYST         3000         11
MILLER     CLERK           1300         12
Littlefoot loser            100         13

13 rows selected.

SQL>

Now you have your "unique identifier" which is fixed and won't change as you query data.

Upvotes: 1

Related Questions