Moving LONGS Around
The question "How do I move a LONG column from one table to another" comes
up from time to time. The following shows how to use SQLPlus to copy
longs from table to table or database to database. Note that this
does not work for long raws. There simply are no good solutions
for long raws without coding C.
The sql*plus copy command will do it for longs. 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@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@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....
All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of
merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or
consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or
these materials.