?
Solved

Changing data type on column with data already in it.

Posted on 2014-02-17
2
Medium Priority
?
359 Views
Last Modified: 2014-03-24
Experts,

I have a table with a column for order quantities. Right now the column is varchar2. I need to change it to number.

Is it possible to do this without me having to empty out the table?

All of the rows have numbers in it.

Thanks,
0
Comment
Question by:FutureDBA-
2 Comments
 
LVL 23

Accepted Solution

by:
David earned 2000 total points
ID: 39866422
Assuming no indexes/constraints:

ALTER TABLE table-Name
{
    ADD COLUMN column-definition |
    ADD CONSTRAINT clause |
    DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]
    DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE
       constraint-name | CHECK constraint-name | CONSTRAINT constraint-name }
    ALTER [ COLUMN ] column-alteration |
    LOCKSIZE { ROW | TABLE }
}
column-definition
Simple-column-Name [ DataType ]
[ Column-level-constraint ]*
[ [ WITH ] DEFAULT DefaultConstantExpression
  | generation-clause
]

column-alteration
column-Name SET DATA TYPE VARCHAR(integer) |
column-Name SET DATA TYPE VARCHAR FOR BIT DATA(integer) |
column-name SET INCREMENT BY integer-constant |
column-name RESTART WITH integer-constant |
column-name [ NOT ] NULL |
column-name [ WITH | SET ] DEFAULT default-value |
column-name DROP DEFAULT

source: http://docs.oracle.com/javadb/10.6.1.0/ref/rrefsqlj81859.html
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39867356
>>Is it possible to do this without me having to empty out the table?

The answer to your question is: yes.

The post above provides the syntax.

The alter table command will fail if there is data that Oracle cannot convert.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question