Re: Automatic random number on row creation
- From: "Michel Cadot" <micadot{at}altern{dot}org>
- Date: Thu, 29 Dec 2005 19:46:17 +0100
"Kuon" <kuon@xxxxxxxxxx> a écrit dans le message de news: dp0uo3$1t3$1@xxxxxxxxxxxxxxxxxx
| Hello,
|
| I have a ticket system which need a random (act as a password) string
| generated. But this string also act as a key (need to be unique).
|
| The approach of the former dev does not suites me.
|
| He was generating a random string, doing a select key where key = ... To
| verify the uniqueness of the key.
|
| If the select returned nothing, he inserted the new row.
|
| Of course this is not good, for many reasons.
|
| I want to do it database side.
|
| I know something like that:
| select dbms_random.string('U', 20) str from dual
|
| for my random number.
|
| But how, when I do:
|
| insert into myTable (otherInfos) values ("myotherinfos"),
|
| how can the "key" column been automaticaly assigned a random and unique
| string?
|
| Then I can select this column and pass it in my app.
|
| Thanks a lot
|
| Regards
|
| Kuon
Notwithstanding Mladen answer (you can handle the duplicate key in
your application and retry the insert), you can do something like:
SQL> create table t2 (id varchar2(20), val number);
Table created.
SQL> create trigger t2_bir before insert on t2 for each row
2 begin
3 :new.id := dbms_random.string('U', 20) ;
4 end;
5 /
Trigger created.
SQL> var id varchar2(20)
SQL> insert into t2 (val) values(1) returning id into :id;
1 row created.
SQL> print id
ID
--------------------------------
ZFQACDEGCROHBYTXXXNK
SQL> select * from t2;
ID VAL
-------------------- ----------
ZFQACDEGCROHBYTXXXNK 1
1 row selected.
Regards
Michel Cadot
.
- References:
- Automatic random number on row creation
- From: Kuon
- Automatic random number on row creation
- Prev by Date: Autoincrement numeric primary key data type
- Next by Date: Re: Autoincrement numeric primary key data type
- Previous by thread: Re: Automatic random number on row creation
- Next by thread: Autoincrement numeric primary key data type
- Index(es):
Relevant Pages
|
