oracle sql developer中如何导出clob字段数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42244941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export clob field datas in oracle sql developer
提问by Prashobh Chandran
How to export clob field data's in oracle sql developer. Currently clob field data's can't export in oracle sql developer.
如何在oracle sql developer中导出clob字段数据。目前在oracle sql developer中无法导出clob字段数据。
回答by Alex Poole
If you don't want to (or can't) export and importyour data, and really want it as a set of insert statements, you can use SQL Developer's built-in formatting tools to automatically split your CLOBs into multiple chunks that are small enough to be valid as string literals, and then spool the result to a file:
如果您不想（或不能）导出和导入您的数据，并且确实希望将其作为一组插入语句，您可以使用 SQL Developer 的内置格式化工具将您的 CLOB 自动拆分为多个块小到足以作为字符串文字有效，然后将结果假脱机到一个文件中：
spool clob_export.sql
select /*insert*/ * from your_table;
spool off
With more recent versions you can use the sqlformatcommandto control the output format without needing to modify the query; this is equivalent:
在更新的版本中，您可以使用该sqlformat命令来控制输出格式，而无需修改查询；这是等效的：
set sqlformat insert
spool clob_export.sql
select * from your_table;
spool off
The generated insert statements will look something like:
生成的插入语句将类似于：
REM INSERTING into YOUR_TABLE
SET DEFINE OFF;
Insert into YOUR_TABLE (ID,CLOB_COLUMN) values (1,TO_CLOB('... up to 4k of characters with quotes escaped ...')
|| TO_CLOB('... up to 4k of characters with quotes escaped ...')
|| TO_CLOB('... up to 4k of characters with quotes escaped ...')
...
|| TO_CLOB('... up to 4k of characters with quotes escaped ...'));
回答by Ferenc Tóth
The following EXP_IMP_LOB package can export and import CLOB, NCLOB, BLOB type column data using simple SQL (text) files.
下面的 EXP_IMP_LOB 包可以使用简单的 SQL（文本）文件导出和导入 CLOB、NCLOB、BLOB 类型的列数据。
How to use:
如何使用：
first of all install the package onto both source and target schemas. To export run this select
首先将包安装到源和目标模式上。要导出运行此选择
select * from table( EXP_IMP_LOB.EXPORT('table_name','lob_column_name','condition') );
where the Table_Name and LOB_Column_Name define the data column and the optional Condition defines the the row or rows. If there is no condition, then every row data will be exported row by row.
其中 Table_Name 和 LOB_Column_Name 定义数据列，可选 Condition 定义行。如果没有条件，则将逐行导出每一行数据。
Example:
例子：
select * from table( EXP_IMP_LOB.EXPORT('person','image','id=103' ) );
Result:
结果：
/******************************************************
TABLE :PERSON
COLUMN :IMAGE
ROW :103
******************************************************/
BEGIN
EXP_IMP_LOB.IMPORT_NEW;
EXP_IMP_LOB.IMPORT_APPEND ( 'FFD8FFE000104A464....23232323232');
EXP_IMP_LOB.IMPORT_APPEND ( '32323232323232323....798999AA2A3');
.........
EXP_IMP_LOB.IMPORT_APPEND ( 'B2316524267279AA9....51401FFFD9');
EXP_IMP_LOB.IMPORT_UPDATE ( 'PERSON','IMAGE','103' );
COMMIT;
END;
/
So, the export converts the binary data to 400 char length hexa strings and creates a script from it. I used ..... to symbolize many chars, because that is only a sample above.
因此，导出将二进制数据转换为 400 个字符长度的六进制字符串并从中创建脚本。我用 ..... 来象征许多字符，因为那只是上面的一个例子。
DO NOT SORT THE RESULT!
不要对结果进行排序！
To import, you only have to install the package onto the target schema too and run this script above in the target schema. That's all.
要导入，您只需要将包安装到目标模式并在目标模式中运行上面的脚本。就这样。
...more:
...更多的：
- The source and target table name, column name must be the same!
- The Table (both source and target) must have Primary key and they must be identical.
- The EXPORT function can detect the primary key automatically. Theoretically it can manage composed keys too...
- The size of a hexa string is defined in G_LENGTH global variable. 200 chars means 400 hexa chars.
- The additional procedures:
- IMPORT_NEW : resets the package variables to prepare it to accept a new LOB
- IMPORT_APPEND : converts the hexa string to a binary data and append it the package variable
- IMPORT_UPDATE : updates the given table, row, column with the package variable
- DIRECT_SQL : executes the given SQL using the global LOB variable as a parameter. eg: EXP_IMP_LOB.DIRECT_SQL( 'insert into ANY_TABLE ( ID, IMAGE ) values ( 123, :1 )' );
- 源表名和目标表名、列名必须相同！
- 表（源和目标）必须具有主键并且它们必须相同。
- EXPORT 函数可以自动检测主键。理论上它也可以管理组合键......
- hex 字符串的大小在 G_LENGTH 全局变量中定义。200 个字符表示 400 个十六进制字符。
- 附加程序：
- IMPORT_NEW ：重置包变量以准备接受新的 LOB
- IMPORT_APPEND : 将 hexa 字符串转换为二进制数据并将其附加到包变量
- IMPORT_UPDATE ：使用包变量更新给定的表、行、列
- DIRECT_SQL ：使用全局 LOB 变量作为参数执行给定的 SQL。例如：EXP_IMP_LOB.DIRECT_SQL('插入ANY_TABLE (ID, IMAGE) 值(123, :1)');
/*============================================================================================*/
create or replace package EXP_IMP_LOB is
/*============================================================================================*/
type T_STRING_LIST is table of varchar2( 32000 );
---------------------------------------------------------------------------
function EXPORT ( I_TABLE_NAME in varchar2
, I_COLUMN_NAME in varchar2
, I_WHERE in varchar2 default null
) return T_STRING_LIST pipelined;
---------------------------------------------------------------------------
---------------------------------------------------------------------------
procedure IMPORT_NEW;
---------------------------------------------------------------------------
---------------------------------------------------------------------------
procedure IMPORT_APPEND ( I_RAW in varchar2);
---------------------------------------------------------------------------
---------------------------------------------------------------------------
procedure DIRECT_SQL ( I_SQL in varchar2 );
---------------------------------------------------------------------------
---------------------------------------------------------------------------
procedure IMPORT_UPDATE ( I_TABLE_NAME in varchar2
, I_COLUMN_NAME in varchar2
, I_PK in varchar2
);
---------------------------------------------------------------------------
end;
/
/*============================================================================================*/
create or replace package body EXP_IMP_LOB is
/*============================================================================================*/
G_TABLE_NAME varchar( 40 );
G_COLUMN_NAME varchar( 40 );
G_COLUMN_TYPE varchar( 40 );
G_PK_KEY varchar( 4000 );
G_PK_LST varchar( 4000 );
G_LENGTH number := 200;
G_BLOB blob;
G_CLOB clob;
---------------------------------------------------------------------------
procedure GET_PK ( I_TABLE_NAME in varchar ) is
---------------------------------------------------------------------------
L_SEP varchar ( 40 ) := ',';
L_DATA_TYPE varchar2( 30 );
begin
G_PK_KEY := '';
G_PK_LST := '';
for L_A_PK in ( select COLUMN_NAME
from USER_CONSTRAINTS UC
, USER_CONS_COLUMNS DBC
where UC.CONSTRAINT_TYPE = 'P'
and DBC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
and DBC.TABLE_NAME = I_TABLE_NAME
order by position
)
loop
if nvl( length( G_PK_KEY ), 0 ) + length( L_A_PK.COLUMN_NAME ) < 4000 then
select DATA_TYPE into L_DATA_TYPE from user_tab_columns where table_name = G_TABLE_NAME and column_name = L_A_PK.COLUMN_NAME;
if instr( L_DATA_TYPE, 'CHAR') > 0 then
G_PK_KEY := G_PK_KEY||'''''''''||'||L_A_PK.COLUMN_NAME||'||''''''''||'''||L_SEP||'''||';
elsif instr( L_DATA_TYPE, 'DATE') > 0 then
G_PK_KEY := G_PK_KEY||'''TO_DATE(''''''||TO_CHAR('||L_A_PK.COLUMN_NAME||',''YYYY.MM.DD HH24:MI:SS'')||'''''',''''YYYY.MM.DD HH24:MI:SS'''')''||'''||L_SEP||'''||';
else
G_PK_KEY := G_PK_KEY||L_A_PK.COLUMN_NAME||'||'''||L_SEP||'''||';
end if;
G_PK_LST := G_PK_LST||L_A_PK.COLUMN_NAME||L_SEP;
end if;
end loop;
G_PK_KEY := substr( G_PK_KEY, 1, length( G_PK_KEY ) - ( 6 + length( L_SEP ) ) );
G_PK_LST := substr( G_PK_LST, 1, length( G_PK_LST ) - length(L_SEP));
end;
---------------------------------------------------------------------------
function EXPORT ( I_TABLE_NAME in varchar2
, I_COLUMN_NAME in varchar2
, I_WHERE in varchar2 default null
) return T_STRING_LIST pipelined is
---------------------------------------------------------------------------
V_BLOB blob;
V_CLOB clob;
V_CUR_SQL varchar( 32000 );
V_LOB_SQL varchar( 32000 );
V_RAW varchar( 32000 );
V_START number;
V_PK varchar( 4000 );
V_REC_SET sys_refcursor;
begin
G_TABLE_NAME := upper( trim( I_TABLE_NAME ) );
G_COLUMN_NAME := upper( trim( I_COLUMN_NAME ) );
GET_PK( G_TABLE_NAME );
select DATA_TYPE into G_COLUMN_TYPE from user_tab_columns where table_name = G_TABLE_NAME and column_name = G_COLUMN_NAME;
if G_COLUMN_TYPE not in ('CLOB','NCLOB','BLOB') then
raise_application_error ( -20001, 'The type of column '||I_COLUMN_NAME||' is not CLOB, NCLOB or BLOB' );
end if;
V_CUR_SQL := 'select '||G_PK_KEY||' from '||G_TABLE_NAME||' where '||nvl( I_WHERE, ' 1 = 1 ');
open V_REC_SET for V_CUR_SQL;
loop
fetch V_REC_SET into V_PK;
exit when V_REC_SET%notfound;
PIPE ROW( '/******************************************************' );
PIPE ROW( ' TABLE :'||G_TABLE_NAME );
PIPE ROW( ' COLUMN :'||G_COLUMN_NAME );
PIPE ROW( ' ROW :'||V_PK );
PIPE ROW( '******************************************************/' );
PIPE ROW( 'BEGIN' );
PIPE ROW( ' EXP_IMP_LOB.IMPORT_NEW;' );
V_LOB_SQL := 'select '||G_COLUMN_NAME||' from '||G_TABLE_NAME||' where ('||G_PK_LST||') in ( select '||V_PK||' from dual )';
if G_COLUMN_TYPE = 'BLOB' then
execute immediate V_LOB_SQL into V_BLOB;
if nvl( dbms_lob.getlength( V_BLOB ), 0 ) > 0 then
V_START := 1;
for L_I IN 1..ceil( dbms_lob.getlength( V_BLOB ) / G_LENGTH )
loop
V_RAW := dbms_lob.substr( V_BLOB, G_LENGTH, V_START );
PIPE ROW( ' EXP_IMP_LOB.IMPORT_APPEND ( '''||V_RAW||''');' );
V_START := V_START + G_LENGTH;
end loop;
PIPE ROW( ' EXP_IMP_LOB.IMPORT_UPDATE ( '''||G_TABLE_NAME||''','''||G_COLUMN_NAME||''','''||replace(V_PK,'''','''''')||''' ); ');
PIPE ROW( ' COMMIT;' );
end if;
else
execute immediate V_LOB_SQL into V_CLOB;
if nvl( dbms_lob.getlength( V_CLOB ), 0 ) > 0 then
V_START := 1;
for L_I IN 1..ceil( dbms_lob.getlength( V_CLOB ) / G_LENGTH )
loop
V_RAW := UTL_RAW.CAST_TO_RAW( dbms_lob.substr( V_CLOB, G_LENGTH, V_START ) );
PIPE ROW( ' EXP_IMP_LOB.IMPORT_APPEND ( '''||V_RAW||''');' );
V_START := V_START + G_LENGTH;
end loop;
PIPE ROW( ' EXP_IMP_LOB.IMPORT_UPDATE ( '''||G_TABLE_NAME||''','''||G_COLUMN_NAME||''','''||replace(V_PK,'''','''''')||''' ); ');
PIPE ROW( ' COMMIT;' );
end if;
end if;
PIPE ROW( 'END;' );
PIPE ROW( '/' );
PIPE ROW( ' ' );
end loop;
close V_REC_SET;
return;
end;
---------------------------------------------------------------------------
procedure IMPORT_NEW is
---------------------------------------------------------------------------
begin
G_BLOB := null;
G_CLOB := null;
end;
---------------------------------------------------------------------------
procedure IMPORT_APPEND ( I_RAW in varchar2 ) is
---------------------------------------------------------------------------
V_BLOB blob;
begin
V_BLOB := hextoraw( I_RAW );
if nvl( dbms_lob.getlength( V_BLOB ), 0 ) > 0 then
if nvl( dbms_lob.getlength( G_BLOB ), 0 ) = 0 then
G_BLOB := V_BLOB;
else
DBMS_LOB.APPEND( G_BLOB, V_BLOB );
end if;
end if;
end;
---------------------------------------------------------------------------
procedure DIRECT_SQL ( I_SQL in varchar2 ) is
---------------------------------------------------------------------------
begin
if nvl( dbms_lob.getlength( G_BLOB ), 0 ) > 0 then
execute immediate I_SQL using G_BLOB;
else
execute immediate I_SQL using G_CLOB;
end if;
commit;
end;
-- I downloaded this from the Net:
function clobfromblob( p_blob blob ) return clob is
l_clob clob;
l_dest_offsset integer := 1;
l_src_offsset integer := 1;
l_lang_context integer := dbms_lob.default_lang_ctx;
l_warning integer;
begin
if p_blob is null then
return null;
end if;
dbms_lob.createTemporary(lob_loc => l_clob
,cache => false);
dbms_lob.converttoclob(dest_lob => l_clob
,src_blob => p_blob
,amount => dbms_lob.lobmaxsize
,dest_offset => l_dest_offsset
,src_offset => l_src_offsset
,blob_csid => dbms_lob.default_csid
,lang_context => l_lang_context
,warning => l_warning);
return l_clob;
end;
---------------------------------------------------------------------------
procedure IMPORT_UPDATE ( I_TABLE_NAME in varchar2
, I_COLUMN_NAME in varchar2
, I_PK in varchar2
) is
---------------------------------------------------------------------------
V_SQL varchar( 32000 );
begin
G_TABLE_NAME := upper( trim( I_TABLE_NAME ) );
G_COLUMN_NAME := upper( trim( I_COLUMN_NAME ) );
GET_PK( G_TABLE_NAME );
select DATA_TYPE into G_COLUMN_TYPE from user_tab_columns where table_name = G_TABLE_NAME and column_name = G_COLUMN_NAME;
V_SQL := 'update '||I_TABLE_NAME||' set '||I_COLUMN_NAME||' = :1 where ('||G_PK_LST||') in ( select '||I_PK||' from dual )';
if G_COLUMN_TYPE in ( 'CLOB', 'NCLOB' ) then
G_CLOB := clobfromblob ( G_BLOB );
G_BLOB := null;
DIRECT_SQL( V_SQL );
elsif G_COLUMN_TYPE in ( 'BLOB' ) then
DIRECT_SQL( V_SQL );
end if;
end;
end;
/
回答by W. Elbashier
I was faced with this issue when tried to copy a table that has CLOB column from one database server to another. Oracle SQL Developer's "Tools > Copy Database" tool wasn't a good choice for me because I needed to have another name for the destination table, so I had to use "Tools > Export Database" which generates the insert statements for you in a .sql file if you choose so.
当我尝试将具有 CLOB 列的表从一个数据库服务器复制到另一个数据库服务器时遇到了这个问题。Oracle SQL Developer 的“工具 > 复制数据库”工具对我来说不是一个好的选择，因为我需要为目标表使用另一个名称，所以我不得不使用“工具 > 导出数据库”为您生成插入语句.sql 文件，如果您选择这样做。
Steps I did:
我做的步骤：
Run this in the source DB:
SELECT someColumA, someColumB, SubStr(myClobColumn, 1, 4000) myClobColumn_part1, SubStr(myClobColumn, 4001, 8000) myClobColumn_part2, . . . SubStr(Clob_field, .., ..) Clob_field_partN, OtherColumns FROM YourTable ;Right click and export to a file, all insert statements would be written to a temporary table: EXPORT_TABLE.
Run that file in the destination DB server to create EXPORT_TABLE
Now, run this to import the first 4000 chars:
Insert Into YourDestinationTable(.., myClobColumn, ..) SELECT .., myClobColumn_part1, .. FROM EXPORT_TABLE ;Run this to append the rest of the Clob parts:
Update YourDestinationTable A set myClobColumn = myClobColumn || (Select myClobColumn_part2 From EXPORT_TABLE B Where B.tableKey = A.tableKey and myClobColumn_part2 is not null) ; Update YourDestinationTable A set myClobColumn = myClobColumn || (Select myClobColumn_part3 From EXPORT_TABLE B Where B.tableKey = A.tableKey and myClobColumn_part3 is not null) ; ...
在源数据库中运行它：
SELECT someColumA, someColumB, SubStr(myClobColumn, 1, 4000) myClobColumn_part1, SubStr(myClobColumn, 4001, 8000) myClobColumn_part2, . . . SubStr(Clob_field, .., ..) Clob_field_partN, OtherColumns FROM YourTable ;右键单击并导出到文件，所有插入语句都将写入临时表：EXPORT_TABLE。
在目标数据库服务器中运行该文件以创建 EXPORT_TABLE
现在，运行它以导入前 4000 个字符：
Insert Into YourDestinationTable(.., myClobColumn, ..) SELECT .., myClobColumn_part1, .. FROM EXPORT_TABLE ;运行此命令以附加其余的 Clob 部分：
Update YourDestinationTable A set myClobColumn = myClobColumn || (Select myClobColumn_part2 From EXPORT_TABLE B Where B.tableKey = A.tableKey and myClobColumn_part2 is not null) ; Update YourDestinationTable A set myClobColumn = myClobColumn || (Select myClobColumn_part3 From EXPORT_TABLE B Where B.tableKey = A.tableKey and myClobColumn_part3 is not null) ; ...
till part N.
直到第 N 部分。
You're done.
你完成了。
回答by ankit
I have same problem when i try to export insert, fields that are clob or blob then it not included in insert query.
当我尝试导出插入、clob 或 blob 字段时，我遇到了同样的问题，然后它不包含在插入查询中。
This is Problem due to insert limit that is not avail to clob datatype size.
这是由于插入限制对 clob 数据类型大小无效的问题。
Solution:
解决方案：
I export data in XML or PDF or loader or Json. Then i got data that is clob or blob.
我以 XML 或 PDF 或加载程序或 Json 格式导出数据。然后我得到了clob或blob的数据。
Tools > Database Export > select connection > uncheck export DDl if you want only data > check export data > select format: XML > next > next > next > next >finish.
工具 > 数据库导出 > 选择连接 > 如果只需要数据，则取消选中导出 DDl > 检查导出数据 > 选择格式：XML > 下一个 > 下一个 > 下一个 > 下一个 > 完成。
open file where you save that XML file and verify.
打开保存该 XML 文件的文件并进行验证。
enjoy...
请享用...
