In an attempt to generate random unique values, which can be exposed (ie customer_id) and used as a PRIMARY KEY I am doing the following:
Generate random number, grabbing the seconds and fractions of a second from the current timestamp, and also I append to that a fixed width sequence to guarantee the uniqueness. This is all working fine. See below as I am generating some customer information.
My question is how can I make the seed column virtual as I really don't need to have it stored but I would like to see the value if needed.
Thanks in advance to all who answer and apologies for the verbose test CASE
CREATE OR REPLACE PACKAGE mf_names IS
FUNCTION random_first_name(
gender IN VARCHAR2 DEFAULT NULL,
percentage_mf IN NUMBER DEFAULT 50
) RETURN VARCHAR2;
FUNCTION random_last_name RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY mf_names IS
first_names_male SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
'Tom', 'Andy', 'Paul', 'Peter', 'Keith', 'Mark', 'Solomon', 'Joseph', 'John', 'Roger', 'Douglas','Harry', 'Barry', 'Larry', 'Gary', 'Jeffrey', 'David', 'Stuart', 'Karl', 'Seth', 'David', 'Brian', 'Sidney', 'James', 'Shane', 'Zachary', 'Anthony'
);
first_names_female SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
'Alice', 'Anna', 'Lee', 'Barbara', 'Carol', 'Debra', 'Madison', 'Faith', 'Cheryl', 'Beth', 'Kathy', 'Abigail', 'Jill', 'Grayce', 'Lynn', 'Roz', 'Carolyn', 'Deena', 'Laura', 'Sophia', 'Elise'
);
last_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
'Cooper', 'Dimeo', 'Caygle', 'Luppo', 'Coralnick', 'Torchiano', 'Fazio', 'Behrens', 'Zaza', 'Lebowitz', 'Stern', 'Malden', 'Kramer', 'Stein', 'Tessio', 'Weinreb', 'Dillon', 'Zanona', 'Rucker', 'Zanzone', 'Santoro', 'Barese', 'Silverberg', 'Aarron', 'Kern', 'Saladino', 'Rice', 'Sanford', 'Orr', 'Roth'
);
FUNCTION random_first_name(
gender IN VARCHAR2 DEFAULT NULL,
percentage_mf IN NUMBER DEFAULT 50
) RETURN VARCHAR2
IS
BEGIN
IF UPPER(gender) LIKE 'M%' THEN
RETURN first_names_male(FLOOR(DBMS_RANDOM.VALUE(1, first_names_male.COUNT 1)));
ELSIF UPPER(gender) LIKE 'F%' THEN
RETURN first_names_female(FLOOR(DBMS_RANDOM.VALUE(1, first_names_female.COUNT 1)));
ELSIF DBMS_RANDOM.VALUE(0, 100) < percentage_mf THEN
RETURN random_first_name('M');
ELSE
RETURN random_first_name('F');
END IF;
END;
FUNCTION random_last_name RETURN VARCHAR2
IS
BEGIN
RETURN last_names(FLOOR(DBMS_RANDOM.VALUE(1, last_names.COUNT 1)));
END;
END;
/
CREATE TABLE CUSTOMERS (
customer_id VARCHAR2 (20),
seed NUMBER,
first_name VARCHAR2 (20),
last_name VARCHAR2 (20),
constraint customer_id_pk primary key (customer_id));
create sequence customer_seq start with 1000000 minvalue 1000000 maxvalue 9999999 cycle;
create or replace function base34(p_num number) return varchar2 is
l_dig varchar2(34) := 'AB0CD1EF2GH3JK4LM5NP6QR7ST8UV9WXYZ';
l_num number := p_num;
l_str varchar2(38);
begin
loop
l_str := substr(l_dig,mod(l_num,34) 1,1) || l_str ;
l_num := trunc(l_num/34);
exit when l_num = 0;
end loop;
return l_str;
end;
/
create or replace function dec34(p_str varchar2) return number is
l_dig varchar2(34) := 'AB0CD1EF2GH3JK4LM5NP6QR7ST8UV9WXYZ';
l_num number := 0;
begin
for i in 1 .. length(p_str) loop
l_num := l_num * 34 instr(l_dig,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end;
/
create or replace trigger customer_trg
before update on customers for each row
begin
if ( updating('customer_id') )
then
raise_application_error(-20000,'Cant Update customer_id');
end if;
if ( updating('seed') )
then
raise_application_error(-20001,'Cant Update seed');
end if;
end;
/
DECLARE
seed NUMBER;
begin
for i in 1 .. 100 loop
seed := (to_number(trunc(dbms_random.value(1000,9999))|| to_char(systimestamp,'FFSS')||customer_seq.nextval));
INSERT into customers(
customer_id,
seed,
first_name,
last_name
) VALUES (
base34(seed),
seed,
mf_names.random_first_name(),
mf_names.random_last_name()
);
end loop;
end;
/
select to_char(seed) from customers where rownum = 1
union all
select base34(seed) from customers where rownum = 1
union all
select to_char(dec34(base34(seed))) from customers where rownum = 1;
TO_CHAR(SEED)
5444355405000301000000
BZCK24C0D3CHYE6
5444355405000301000000
select base34(seed),
dump(base34(seed)) from customers where rownum = 1
BASE34(SEED) DUMP(BASE34(SEED))
BZCK24C0D3CHYE6 Typ=1 Len=15: 66,90,67,75,50,52,67,48,68,51,67,72,89,69,54
CodePudding user response：
I'm afraid you can't do that because dbms_random.value and systimestamp functions you'd want to use aren't deterministic (they should return the same value whenever called, while these two don't).
For example: this works because 1 2 is always 3:
SQL> create table test
2 (id number generated always as identity,
3 seed number generated always as (1 2)
4 );
Table created.
SQL> drop table test;
Table dropped.
Can you use dbms_random.value? Nope:
SQL> create table test
2 (id number generated always as identity,
3 seed number generated always as (to_number(trunc(dbms_random.value(1000,9999))))
4 );
seed number generated always as (to_number(trunc(dbms_random.value(1000,9999))))
*
ERROR at line 3:
ORA-30553: The function is not deterministic
If the function is deterministic, mark it DETERMINISTIC. If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. The values returned by a deterministic function should not change even when the function is rewritten or recompiled.
Can you use sysdate (or systimestamp, no difference)? No:
SQL> create table test
2 (id number generated always as identity,
3 seed number generated always as (to_number(to_char(sysdate, 'yyyymmdd')))
4 );
seed number generated always as (to_number(to_char(sysdate, 'yyyymmdd')))
*
ERROR at line 3:
ORA-54002: only pure functions can be specified in a virtual column expression
Column expression contained a function whose evaluation is non-deterministic
Therefore, that won't work.