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

You cannot use PL/SQL functions in the default expression. But it can be a SQL function.Here's an extract from the 19c Doc:

Default column values are subject to the following restrictions:

• A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

And here's an example using a sql function:

    SQL> create table tc (c1 number default sqrt(2));

    Table TC created.

Answer 2

The default value has to be an actual value, not a function.