ALTER TABLE requires extra parentheses for Oracle?

[Dylan Nicholson]

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.

TIA

Dylan

[Jim Kennedy]

Re: ALTER TABLE requires extra parentheses for Oracle?


"Dylan Nicholson" <wizofaus@hotma il.comwrote in message
news:7d428a77.0 405051726.10d5e 1b2@posting.goo gle.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.
>
TIA
>
Dylan

The driver might be "editing" your sql statements for you. Try a pass thru
query. Also usually for variable characters in Oracle you should use
varchar2 not varchar. The extra parens should be ignored in any database
since they are just a grouping mechanism.
Jim

[Jarl Hermansson]

Re: ALTER TABLE requires extra parentheses for Oracle?

wizofaus@hotmai l.com (Dylan Nicholson) wrote in message news:<7d428a77. 0405051726.10d5 e1b2@posting.go ogle.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>::=AL TER 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>::=A DD [ 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