Oracle：将带有 blob 的表导出到可以再次导入的 .sql 文件

Oracle: export a table with blobs to an .sql file that can be imported again

提问by user685428

I have a table "Images" with two fields:

我有一个包含两个字段的表“图像”：

• Name VARCHAR2
• Data BLOB

• 名称 VARCHAR2
• 数据块

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.

我想将该表导出到我可以在另一个系统上导入的 .sql 文件。我尝试使用 Oracle SQL Developer 的“数据库卸载”助手执行此操作。然而，生成的文件只包含名称的内容，而不包含数据。因此，导入后我会得到所有的名字，但数据字段到处都是空的。

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

我真的更喜欢它只是一个文件（我看到了一些例子，其中包括将数据转储到 fs 上的每个字段一个文件......）

Is it possible to generate such a script with SQL Developer? or is there any other way/tool to do so?

是否可以使用 SQL Developer 生成这样的脚本？或者有没有其他方法/工具可以这样做？

采纳答案by a_horse_with_no_name

I don't think this is possible with SQL Developer (but then I don't use it very often).

我认为 SQL Developer 不可能做到这一点（但我不经常使用它）。

The SQL client I am using - SQL Workbench/J- can do this.

我正在使用的 SQL 客户端 - SQL Workbench/J- 可以做到这一点。

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:

它可以创建一个使用特殊（特定于工具）表示法来引用外部文件的 SQL 脚本，例如：

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.

以上语句只能用SQL Workbench 再次执行。它与任何其他 SQL 客户端都不兼容。

Use utl_raw

使用 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 reallysmall blob values:

另一种选择是使用“blob 文字”，但由于 Oracle 对字符文字 4000 字节的限制，这仅适用于非常小的 blob 值：

INSERT INTO images
  (name, data)
VALUES
  ('foobar', to_blob(utl_raw.cast_to_raw('......')));

where the character literal for the cast_to_rawcall 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).

其中cast_to_raw调用的字符文字将包含 BLOB 的十六进制值。由于这需要每个“blob 字节”有 2 个字符，因此您无法处理大于 2000 字节的 BLOB。但是这种语法几乎适用于所有 Oracle SQL 工具（如果它们可以处理很长的行的脚本）。

SQL*Loader input file

SQL*Loader 输入文件

The third alternative is to export the data into a text file that can be imported using SQL*Loader:

第三种选择是将数据导出到可以使用 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:

连同以下 SQL*Loader 控制文件：

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.

这可以使用 SQL*Loader 加载，因此不需要 SQL Workbench 来导入数据。

More details are in the manual

更多细节在手册中

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.

正如 Alex 在他的评论中指出的那样，您还可以使用 DataPump 导出 - 但这要求您有权访问服务器上的文件系统。以上方案都是将数据存储在客户端。

回答by user685428

Thx for your answer. I used the third alternative. First I downloaded SQL Workbench/J. Then I used the following command to make an export:

谢谢你的回答。我使用了第三种选择。首先，我下载了 SQL Workbench/J。然后我使用以下命令进行导出：

WbExport -type=text -file='c:\temp\Images' delimiter='|' -decimal=',' -sourcetable=Images -formatfile=oracle;

This produced a Images.txt file and many Images_r*_c2.data files and a Images.ctl file.

这产生了一个 Images.txt 文件和许多 Images_r*_c2.data 文件和一个 Images.ctl 文件。

I could then use the following command to import:

然后我可以使用以下命令导入：

sqlldr myuser@myhost/mypassword control=Images.ctl

回答by Kaushik Nayak

This is definitely possible in SQL developer.

这在 SQL 开发人员中绝对是可能的。

• First you need to export the table in the source location choosing appropriate table(s).

• 首先，您需要在源位置导出表，选择适当的表。

Tools > Database Export

工具 > 数据库导出

• Select output format as loaderrather than insert , excel which we normally use.

• 选择输出格式 asloader而不是我们通常使用的 insert , excel 。

Following these steps would create sqlldrcontrol files and data files and also the create tableddl if you chose the option.You can use them to import(sqlldr) data in the destination.

遵循这些步骤将创建sqlldr控制文件和数据文件以及create tableddl（如果您选择该选项sqlldr）。您可以使用它们在目标中导入（）数据。

This is a better solution and is portable in terms of extraction and distribution . It gives the flexibility of delivering components to be deployed through code repositories.

这是一个更好的解决方案，并且在提取和分发方面是可移植的。它提供了交付要通过代码存储库部署的组件的灵活性。

Here is a link that explains it step by step.

这是一个逐步解释它的链接。

Exporting Multiple BLOBs with Oracle SQL Developer

使用 Oracle SQL Developer 导出多个 BLOB

回答by Diogo Kollross

If you absolutely need to use a single .sql file to import the BLOB you can generate the script using PL/SQL:

如果您绝对需要使用单个 .sql 文件来导入 BLOB，您可以使用 PL/SQL 生成脚本：

set serveroutput on
declare
  lob_in blob;
  i integer := 0;
  lob_size integer;
  buffer_size integer := 1000;
  buffer raw(32767);
begin
  select
    data, dbms_lob.getlength(data)
    into lob_in, lob_size
  from images
  where name = 'example.png';

  for i in 0 .. (lob_size / buffer_size) loop
    buffer := dbms_lob.substr(lob_in, buffer_size, i * buffer_size + 1);
    dbms_output.put('dbms_lob.append(lob_out, hextoraw(''');
    dbms_output.put(rawtohex(buffer));
    dbms_output.put_line('''));');
  end loop;
end;

Its output will be the BLOB's content encoded like:

它的输出将是 BLOB 的内容，编码如下：

dbms_lob.append(lob_out, hextoraw('FFD8FFE0...0000'));
dbms_lob.append(lob_out, hextoraw('00000000...0000'));
...
dbms_lob.append(lob_out, hextoraw('007FFFD9'));

Which you can load into an already inserted row with PL/SQL:

您可以使用 PL/SQL 将其加载到已插入的行中：

declare
  lob_out blob;
begin
  select data into lob_out
  from images
  where name = 'example.png'
  for update;

  dbms_lob.append(lob_out, hextoraw('FFD8FFE0...0000'));
  dbms_lob.append(lob_out, hextoraw('00000000...0000'));
  ...
  dbms_lob.append(lob_out, hextoraw('007FFFD9'));
end;

Just remember the resulting .sql file will be huge.

请记住，生成的 .sql 文件会很大。

回答by user474491

SQL workbench uses a special file format for blob data, in addition to .sql. If you can accept such files, an even simpler solution is to use Oracle's Original import and export. (It is deprecated, but unlike Oracle's DataPump, it does not require access rights on the server.)

除了 .sql 之外，SQL 工作台还为 blob 数据使用一种特殊的文件格式。如果您可以接受此类文件，则更简单的解决方案是使用 Oracle 的原始导入和导出。（它已被弃用，但与 Oracle 的 DataPump 不同，它不需要服务器上的访问权限。）

Here is a nice tutorial on the export part.

这是关于导出部分的一个很好的教程。