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

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

oracle alter table modify column default value example
alter table add column oracle default value
alter table modify column oracle
oracle alter table add column
oracle alter table drop column
how to add a column in the middle of a table in oracle
modify column datatype in oracle with data
modify column size in oracle with data

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.


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

Ask TOM "Adding a column with a default value to a table", Reading through the documentation I see this "When you specify a Would alter table be better or add column, update value and add not null constraint? And - when you insert new rows, they will have the default value right there in the row itself. DEFAULT" function quite like the "IS NULL" function. Let’s add a new column named status to the accounts table with default value 1. ALTER TABLE accounts ADD status NUMBER ( 1 , 0 ) DEFAULT 1 NOT NULL ; Once you executed the statement, the values in the status column are set to 1 for all existing rows in the accounts table.


Oracle ALTER TABLE MODIFY Column Explained By Practical , This tutorial shows you how to use the Oracle ALTER TABLE MODIFY column statement to change Third, verify the insert operation by using the following SELECT statement: Note that the LOWER() function converts a string to lowercase. Let's add a new column named status to the accounts table with default value 1. First, you specify the name of the table, which you want to add the new column, after the ALTER TABLE clause. Second, you specify the column name, data type, and its constraint. Note that you cannot add a column that already exists in the table; trying to do so will cause an error.


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

Oracle ALTER TABLE ADD Column By Examples, To add a new column to a table, you use the ALTER TABLE statement as CREATE TABLE members( member_id NUMBER GENERATED BY DEFAULT AS� Adding a column with default value on a empty table will take few seconds but with 50 million rows it will take time as it has to populate each row with default value. Other option will be. 1. Add the column without any default value (should be quick) 2. Update the column with the value ( Will take some time) 3.


Oracle / PLSQL: ALTER TABLE Statement, This Oracle ALTER TABLE example will add a column called customer_name to the customers table that is a data type of varchar2(45). In a more complicated example, you could use the ALTER TABLE statement to add a new column that also has a default value: ALTER TABLE customers ADD city varchar2(40) DEFAULT 'Seattle'; (1) In very Big Table add column as NOT NULL with 'y' default value (it update in data dictionary with value 'y') (2) query the records we see default value as 'y' (3) Now again alter table change default value for the column added in step 1 'X' (This will again update the data dictionary and override previous value i.e. 'y' with 'x')


DEFAULT Values for Table Columns , AutoNumber, Identity and Using Sequences as Default Values in Oracle � Identity Oracle 12c allows you to modify this behaviour using the ON NULL clause in the adding a new column to an existing table required all rows in that table to be The website cannot function properly without these cookies, and can only be� I should point out, though, that you can use SYS_GUID() alone as the default value for a column without worrying about a trigger. SQL> create table foo ( 2 col1 varchar2(32) default sys_guid(), 3 col2 number 4 ); Table created. SQL> insert into foo( col2 ) values( 1 ); 1 row created.


Oracle Add Column to Oracle Database Tables via the Alter Table , The add column function has options for the new column name, the new column data values, and whether or not the new column has a default value associated with it. of the SQL generated by the Oracle Alter Table Add Column function:. For example: ALTER TABLE customers ADD (customer_name varchar2 (45), city varchar2 (40) DEFAULT 'Seattle'); This Oracle ALTER TABLE example will add two columns, customer_name as a varchar2 (45) field and city as a varchar2 (40) field with a default value of 'Seattle' to the customers table.