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.
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...
I had error 'illegal use of longtype' , because of the column DATA_DEFAULT that contain a type of long.
Is there an alterantive way where I can store a long type into another table?
The LONG datatype can store variable-length character data containing up to two gigabytes of information. The length of LONG values might be limited by the memory available on your computer. You can use columns defined as LONG in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.
You'll need to create your target table explicitly, not from 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.)
Then you can insert rows in a PL/SQL loop:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With