Oracle：将具有blob的表导出到可以再次导入的.sql文件 [英] Oracle: export a table with blobs to an .sql file that can be imported again
问题描述
- 名称VARCHAR2
- 数据BLOB
我想将该表导出到我可以在另一个系统上导入的.sql文件。我试图使用Oracle SQL Developer的数据库卸载助手。但是，生成的文件只包含其中的名称的内容，但不包含数据。因此，在导入之后，我会得到所有的名字，但数据字段将无处不在。
我真的希望只是一个文件（我看到一些例子，包括将数据转储到fs中的每个字段的一个文件...）
是否可以使用SQL Developer生成这样的脚本？或者有什么其他方法/工具吗？
我不认为这是可能的SQL Developer（但那么我不经常使用它）。
我正在使用的SQL客户端 - SQL Workbench / J - 可以这样做。
有几种方法可以导出这些数据。
生成专有脚本
它可以创建一个使用特殊（特定于工具）符号的SQL脚本引用外部文件，如：
INSERT INTO图像
（名称，数据）
VALUES
（'foobar'，{$ blobfile ='blob_r1_c2.data'}）;
上述语句只能再次与SQL Workbench一起执行。它不兼容任何其他SQL客户端。
使用utl_raw
另一个选择是使用blob字面值，但是由于Oracle对于字符文字的限制为4000字节，因此仅适用于 小blob值：
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... ..'）））;
其中 cast_to_raw 调用的字符文字将包含BLOB的十六进制值。因为每个blob字节需要2个字符，所以不能处理大于2000字节的BLOB。但是，这种语法对于几乎所有的Oracle SQL工具（如果他们可以处理非常长的脚本的脚本）将是有效的。
SQL * Loader输入文件
第三种方法是将数据导出为可以使用SQL * Loader导入的文本文件：
文本文件将包含某些东西像这样：
名称数据
foobar blob_r1_c2.data
与以下SQL * Loader控制文件一起：
选项（skip = 1）
LOAD数据字符'WE8ISO8859P15'
INFILE'images.txt'
APPEND
INTO TABLE IMAGES
终止的字符'\''TRAILING NULLCOLS
（
NAME，
lob_file_data FILLER，
数据LOBFILE（lob_file_data）终止EOF
）
这可以使用SQL * Loader加载，因此不需要SQL Workbench导入数据。
更多详细信息在手册中： http://www.sql-workbench.net/manual/command-export.html
修改
正如Alex在其评论中指出的，您还可以使用DataPump导出 - 但这需要您可以访问服务器上的文件系统。上述解决方案都将数据存储在客户端上。
I have a table "Images" with two fields:
- Name VARCHAR2
- Data BLOB
I would like to export that table to a .sql file which I could import on another system. I tried to do so using the "Database unload" assistant of Oracle SQL Developer. However the generated file does just have the content for the names in it but not the data. Thus after importing I would end up with all the names but the data field would be null everywhere.
I'd really prefer it just to be one file (I saw some examples that included dumping the data to one file per field on the fs...)
Is it possible to generate such a script with SQL Developer? or is there any other way/tool to do so?
I don't think this is possible with SQL Developer (but then I don't use it very often).
The SQL client I am using - SQL Workbench/J - can do this.
There are several ways to export this data.
Generate a proprietary script
It can create a SQL script that uses a special (tool specific) notation to reference an external file, something like:
INSERT INTO images
(name, data)
VALUES
('foobar', {$blobfile='blob_r1_c2.data'});
The above statement can only be executed with SQL Workbench again. It is not compatible with any other SQL client.
Use utl_raw
Another alternative is to use a "blob literal", but due to Oracle's limit on 4000 bytes for a character literal, this only works for really small blob values:
INSERT INTO images
(name, data)
VALUES
('foobar', to_blob(utl_raw.cast_to_raw('......')));
where the character literal for the cast_to_raw call would contain the hex values of the BLOB. As this requires 2 characters per "blob byte", you can't handle BLOBs larger than 2000 bytes with that. But that syntax would work for nearly all Oracle SQL tools (if they can handle scripts with very long lines).
SQL*Loader input file
The third alternative is to export the data into a text file that can be imported using SQL*Loader:
The text file would contain something like this:
NAME DATA foobar blob_r1_c2.data
Together with the following SQL*Loader control file:
OPTIONS (skip=1) LOAD DATA CHARACTERSET 'WE8ISO8859P15' INFILE 'images.txt' APPEND INTO TABLE IMAGES FIELDS TERMINATED BY '\t' TRAILING NULLCOLS ( NAME, lob_file_data FILLER, DATA LOBFILE(lob_file_data) TERMINATED BY EOF )
This can be loaded using SQL*Loader and is thus doesn't need SQL Workbench to import the data.
More details are in the manual: http://www.sql-workbench.net/manual/command-export.html
Edit
As Alex has pointed out in his comment, you can also use a DataPump export - but that requires that you have access to the file system on the server. The above solutions all store the data on the client.
这篇关于Oracle：将具有blob的表导出到可以再次导入的.sql文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
