SQL ORA-00997 的解决方法：非法使用 LONG 数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29116396/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Workaround for ORA-00997: illegal use of LONG datatype
提问by Moudiz
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.
我有错误 'illegal use of longtype' ，因为列 DATA_DEFAULT 包含 long 类型。
Is there an alterantive way where I can store a long type into anotehr table?
有没有一种替代方法可以将长类型存储到 anotehr 表中？
回答by Lalit Kumar B
ORA-00997: illegal use of LONG datatype
ORA-00997: 非法使用 LONG 数据类型
It is a restrictionon usage of LONGdata 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_LOBas 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.
在此处查看更多变通方法示例。
回答by William Robertson
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_defaulta clobfor 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 longcolumn 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.
这种方法在原则上有一些限制，因为一long列可以容纳比varchar2(32760)PL/SQL 将在循环中使用的更多的列。但是，对于大多数列默认表达式，我预计 32K 就足够了。
