Re: ALTER TABLE requires extra parentheses for Oracle?

wizofaus_at_hotmail.com (Dylan Nicholson) wrote in message news:<7d428a77.0405051726.10d5e1b2_at_posting.google.com>...
> Seems that Oracle 9.2 (using MS ODBC driver) requires extra
> parentheses when adding multiple columns to a table:
>
> ALTER TABLE MyTable ADD (MyColumn1 VARCHAR(255), MyColumn2
> VARCHAR(255))
>
> vs
>
> ALTER TABLE MyTable ADD MyColumn1 VARCHAR(255), MyColumn2 VARCHAR(255)
>
> The former simply doesn't work with MS Access or SQL server. I'm
> wondering what the SQL standard is, and whether there is a way (other
> than adding columns one at a time, which is surely inefficient for a
> table with existing data) of writing the statement to work with all 3
> types of database.

Dylan,

According to the current SQL standard, SQL-2003, you may only add one column per ALTER TABLE statement:

<alter table statement>::=ALTER TABLE <table name> <alter table action>

<alter table action>::=

  <add column definition>
 |<alter column definition>
 |<drop column definition>
 |<add table constraint definition>
 |<drop table constraint definition>

<add column definition>::=ADD [ COLUMN ] <column definition>

The SQL-99 and SQL-92 standards specified ALTER TABLE ADD COLUMN the same way as above.

The SQL-89 standard allowed a form of ALTER TABLE that specified several columns in parentheses. Just like your first example.

To verify SQL standard compliance, you can use the SQL Validator: http://developer.mimer.com/validator/

HTH,
Jarl Received on Thu May 06 2004 - 09:47:32 CEST