Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to: Copy LONG field from one table to another
A copy of this was sent to Ken Rachynski <krachyn_at_cadvision.com>
(if that email address didn't require changing)
On Wed, 03 Feb 1999 08:35:33 -0700, you wrote:
>Good day,
>
>I have two identical tables that I would like to make have the same
>data. I recall from school that one cannot create a table based on
>another table (CREATE...AS...) where the source table contains a LONG
>field. As a result, I tried to insert data in the second table using a
>sub query (INSERT...SELECT...) and discovered that I can't do that
>either.
>
>How do I get the contents of one LONG field into an identical field in
>another table?
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 Service Industries
Reston, VA USA
--
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 Wed Feb 03 1999 - 11:08:54 CST