| 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: Copying records that have a LONG field
A copy of this was sent to "Doug Hughes" <doug.hughes_at_johncrane.co.uk>
(if that email address didn't require changing)
On Wed, 9 Sep 1998 12:39:41 +0100, you wrote:
>I'm trying to copy selected records from one table to another. The records
>contain a LONG field.
>
>It would seem that there is no way to do this, as it's not possible to
>include a LONG field in the SELECT sub-query, i.e.
>
>INSERT INTO Table1 (SELECT * FROM Table2 WHERE Clause);
>
>gives the Oracle error ORA-00997 if Table2 contains a LONG field.
>
>Is this an impossible quest? Is there really no way to do this? Any help
>appreciated.
>
>Doug Hughes
>
>
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....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
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 Mon Sep 14 1998 - 10:24:59 CDT
 |
 |