I am using Oracle 11g for my web application. I want to add a column and a comment to an existing table. I can do that easily with the below commands
ALTER TABLE product ADD product_description VARCHAR2(20)
and
COMMENT ON COLUMN product.product_description
IS 'This is comment for the column';
But I want to do above task in single command. I searched on internet for a command to add a column and comment in a single command but I couldn't find. I wonder if this is possible. Any suggestions would be highly appreciated.
No, you can't.
There's no reason why you would need to. This is a one-time operation and so takes only an additional second or two to actually type and execute.
If you're adding columns in your web application this is more indicative of a flaw in your data-model as you shouldn't need to be doing it.
In response to your comment that a comment is a column attribute; it may seem so but behind the scenes Oracle stores this as an attribute of an object.
SQL> desc sys.com$
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
COL# NUMBER
COMMENT$ VARCHAR2(4000)
SQL>
The column is optional and sys.col$ does not contain comment information.
I assume, I have no knowledge, that this was done in order to only have one system of dealing with comments rather than multiple.
You can use below query to update or create comment on already created table.
SYNTAX:
COMMENT ON COLUMN TableName.ColumnName IS 'comment text';
Example:
COMMENT ON COLUMN TAB_SAMBANGI.MY_COLUMN IS 'This is a comment on my column...';
Add comments for two different columns of the EMPLOYEE table :
COMMENT ON EMPLOYEE
(WORKDEPT IS 'see DEPARTMENT table for names',
EDLEVEL IS 'highest grade level passed in school' )
Query to add column with comment are :
alter table table_name
add( "NISFLAG" NUMBER(1,0) )
comment on column "ELIXIR"."PRD_INFO_1"."NISPRODGSTAPPL" is 'comment here'
commit;
Source: Stackoverflow.com