Altering Table Column: Oracle vs MS SQL

Today I needed to do some coding in MS SQL and discovered some differences between Oracle and MS SQL worth mentioning.

As you know, Oracle allows altering more than one column in a single command. For ex:

ALTER TABLE scott.emp MODIFY (
   hiredate NOT NULL,
   deptno NUMBER(4) NOT NULL
);

In TSQL, however, you will have to execute two commands:

ALTER TABLE emp ALTER COLUMN hiredate DATE NOT NULL;
ALTER TABLE emp ALTER COLUMN deptno INT NOT NULL;

A couple of more differences:

• TSQL uses ALTER COLUMN, Oracle SQL – MODIFY
• TSQL, unlike Oracle SQL, cannot alter column null-ability without knowing its data type

I hope this short post will help Oracle developers to be productive with MS SQL if the opportunity presents.

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

Further Reading:

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” for instructions.

Add multiple columns to a table. Teradata vs Oracle syntax.

Today, I am starting a new topic in my blog – Teradata tips and Tricks for Oracle developers.

It is not very hard for a seasoned Oracle professional to learn Teradata SQL. At the same time, there are syntax nuances that need to be remembered. This is the first post that should help Oracle developers to be productive with Teradata SQL.

Oracle syntax:

ALTER TABLE [table name] ADD 
(
   column1 datatype1 ...,
   column2 datatype2 ...,
   ...
   columnN datatypeN ...
)

Teradata syntax:

ALTER TABLE [table name] 
ADD column1 datatype1 ...,
ADD column2 datatype2 ...,
   ...
ADD columnN datatypeN ...

Notice, that Teradata SQL syntax does not use parenthesis and requires “ADD” for every column being added.

How to enforce email address column to accept only “valid” email values?

The easiest way to solve this problem is to add a check constraint with regular expression validation:

--Let's first add an email column to the emp table:

ALTER TABLE emp ADD email VARCHAR2(100);

Table altered.

--Now let's add constraint using REGEXP_LIKE function:

ALTER TABLE emp ADD CONSTRAINT chk_emp_email 
CHECK (REGEXP_LIKE (EMAIL,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'));

Table altered.

Now let’s see if it works:

UPDATE emp
SET email=ename || '@emp.com'
WHERE ROWNUM=1;

1 row updated.

UPDATE emp
SET email = 'abc@' --invalid email
WHERE ROWNUM=1;
UPDATE emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_EMP_EMAIL) violated

You may want to drop the new column after this exercise:

ALTER TABLE emp DROP COLUMN email;
Table altered.

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

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”.