Re: FAQ: How to move LONG data from one table to another?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/17
Message-ID: <34c7c91c.30165525@inet16>#1/1

On Wed, 14 Jan 1998 15:12:24 +0000, Jeff Lowery <jlowery_at_walldata.com> wrote:

>We're no database neophytes here, but we're stumped at how to move LONG
>data from one table to another in Oracle. I'm sure it's been done....
>--
>------------------------
>Jeff Lowery
>Sr. Product Developer
>Wall Data
>Seattle, WA

look at the sql*plus copy command. It will do it. You need a sql*net connect string that loops back to your local database (not a dblink, a sqlplus connect string, you need to be able to "sqlplus scott/tiger_at_yourdatabase"...  

For example, I just tried:  

create table foo
( The_Whole_View varchar2(65),

  TextLength       number,
  TheText          Long )

/  

which is a table, sort of like all_views (which has a long)... Then I:  

SQL> copy from tkyte/tkyte_at_aria insert foo (the_whole_view, textlength, thetext ) using select owner||'.'||view_name, text_length, text from all_views;  

So the sqlplus command transformed the table for me (the columns are not the same). Also, I could have used a where clause to pick off just some rows.  

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....    

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 17 1998 - 00:00:00 CST