Add a column SQL query in Oracle database

Question

I am using Oracle Database (version is 9i) and I want to add a column to a current table in oracle database.

I want to add an integer column to keep track of invalid tries per user, so default value should be 5.

When I try to execute this query in Sql*Plus it gives an error table or view doesn't exist ( I have double checked table name is correct.

ALTER TABLE CustApps_user ADD VALID_TRIES INT DEFAULT 5 NOT NULL;

When a command gives you an error, it is helpful to share it when asking for help. — Adam Hawkes, Jul 17 '12 at 18:49

score 2 · Answer 1 · answered Jul 17 '12 at 09:08

I guess the error you're getting is ORA-00942. This can mean a number of things, but basically it means the object does not exist in the current scope and context of what you're doing. So for instance it is the error thrown when we attempt to build a view on a table in another schema when we have been granted privileges through a role and not directly.

In your case it probably mean that the table is in another schema. You normally may be accessing it through a view or synonym. You can easily check this by querying the data dictionary:

select owner, object_type
from all_objects
where object_name = 'CUSTAPPS_USER'

And if that doesn't find anything, look for `upper(object_name) = 'CUSTAPPS_USER'`, in case it was built with a case-sensitive name; I'm suspicious when I see table names written in mixed case in a query. — Alex Poole, Jul 17 '12 at 10:04

score 0 · Answer 2 · edited Feb 12 '14 at 10:41

alter table
   table_name
add
   (
   column1_name column1_datatype column1_constraint,  
   column2_name column2_datatype column2_constraint,
   column3_name column3_datatype column3_constraint
   );

Here are some examples of Oracle "alter table" syntax to add data columns.

alter table
   cust_table
add
   cust_sex  varchar2(1) NOT NULL;

Here is an example of Oracle "alter table" syntax to add multiple data columns.

ALTER TABLE 
   cust_table 
ADD 
   (
      cust_sex             char(1) NOT NULL,
      cust_credit_rating   number
   );

score -1 · Answer 3 · answered Jul 17 '12 at 07:33

-1

You have to add bracket in query:

ALTER TABLE CustApps_user ADD (VALID_TRIES INT DEFAULT 5 NOT NULL);

INT is legal, but it will be converted to NUMBER, so you can also use:

ALTER TABLE CustApps_user ADD (VALID_TRIES NUMBER(38,0) DEFAULT 5 NOT NULL);

or change (decrease) NUMBER precision.

answered Jul 17 '12 at 07:33

tomi

706
1
6
21

The brackets are documented but in fact are optional. We can add a column without them. – APC Jul 17 '12 at 08:39

Add a column SQL query in Oracle database

3 Answers3