By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,559 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,559 IT Pros & Developers. It's quick & easy.

ALTER TABLE requires extra parentheses for Oracle?

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a

"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

P: n/a
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.