Alter table and add new column with default value via a function in Oracle

Question

I am trying to update a table in Oracle. The table is created using following DDL:

CREATE TABLE TEST (
    ID_NUM INTEGER,
    NAME INTEGER,
    VALUE INTEGER,
    ITEMS_NUM INTEGER,
)

And there were some data injected into this table. Now, I need to update the table to change the ID_NUM column as VARCHAR and add formatted UUID as default value.

I have followed the queries given below:

CREATE OR REPLACE FUNCTION RANDOM_UUID RETURN VARCHAR IS
  V_UUID VARCHAR(255);
BEGIN
  SELECT REGEXP_REPLACE(RAWTOHEX(SYS_GUID()), '([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') INTO V_UUID FROM DUAL;
  RETURN V_UUID;
END RANDOM_UUID;

ALTER TABLE TEST 
    DROP COLUMN ID_NUM;

ALTER TABLE TEST 
    ADD ID_NUM VARCHAR(255) DEFAULT random_uuid()  NOT NULL;

It gives an error as SQL Error [4044] [42000]: ORA-04044: procedure, function, package, or type is not allowed here I have executed and validated the function using following command and it gives a valid formatted UUID.

SELECT RANDOM_UUID() FROM DUAL;

What could be the issue in the ALTER table statement. Can't we use a function for setting default value in Oracle?

Thanks in advance.

Answer 1

I think you can achieve it using the default clause on the column but without function (just replace the function call with the content of the function in default clause) as following. (Please note that the User functions are not allowed in the default clause)

ALTER TABLE TEST 
    ADD ID_NUM VARCHAR(255)
    DEFAULT REGEXP_REPLACE(RAWTOHEX(SYS_GUID()), '([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')
    NOT NULL;

I have prepared the db<>fiddle demo to show you the error with function and success without function.

Cheers!!