Altering column data type

Question

How can I alter a column of number to a list of numbers.

In myTable, m_no is an integer. Now I wish to change m_no column to a list of integers without loosing the currently stored values.

CREATE TABLE myTable (
    m_id,
    m_no
);

Answer 1

Don't try to store a list of numbers in a column (especially not as comma-separated values).

What you should do is create another table:

CREATE TABLE myTable (
    m_id NUMBER PRIMARY KEY
);

CREATE TABLE myTable_numbers (
    m_id REFERENCES mytable(m_id),
    m_no NUMBER(10,0)
);

Then, when you want to get the list of numbers for an id you can use a JOIN:

SELECT mt.m_id,
       mtn.m_no
FROM   mytable mt
       LEFT OUTER JOIN mytable_numbers mtn
       ON (mt.m_id = mtn.m_id)

Which, for the sample data:

INSERT ALL
  INTO myTable (m_id) VALUES (1)
  INTO myTable (m_id) VALUES (2)
  INTO myTable (m_id) VALUES (3)
  INTO myTable_numbers (m_id, m_no) VALUES (1, 1)
  INTO myTable_numbers (m_id, m_no) VALUES (1, 2)
  INTO myTable_numbers (m_id, m_no) VALUES (1, 3)
  INTO myTable_numbers (m_id, m_no) VALUES (3, 2)
  INTO myTable_numbers (m_id, m_no) VALUES (3, 4)
SELECT 1 FROM DUAL;

Outputs:

M_ID	M_NO
1	1
1	2
1	3
3	2
3	4
2	null

Or, if you want a comma-separated list of numbers (for display purposes) then JOIN and aggregate:

SELECT mt.m_id,
       LISTAGG(mtn.m_no, ',') WITHIN GROUP (ORDER BY mtn.m_no) AS m_nos
FROM   mytable mt
       LEFT OUTER JOIN mytable_numbers mtn
       ON (mt.m_id = mtn.m_id)
GROUP BY mt.m_id;

Which outputs:

M_ID	M_NOS
1	1,2,3
2	null
3	2,4

---

If you want to modify the existing table to use two tables then:

1. Create the myTable_numbers table using the DDL statement above (without the referential constraint).
2. Copy the data using INSERT INTO mytable_numbers (m_id, m_no) SELECT m_id, m_no FROM my_table;
3. Drop the m_no column from mytable.
4. Delete the duplicate m_id values from myTable.
5. Add a primary key or unique constraint on mytable.m_id.
6. Add the referential constraint on mytable_numbers.m_id to mytable.m_id.

---

If you really want a list of numbers in a column (don't) then use a nested table:

CREATE TYPE int_list IS TABLE OF NUMBER(10,0);

CREATE TABLE myTable (
  m_id  NUMBER,
  m_nos int_list
)
STORE m_nos AS mytable_numbers;

Then you can insert the same data:

INSERT ALL
  INTO mytable (m_id, m_nos) VALUES (1, int_list(1,2,3))
  INTO mytable (m_id, m_nos) VALUES (2, int_list())
  INTO mytable (m_id, m_nos) VALUES (3, int_list(2,4))
SELECT 1 FROM DUAL;

And use the queries:

SELECT mt.m_id,
       mtn.COLUMN_VALUE
FROM   mytable mt
       LEFT OUTER JOIN TABLE(mt.m_nos) mtn
       ON 1 = 1;

or

SELECT mt.m_id,
       LISTAGG(mtn.COLUMN_VALUE, ',') WITHIN GROUP (ORDER BY mtn.COLUMN_VALUE)
         AS m_nos
FROM   mytable mt
       LEFT OUTER JOIN TABLE(mt.m_nos) mtn
       ON 1 = 1
GROUP BY mt.m_id;

giving the same output as above.

fiddle

Answer 2

That's not a straightforward task as you can't change column's datatype if that column isn't empty (and yours isn't).

Moreover, it depends on what you call a "list of integers"; a simple option is if it represents a string with comma-separated integer values. Basically, a varchar2 datatype column. Or, did you mean to use a nested table?

Presuming it is a simple string, here's a walkthrough:

Original table with some values:

SQL> create table mytable (m_id number, m_no number);

Table created.

SQL> insert into mytable values (1, 100);

1 row created.

If you just try to change m_no column's datatype, it won't work:

SQL> alter table mytable modify m_no varchar2(50);
alter table mytable modify m_no varchar2(50)
                           *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Therefore, add a new column (to store "original" value):

SQL> alter table mytable add m_no_old number;

Table altered.

SQL> update mytable set m_no_old = m_no;

1 row updated.

Empty the original column:

SQL> update mytable set m_no = null;

1 row updated.

Change its datatype:

SQL> alter table mytable modify m_no varchar2(50);

Table altered.

Move original values back:

SQL> update mytable set m_no = m_no_old;

1 row updated.

Drop newly added column:

SQL> alter table mytable drop column m_no_old;

Table altered.

Add some more integers into it:

SQL> update mytable set m_no = m_no || ', 200, 300';

1 row updated.

Result:

SQL> select * from mytable;

      M_ID M_NO
---------- --------------------------------------------------
         1 100, 200, 300