ORA-00997的解决方法：非法使用LONG数据类型

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.

我想将系统表user_tab_cols中的一些数据保存到临时表中,以便从中获取转储。

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:

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

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

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

我有错误'非法使用longtype',因为列DATA_DEFAULT包含long类型。

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

有没有一种改变的方式,我可以将长型存储在anotehr表中?

2 个解决方案

#1

ORA-00997: illegal use of LONG datatype

ORA-00997:非法使用LONG数据类型

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

这是对LONG数据类型的使用的限制。您无法创建具有LONG属性的对象类型。

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>

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

或者,您可以使用TO_LOB作为解决方法。哪个会将其转换为CLOB数据类型。

For example,

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.

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

#2

You'll need to create your target table explicitly, not from select *:

您需要显式创建目标表,而不是从select *:

create table demo_copy
( table_name varchar2(30)
, column_name varchar2(30)
, data_type varchar2(106)
, data_type_mod varchar2(3)
, data_type_owner varchar2(30)
, data_length number
, data_precision number
, data_scale number
, nullable varchar2(1)
, column_id number
, default_length number
, data_default clob
, num_distinct number
, low_value raw(32)
, high_value raw(32)
, density number
, num_nulls number
, num_buckets number
, last_analyzed date
, sample_size number
, character_set_name varchar2(44)
, char_col_decl_length number
, global_stats varchar2(3)
, user_stats varchar2(3)
, avg_col_len number
, char_length number
, char_used varchar2(1)
, v80_fmt_image varchar2(3)
, data_upgraded varchar2(3)
, hidden_column varchar2(3)
, virtual_column varchar2(3)
, segment_column_id number
, internal_column_id number
, histogram varchar2(15)
, qualified_col_name varchar2(4000) );

(I've made data_default a clob for more convenient querying.)

(我已经将data_default变成了一个clob,以便更方便地查询。)

Then you can insert rows in a PL/SQL loop:

然后,您可以在PL / SQL循环中插入行:

begin
    for r in (
        select * from user_tab_cols c
        where  rownum <= 2  -- your filter condition here
    )
    loop
        insert into demo_copy values r;
    end loop;
end;

There are some limitations in principle with this approach, as a long column can hold more than the varchar2(32760) that PL/SQL will use in the loop. However, I expect 32K will be enough for most column default expressions.

这种方法在原理上存在一些限制,因为长列可以容纳PL / SQL将在循环中使用的varchar2(32760)。但是,对于大多数列默认表达式,我预计32K就足够了。

#1