ORA-00997的变通办法:非法使用LONG数据类型

问题描述

我想将系统表user_tab_cols中的一些数据保存到临时表中，以便可以从中进行转储.

I want to save some data from a system table user_tab_cols, to a temp table so I can take a dump from it.

其中有100,000行，我从user_tab_cols中选择了约1,000条记录，并使用以下查询将它们保存到临时表中:

There are 100,000 rows in it , I have select from user_tab_cols about 1,000 records and d save them into a temp table with this query:

create table temp table as
select * from user_tab_cols where condition...

由于列DATA_DEFAULT包含long类型，因此出现非法使用longtype"错误.

I had error 'illegal use of longtype' , because of the column DATA_DEFAULT that contain a type of long.

是否可以将长类型存储到anotehr表中?

Is there an alterantive way where I can store a long type into anotehr table?

推荐答案

这是对 LONG 数据类型的使用的限制. 您不能创建具有LONG属性的对象类型.

It is a restriction on usage of LONG data type. You cannot create an object type with a LONG attribute.

SQL> CREATE TABLE t AS SELECT data_default FROM user_tab_cols;
CREATE TABLE t AS SELECT data_default FROM user_tab_cols
                         *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL>

或者，您可以使用 TO_LOB 作为解决方法.它将转换为CLOB数据类型.

Alternatively, you could use TO_LOB as a workaround. Which would convert it into CLOB data type.

例如，

SQL> CREATE TABLE t AS SELECT TO_LOB(data_default) data_default FROM user_tab_cols;

Table created.

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATA_DEFAULT                                       CLOB

SQL>

在此处查看更多解决方法示例.

See more examples of workarounds here.

这篇关于ORA-00997的变通办法:非法使用LONG数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！