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.

Popeye · Accepted Answer · 2019-11-30 04:48:18Z

2

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!!

answered Nov 30, 2019 at 4:48

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gsalem · Accepted Answer · 2019-11-29 13:23:39Z

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.

answered Nov 29, 2019 at 13:23

gsalem

2,0281 gold badge10 silver badges8 bronze badges

Comments

simon at rcl · Accepted Answer · 2019-11-29 13:13:00Z

0

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

answered Nov 29, 2019 at 13:13

simon at rcl

7,3691 gold badge21 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related