A tricky Oracle DDL question from a real job interview

Recently I was asked the following question during the phone interview with one of the large companies:

Consider the following table:
[CREATE] TABLE table1
(
column1 NUMBER PRIMARY KEY,
column2 NUMBER,
column3 VARCHAR2(50),
column4 NUMBER
)

Change the data type of column3 from VARCHAR2(50) to CLOB while keeping the order of columns intact.

The anticipated answer was:

-- Step 1: Create a backup table with PK and last 2 columns
CREATE TABLE table1_bak AS
SELECT column1, column3, column4
FROM table1;

--Step 2: Drop last 2 columns
ALTER TABLE table1 DROP (column3, column4);

--Step 3: Add last 2 columns back:
ALTER TABLE table1 ADD(column3 CLOB, column4 NUMBER);

--Step 4: Restore the data
MERGE INTO table1 a
USING table1_bak b
ON (a.column1=b.column1)
WHEN MATCHED THEN
    UPDATE SET a.column3=b.column3,
               a.column4=b.column4;

--Step 5: Drop backup table
DROP TABLE table1_bak;

This answer was provided. In the spirit of finding workarounds I added another solution:

--Step 1: Add temp column
ALTER TABLE table1 ADD (column5 VARCHAR2(50));

--Step 2: Update column3 with NULLs and column5 with values of column3
UPDATE table1
SET column5=column3,
    column3=NULL;

--Step 3: Change column3 data type to LONG first and CLOB after that:
ALTER TABLE table1 MODIFY (column3 LONG);
ALTER TABLE table1 MODIFY (column3 CLOB);

--Note: trying to change empty column VARCHAR2 to CLOB straight throws the following error:

ALTER TABLE table1 MODIFY (column3 CLOB)
                           *
ERROR at line 1:
ORA-22858: invalid alteration of datatype

--Step 4: Restore the content of column3 from column5:
UPDATE table1
SET column3=column5;

--Step 5: Drop the temp column:
ALTER TABLE table1 DROP COLUMN column5;

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

Advertisements

2 thoughts on “A tricky Oracle DDL question from a real job interview

  1. danstober October 14, 2015 / 10:38 pm

    Why mess with trying to change column 3 to CLOB at all? Why not just add two new columns, column5 (CLOB) and column6 (NUMBER), then set column5=column3 and column6 = colum4?
    After that, you drop columns 3 and 4, and then rename columns 5 and 6 to be 3 and 4.
    I would probably finish this with ALTER TABLE table1 MOVE to try and clean up the wasted space that would be left after dropping those two columns.

    • Zahar Hilkevich October 16, 2015 / 7:59 am

      For a huge table updating 2 columns one of which is CLOB could take longer than updating just the CLOB column as CLOBs are stored separately.
      On a separate note, what if column4 is a unique Key or even a primary key, then referential integrity may not allow you to drop it at all if you have foreign keys referencing column4.

      The approach you suggested is valid overall, it is just a variation of the 1st approach I presented.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s