473,395 Members | 1,680 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

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 9791

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Maximilian Scherf | last post by:
Hello, I have two problems with the ALTER TABLE command (Warning: I'm not exactly an Oracle expert): First Problem: I want to change the name of a column. I've tried the following: ALTER...
1
by: FRED | last post by:
hi all, i have a table that has a column date varchar(20) and now i learned some more about mysql I want to make it date date but if I do an alter table on it what will it do to my existing data...
3
by: Prince Kumar | last post by:
Is there any way I can define an Unique constraint or unique index which allows more than one null values for the same column combination in DB2? ie, If my index is defined on (col3, col4) where...
4
by: maricel | last post by:
Could someone confirm which tablespace is being used when running ALTER & CREATE INDEX. Is it the tempspace or the tablespace where the table resides? Many thanks, maricel
4
by: Jeff Kish | last post by:
Hi. I have a database I need to supply something (I'm assuming a t-sql script.. maybe something else is better) to update customer tables with. The operations include mostly changing varchar...
117
by: phil-news-nospam | last post by:
Is there really any advantage to using DIV elements with float style properies, vs. the old method of TABLE and TR and TD? I'm finding that by using DIV, it still involves the same number of...
1
by: vasilip | last post by:
I'm testing out db2 for a project I'm starting that requires proper xml support and I can't seem to get both xml and spatial data to work well in the same table. Once having created a table...
6
by: Barry | last post by:
In sqlserver 2000 I have a UDF which works fine but I want to make a change to it. When I do an ALTER FUNCTION ... I get an error saying that I can't alter the function because it is referenced by...
2
by: Dylan Nicholson | last post by:
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 ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.