请问有long类型的表如何复制？

lwxxrq · 发表于 2004-12-24 11:29

表t中有一个字段类型是long并且不可能更改，现在我要复制这个表，我用create table newtab as select * from t会报错，这样的表该如何用SQL复制呢？

玉面飞龙 · 发表于 2004-12-24 13:59

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> create table testlong(text long);

Table created.

SQL> insert into testlong values('create table as select ');

1 row created.

SQL> insert into testlong select * from testlong;
insert into testlong select * from testlong
                        *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SQL> commit;

Commit complete.

SQL> declare
  2  tempv long;
  3  begin
  4  select text into tempv from testlong where rownum=1;
  5  insert into testlong values(tempv);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from testlong;

TEXT
--------------------------------------------------------------------------------

create table as select
create table as select

SQL>

lwxxrq · 发表于 2004-12-24 15:44

谢谢版主了，但是如果不用PL/SQL块而只用SQL能实现么？

l2g32003 · 发表于 2004-12-24 16:29

sqlplus 的copy命令

玉面飞龙 · 发表于 2004-12-26 18:27

最初由 l2g32003 发布
[B]sqlplus 的copy命令 [/B]

good
l2g32003的好东东真多阿

http://asktom.oracle.com/~tkyte/Misc/MoveLongs.html

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....

lwxxrq · 发表于 2004-12-27 10:38

谢谢了，根据玉面飞“猫”老兄的指引，我做了如下试验：
SQL> CONN / AS SYSDBA

SQL> create table foo
  2  ( The_Whole_View varchar2(65),
  3 TextLength    number,
  4 TheText       Long );

Table created.

SQL> copy help  --此为copy命令的帮助信息
usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>

} USING <sel>
  <db> : database string, e.g., hr/your_password@d:chicago-mktg
  <opt>  : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
  <table>: name of the destination table
  <cols> : a comma-separated list of destination column aliases
  <sel>  : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.

SQL> copy from system/***@orcl insert foo(the_whole_view, textlength, thetext ) using select owne
r||'.'||view_name, text_length, text from all_views;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
3522 rows selected from system@orcl.
3522 rows inserted into FOO.
3522 rows committed into FOO at DEFAULT HOST connection.

SQL> select count(*) from foo;

  COUNT(*)
----------
   3522
--一些copy命令的相关参数
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!!)

wlidflower · 发表于 2004-12-27 10:52

好贴
保留先!

lwxxrq · 发表于 2004-12-27 11:50

哦，再问一下，各位高手，如何取得long类型字段的前50个字符呢？应该有个函数的吧。

yaanzy · 发表于 2005-3-22 10:03

好像没有直接的函数，可以先转换成clob再用dbms_lob.substr来取

ablom · 发表于 2006-1-11 21:42

好贴!

请问有long类型的表如何复制？

浏览过的版块