Re: inserting LONG data types
Date: 1998/06/15
Message-ID: <35863e9a.12117904_at_192.86.155.100>#1/1
A copy of this was sent to Peter Chan <chanp_at_nortel.ca> (if that email address didn't require changing) On Mon, 15 Jun 1998 10:12:14 -0400, you wrote:
>I have two databases.
>
>I am trying to insert information from one of the databases to the other
>database using a database link.
>
>The tables are identical.
>
>So the table schema looks like this:
>
>employee:
>---------
>
>name varchar2(10)
>phone varchar2(5)
>address long
>
>I am running the following query on a test database and I want to get
>this information from the production database.
>
>The query:
>
>insert into employee select * from employee_at_prod;
>
>Why does this not work? And if this does not work, what can I do to
>work around?
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....
>
>Thanks!
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
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 Mon Jun 15 1998 - 00:00:00 CEST