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

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, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.