如何将dataype CLOB更改为VARCHAR2(sql) [英] How to change a dataype CLOB TO VARCHAR2(sql)

问题描述

表:客户

ID      NAME             DATATYPE
NUMBER  VARCHAR2(100)    CLOB

我想将DATA列从CLOB更改为`VARCHAR2(1000)

I want to change the DATA column from CLOB to `VARCHAR2(1000)

我也尝试过ALTER TABLE customers MODIFY DATA VARCHAR2 (1000)

ALTER TABLE customers MODIFY (DATA VARCHAR2 (1000))

还

alter table customers  modify
(data VARCHAR2(4000))

如果数据类型不是Clob，但我得到ORA-22859的话通常可以使用，因为我使用的是oracle toad/apex.

those normally works if the datatype is not a clob but I am getting a ORA-22859 because I am using oracle toad/apex.

推荐答案

您可以尝试以下方法:

1. 添加新列作为varchar2

1. Add a new column as varchar2

alter table my_table add (new_column varchar2(1000));

将CLOB名称更新为varchar2列;

UPDATE CLOB name to varchar2 column;

update my_table set new_column=dbms_lob.substr(old_column,1000,1);

测试数据后:

1. DROP CLOB列

1. DROP CLOB column

alter table my_table drop column old_column

将varchar2列重命名为CLOB列名称

Rename varchar2 column to CLOB column name

alter table my_table rename column new_column to old_column

这篇关于如何将dataype CLOB更改为VARCHAR2(sql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！