| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oh, how I hate LONGs!
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:
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....
On Mon, 21 Jul 1997 13:13:07 -0400, Bill Meahan <wmeahan_at_ford.com> wrote:
>UNCLE! I give up!!
>
>How the bloody h$## do I copy a LONG field from one table to another?
>
>I'm trying to do some data archiving - moving "closed" records out of
>the "OLTP" section of my application to an "archive/reporting" area in
>another tablespace. Same database, same instance. I have some tables
>consisting of a primary key field (VARCHAR2(15)) and a LONG. All I want
>to do is copy a record from table_1 to table_1_archive.
>
>Bloody LONG's can't appear in subqueries so I can't do a
>
> INSERT INTO table_1_archive
> ( pk_field_name, long_Field_name)
> SELECT pk_field_name, long_field_name
> FROM table_1
> WHERE pk_field_name = 'pk value'
>
>So how **do** I copy the darned thing????? Do I really have to write an
>external PRO*C program to do it (gosh I hope not!)??
>
>(I also have to do updates but the same issues applu to UPDATE ...
>SELECT)
>
>--
>Bill Meahan wmeahan_at_ford.com
>Ford Motor Company -- End User Support - North America
>Not an official statement of Ford Motor Company or anyone else
>except the author.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |