0

I have the following function:

CREATE FUNCTION UUID_AS_HEX(bytes IN RAW)
  RETURN CHAR DETERMINISTIC
  IS uuid CHAR(36);
  BEGIN
    RETURN REGEXP_REPLACE(bytes, '([0-9A-F]{8})?([0-9A-F]{4})?([0-9A-F]{4})?([0-9A-F]{4})?([0-9A-F]{12})', '\1-\2-\3-\4-\5');
  END;

and I want to create a virtual column:

CREATE TABLE example_table
(
  uuid               RAW(16)                                   NOT NULL PRIMARY KEY,
  v_uuid             CHAR(36) GENERATED ALWAYS AS (UUID_AS_HEX(uuid)) VIRTUAL
);

throws the following exception:

ORA-12899: value too large for column "V_UUID" (actual: 36, maximum: 4000)

I tried creating the function with RETURN CHAR(36) DETERMINISTIC but it complains about that syntax with

2:14:PLS-00103: Encountered the symbol "(" when expecting one of the following:
; is authid as cluster order using external varying character

How do I create this function where it match the CHAR(36) in the table?

0

1 Answer 1

0

After some experimenting I was able to get it to work by casting the result of the function call:

CREATE TABLE example_table
(
  uuid RAW(16)    NOT NULL PRIMARY KEY,
  v_uuid CHAR(36) GENERATED ALWAYS AS (CAST(UUID_AS_HEX(uuid) AS CHAR(36))) VIRTUAL
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.