469,610 Members | 2,101 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

ALTER TABLE requires extra parentheses for Oracle?

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
Jul 19 '05 #1
2 9481

"Dylan Nicholson" <wi******@hotmail.com> wrote in message
news:7d**************************@posting.google.c om...
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
Jul 19 '05 #2
wi******@hotmail.com (Dylan Nicholson) wrote in message news:<7d**************************@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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Maximilian Scherf | last post: by
1 post views Thread by FRED | last post: by
117 posts views Thread by phil-news-nospam | last post: by
6 posts views Thread by Barry | last post: by
2 posts views Thread by Dylan Nicholson | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.