How can I alter a table in Oracle while adding a column, which is of the same type as a column from a different table

Question

I have two tables : table1 and table2.

There is a customerId field in table2 of data type Varchar2(30), how can I alter table1 to add the customerId field of the same data type as table2 using %type.

I tried the below code but no luck.

alter table table2
add  customer_id table1.CUSTOMER_ID%type;

is it possible to alter using %type? Will this work. Please advise.

If it does not work, shall I do it manually by stating

alter table table2
add  customer_id varchar2(30);

Oracle SQL does not support "copying" the data type (while PL/SQL does, with the `%type` operator). Why do you want/need to copy it that way - what is wrong with doing it "manually" as you show in your question? — , Feb 26 '18 at 06:06
@mathguy, if the data type of customerId changes, we have to manually change everywhere, instead if it is a copy, we just need to change at one place, which i thought is more of a good practice. — Jayanth, Feb 26 '18 at 06:08
Even _if_ `%type` worked, I am pretty sure that wouldn't help either because the data type of a column won't change once it's created. — , Feb 26 '18 at 06:59
@a_horse_with_no_name, I am not sure about that..i had a hunch that it would be dynamic. Anyways, do you have any url that justifies your statement. It would be of great help if you can share. — Jayanth, Feb 26 '18 at 08:02
@a_horse_with_no_name, I meant, your last statement (i.e. the data type of a column won't change once it's created). I am just looking for a material that justifies this, thats all. Thanks in Advance. — Jayanth, Feb 26 '18 at 08:11
This doesn't address your case, but it may interest future visitors. There are some database products that support CREATE DOMAN and ALTER DOMAIN. By basing all the columns that must conform to each other on a single domain you simplify things when data definitions must change. This can happen even when you analyze carefully before designing. — Walter Mitty, Feb 28 '18 at 04:08
Hi @WalterMitty, can you specify what are those database domains are...just for an idea — Jayanth, Feb 28 '18 at 04:45
Postgres supports CREATE DOMAIN. You can look up online documentation to see how it works. Conceptually, an SQL DOMAIN is very much like a user defined datatype, except that it is DDL, the definition is stored in the database metadata, and column definitions can refer to it. If I find some good sample uses online, I'll post another comment. — Walter Mitty, Feb 28 '18 at 14:01

score 0 · Answer 1 · answered Feb 26 '18 at 06:57

This is not an answer (mathguy told it to you already), but a comment is a little bit "short" for what I'd like to say.

While attending HrOUG conference, I saw a man wearing a T-shirt saying

Thank you for spending months in coding & saving us days in planing

In other words, carefully choose CUSTOMERID data type. If you are selling products to 13 customers today, don't set it to NUMBER(2) because (if your company develops and becomes prosperous), you'll soon be selling products to thousands of customers. Will you first alter it (and its all dependant column data types, as well as all its appearances in your application(s)) to NUMBER(3), and then to NUMBER(4), etc.? Think about the future!

Similarly, at the same conference, there were guys who said that they have tables with 570 columns. Gosh! 5-7-0! What are they doing with such a tables? Their answer was: "We pay Oracle a lot of money. It allows us to create tables with 1000 columns, and we are going to use every single one of them." The audience was kind of puzzled (hint: normalization?), but hey - it's their choice.

Yes, I noticed that you chose a VARCHAR2 data type for that ID column. (I'm not saying that it is wrong, but I, somehow, prefer numbers over strings for such purposes.) So, what do you think? Will 30 characters be enough? How much would it cost if you set it to 50 characters? Or 100? They won't take any additional space on a disk. If there is 'A234' in your VARCHAR2(100 BYTE) column, it'll take only 4 bytes on a disk. Memory is a different story, as Oracle will pre-allocate space when you use such a variable in your PL/SQL code, so you might end up in wasting space unnecessarily. Adding more RAM? Sure, it is an option, but it costs money.

Therefore, once again - design your data model carefully and you should be OK, following the supported ALTER TABLE syntax.

thanks for the insight. We dont have the customers growing on a large scale. Hardly, 2-5 customers / 10 years — Jayanth, Feb 26 '18 at 08:04

APC · Accepted Answer · 2018-02-26T16:01:13.100

alter table table2 add customer_id table1.CUSTOMER_ID%type;

%type is a PL/SQL construct. We use it to define local variables in a program which are based on table columns. It does not work in SQL.

"if the data type of customerId changes, we have to manually change everywhere, instead if it is a copy, we just need to change at one place, "

This is not how Oracle (and most other if not all) databases work. They are engines for storing and retrieving data. They make this easy by enforcing strong data-typing and by making it hard to lose data carelessly. The rigour of the data dictionary is there to protect us from our lazy selves.

As a thought experiment, consider the impact on table2.customer_id if we did any one of the following:

alter table table1 modify customer_id not null;
alter table table1 modify customer_id number(6,0); -- from number(9,0)
alter table table1 modify customer_id number(6,0); -- from varchar2(6)
alter table table1 drop column customer_id;

All of these are possible real-life cases. For any of them ,the state of data in table2.customer_id could cause the statement to fail on table2 (even though they would succeed on table1). Is that desirable? Almost certainly yes. But it now means we cannot change table1, which greatly reduces the utility of having a template column.

"i thought is more of a good practice."

The best practice is to get it right first time. Obviously that's not always possible, because circumstances evolve over time. We need to accept there will be change, and the good practice for handling change is to run an impact assessment: if we change table1.customer_id what else might be affected? What else will need to change after that? What about all the program code which uses these columns?

Data management is hard, but it's hard for a good reason. Unlike source code, databases have state. Changing state is expensive, and reverting to a previous state even more so. Changing the datatype of a column means changing the state of all the data in that column. This is not something which should be done lightly.

So. Do proper analysis. Have a decent data model. Understand your data structures. These are good practices.

score 0 · Answer 3 · answered Feb 26 '18 at 07:13

Note : Use this with caution.

Use this only after you have read all other answers and still think you want it that way.

To use a DDL Trigger. The below is just a sample which considers customer_id as NUMBER type.For VARCHAR2, DATE etc, you need a generic way to construct the DDL. Refer Issue in dynamic table creation

CREATE OR REPLACE TRIGGER trg_alter_table1
   AFTER ALTER
   ON SCHEMA
   WHEN (ORA_DICT_OBJ_TYPE = 'TABLE' AND ORA_DICT_OBJ_NAME = 'TABLE1')
DECLARE
   v_ddl   VARCHAR2 (200);
BEGIN
   SELECT    'ALTER TABLE TABLE2 MODIFY '
          || column_name
          || ' '
          || data_type
          || '('
          || data_precision
          || ')'
     INTO v_ddl
     FROM user_tab_columns
    WHERE table_name = 'TABLE1' AND column_name = 'CUSTOMER_ID';


   EXECUTE IMMEDIATE v_ddl;
END;
/

Hi Kaushik, I am not looking for this way. I just needed to know whether i can use %type in ALTER statement. Thanks for the effort. — Jayanth, Feb 26 '18 at 08:12

How can I alter a table in Oracle while adding a column, which is of the same type as a column from a different table

3 Answers3