[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 971
  • Last Modified:

Long Data Type records Insert into Table.

How to copy the records from a table which has LONG Data type to another Table of the same structure.

From

Table1(
X varchar2(100)
Y number(10)
Z LONG )

copy to

Table2(
X varchar2(100)
Y number(10)
Z LONG )

Thanking in Advance...!
0
UMESHNC
Asked:
UMESHNC
1 Solution
 
seazodiacCommented:
try:

insert into Table2 select * from Table1;
0
 
andrewstCommented:
> insert into Table2 select * from Table1;

Thst will give the error:
ORA-00997: illegal use of LONG datatype
0
 
andrewstCommented:
You can:
- use the COPY command
- Write a PL/SQL program to do it, provided all the long values are <= 32K
- Write a Pro*C program to do it
- Convert it to CLOB:
create table Table2(
X varchar2(100)
Y number(10)
Z CLOB );

insert into Table2 select X, Y, TO_LOB(Z) from Table1;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wemyCommented:
In 8i you should not be using LONGS (long raws) they are deprecated types.

There are many restrictions with them -- this is one of them (not so with
CLOBS/BLOBS)

but you can use COPY command as the following:

SQL> copy from User_Name/Password@DB1 insert Table2 (X, Y, Z ) using select X,Y,Z from Table1;

or in the same Schema:

SQL> copy TO Table2 (X, Y, Z ) using select X,Y,Z from Table1;
 and you will be asked about the password of the TO DB.
 
You'll want to set

set arraysize N -- amount of rows the copy command will copy with each fetch
set long N -- size of your longest long
set copycommit M -- number of fetches to do before commit (N*M rows!!)
 
in plus before doing this.  see the manual for all the options....
0
 
konektorCommented:
what about using export and import ? ...
0
 
UMESHNCAuthor Commented:
I am Using Oracle 9.2 release... but there are tables created when 8/8i was there .

Now how can i convert this tables which has long column into CLOB/BLOB.
Will this convertion effect any of my data in tables.


0
 
seazodiacCommented:


In Oracle9i , you can use this one-liner to change long column to LOB:

"alter table <table_name> modify <long col name>  CLOB"


this will not affect any of your data.
 
 
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now