Search code examples
sqloracle-databasealter-table

Oracle SQL- Invalid Alter Table Option

I have created the table below, The column with Payment status uses numbers (1&0) to display status of payments 1 if received and 0 if not. I filled up some of the lines with data. However I decided to use (Paid, Not Paid) instead of (1, 0). I tried to perform Alter table and I got this error : ORA-01735: invalid ALTER TABLE option 01735. 00000 - "invalid ALTER TABLE option". I know this happens because the datatype in the table is set to numbers and already filled with numbers, but what is the easiest way to resolve this issue ?

Thank you

Name                    Null?    Type         
----------------------- -------- ------------ 
OWNER_NAME                       VARCHAR2(20) 
APARTMENT_NUMBER                 VARCHAR2(4)  
TELEPHONE_NUMBER                 VARCHAR2(15) 
MONTH_YEAR                       VARCHAR2(6)  
FEE                              NUMBER       
PAYMENT_STATUS          NOT NULL NUMBER(1)    
MONEY_HANDED_TO_COMMITY          VARCHAR2(3)
Solution

  • There's no "easy" way.

    • Create a new column
    • update table and set new column's value (depending on those 0/1)
    • drop the old column
    • rename the new column to old name