alter table add column using %TYPE oracle

Question

Can we add a column in a table using the datatype of a column from another table. NO PLSQL. just a normal SQL query

eg as below

*Table ABC has 3 columns

name varchar2(50)

id number

dob Date

Table XYZ has 2 columns

id number

phone_number number(10)*

Now i want to add another column in Table XYZ With same datatype as NAME of column ABC.

but the below command is not working

alter table XYZ ADD MOTHERS_NAME ABC.NAME%TYPE;

Answer 1

Sorry that facility does not exist.

You could query USER_TAB_COLS to determine the data type of the source column and dynamically build the ALTER TABLE command, but that still does not do anything like keep them in sync. It would just be a syntactical convenience.