SQL & PL/SQL

Hello everyone,

Oracle version 12.1.0.2

Here's the context:

1. I have a function which generates a UUID 128 bytes , 36 char of length.
2. I need to insert it automatically when a new row is created.
3. Doing it with a trigger is out of the question, the performance (of course) is terrible.
4. I have tried different ways to CAST it in a generated as always column definition

     Problems I encountered:

1.   If I cast my function in a generated as always column, eveytime I query the table, a new UUID appears in the column (which is understandable)

       Question:

          How do I set it permanently ?

Thanks a lot, Pierre

   Here's the code to reproduce the issue:

THE FUNCTION:

CREATE OR REPLACE FUNCTION pierre_uuid RETURN varchar2 deterministic is

  uuid_raw raw(16);

  uuid     varchar2(36);

  xvar        varchar2(1);

begin

  uuid_raw := sys.dbms_crypto.randombytes(16);

  uuid_raw := utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(uuid_raw, 7, 1), '0F'), '40'), uuid_raw, 7);

  xvar := case round(dbms_random.value(1, 4))

            when 1 then

             '8'

            when 2 then

             '9'

            when 3 then

             'a'

            when 4 then

             'b'

           end;

  uuid_raw := utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(uuid_raw, 9, 1), '0F'), xvar || '0'), uuid_raw, 9);

  uuid     := regexp_replace(lower(uuid_raw), '([a-f0-9]{8})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{12})', '\1-\2-\3-\4-\5');

  return uuid;

end pierre_uuid;

/

THE TABLE

CREATE TABLE UUID_TABLE

(

  uuid CHAR(36) GENERATED ALWAYS AS (CAST(pierre_uuid() AS CHAR(36))),

  name varchar2(10)

);

REPRODUCTION OF ISSUE

insert into UUID_TABLE(name) values ('HELLO');

commit;

select * from uuid_table;

select * from uuid_table;