If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > SQL Error: ORA-01735: invalid ALTER TABLE option

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 02-09-12, 05:49
Shikhashah Shikhashah is offline
Registered User
 
Join Date: Jan 2012
Posts: 9
Question SQL Error: ORA-01735: invalid ALTER TABLE option

I am trying to alter the column name-

Quote:
Alter table Employee change ADDRESS address Varchar 100
Quote:
I am getting following error-
SQL Error: ORA-01735: invalid ALTER TABLE option
What should be the cause of error??????
Reply With Quote
  #2 (permalink)  
Old 02-09-12, 05:58
flyboy flyboy is offline
Registered User
 
Join Date: Mar 2007
Posts: 546
Quote:
Originally Posted by Shikhashah View Post
What should be the cause of error??????
It is stated in the Oracle response above: CHANGE is not valid option of ALTER TABLE statement.
Maybe, before using any SQL statement, you should familiarize with its proper syntax instead of inventing your own.
ALTER TABLE statement is described in SQL Language Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
Just pick the one for your Oracle version.

Last edited by flyboy; 02-09-12 at 05:59. Reason: Added last sentence
Reply With Quote
  #3 (permalink)  
Old 02-09-12, 06:44
Shikhashah Shikhashah is offline
Registered User
 
Join Date: Jan 2012
Posts: 9
SQL Error: ORA-01735: invalid ALTER TABLE option

Basically I want to change the column name to lowercase, I tried remaining the column but its not useful as it would take the duplicate columns.
How can I change the column name to lowercase?
Reply With Quote
  #4 (permalink)  
Old 02-09-12, 07:01
flyboy flyboy is offline
Registered User
 
Join Date: Mar 2007
Posts: 546
Quote:
Originally Posted by Shikhashah View Post
Basically I want to change the column name to lowercase, I tried remaining the column but its not useful as it would take the duplicate columns.
Changing name = renaming. Not sure what you mean with "duplicate".
Quote:
Originally Posted by Shikhashah View Post
How can I change the column name to lowercase?
Rename it. Oracle identifiers are case insensitive, interpreted as uppercase. When you want to use mixed case or include any special character except underscore (_), dollar sign ($) or pound sign (#), you have to put them between double quotes.
Code:
ALTER TABLE employee RENAME COLUMN address TO "address"
Note that you will have to always use double quotes when referencing that column. Why do you want to do this at all????
(yes, those four question marks are intentional, because it is really not a good idea. definitely not)

You may read more facts about Oracle identifiers (they use the term "Object Names and Qualifiers", just search for it) in the same book I suggested you in the previous post.
Reply With Quote
  #5 (permalink)  
Old 02-09-12, 08:01
shammat shammat is offline
Registered User
 
Join Date: Nov 2003
Posts: 2,408
Quote:
Originally Posted by flyboy View Post
because it is really not a good idea. definitely not
I couldn't agree more.
Reply With Quote
Reply

Tags
alter_table

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On