| 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: data moving / FETCH / LONG / etc..
A copy of this was sent to "John W. Kinkead" <kinkead_at_ghg.net>
(if that email address didn't require changing)
On Tue, 22 Sep 1998 08:20:38 -0500, you wrote:
>I am trying to move data from one table to another table which is the
>same with the addition of a unique-id field. The problem is that one of
>the fields in the original table is long, and I'm getting errors when
>attempting to solve the problem various ways.
>
>Some background info:
> SCO OPENDESKTOP 5
> ORACLE 7
> PRO*C
>
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....
>Initially, I tried to use the
>
> EXEC SQL INSERT INTO table2 ("unique-id", field1, field2, etc...)
> SELECT field1, field2, etc.
> FROM table2;
>
>but it complains about the long field, so then I tried to
>
> SELECT field1, field2, etc...
> FROM table2
> ...
> EXEC SQL FETCH CURSOR1 INTO :field1, :field2, etc....
>
>and now it complains that I'm fetching a null value, so I try
>
> SELECT nvl(field1, -1), nvl(field2, -1), ...
> FROM table2
>
>but apparrently you can't use the NVL function with the long field.
>
>Any suggestions? Perhaps there is an easier way to move the data to a
>new table with one additional field. Any help is appreciated and let me
>know if you need more info. Thanks.
>
>
>John
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 Tue Sep 22 1998 - 10:18:46 CDT
 |
 |