Oracle如何使用sqlldr将图片加载到Blob列中 [英] Oracle how to load a picture into a blob column using sqlldr

问题描述

我想将员工照片加载到per_images表中.Per_images过去对于图像有一个很长的原始列，但是现在变成了blob.

I would like to load employee photos into the table per_images. Per_images used to have a long raw column for the image, but this has changed to a blob.

以下用于长long列的sql loader控制文件:

The following sql loader control file used to work with the long raw column:

options (bindsize 9529)
load data
infile 0211664.jpg "fix 9529"
append
into table PER_IMAGES
(image raw(9529),
parent_id constant 6598,
table_name constant "PER_PEOPLE_F",
image_id "PER_IMAGES_s.nextval")

其中9529是jpg图片的大小.我应该如何更改raw(9529)以允许加载到Blob列中?

where 9529 is the size of the jpg picture. How should I change the raw(9529) to instead allow loading into a blob column?

推荐答案

我通常使用另一种方式使用SQL * Loader加载BLOB数据.基本上，我导入一个包含文件名的文本文件，然后在控制文件中，然后告诉SQL * Loader实际内容来自lobfile.

I usually use a different way to load BLOB data using SQL*Loader. Basically I import a text file that contains the filenames and in the control file, I then tell SQL*Loader that the actual content comes from a lobfile.

在您的情况下，这意味着您需要创建一个文本文件，其中包含(仅)jpg文件名.然后，控制文件应如下所示:

In your case this means you would need to create text file that contains (only) the filename of the jpg. The control file then should look like this:

LOAD DATA
infile data.txt
TRUNCATE
INTO TABLE per_images
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
  input_file FILLER,
  image_id "PER_IMAGES_s.nextval",  
  parent_id constant 6598,
  table_name constant "PER_PEOPLE_F",  
  image lobfile(input_file) terminated by eof
)

输入文件 data.txt 如下所示:

0211664.jpg

重要的是， image lobfile ... 部分位于末尾，并且任何常量定义都应首先出现在控制文件中.

It's important that the image lobfile ... part is at the end and that any constant definition comes first in the control file.

使用这种方法对我来说似乎简单得多，因为您不需要知道输入文件的大小，并且可以通过SQL * Loader运行来加载多张图片，如果需要，运行起来可能会快得多加载大量图片.

Using this kind of approach seems much simpler to me because you do not need to know the size of the input file and you can load more than one picture with a SQL*Loader run which is most probably a lot faster if you need to load a large amount of pictures.

如果要加载一张以上的图片，则输入文件需要包含到目前为止在控制文件中提供的常量值.取得以下输入文件:

If you want to load more than one picture, the input file needs to contain the constant values that you supplied so far inside the control file. Take the following input file:

6598,PER_PEOPLE_F,0211664.jpg
6599,PER_PEOPLE_F,0123456.jpg
6600,PER_PEOPLE_X,0987654.jpg

然后，您可以使用一个控制文件加载所有三张图片:

Then you can load all three pictures with a single control file:

LOAD DATA
infile data.txt
TRUNCATE
INTO TABLE per_images
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
  parent_id,
  table_name,
  input_file FILLER,
  image_id "PER_IMAGES_s.nextval",  
  image lobfile(input_file) terminated by eof
)

控制文件将永远不会改变，只会更改 data.txt 文件的内容.

The control file will never change, only the contents of the data.txt file.

如果完全删除了 raw(9529)，您的原始控制文件将为我工作:

Your original control file works for me, if the raw(9529) is removed completely:

options (bindsize 9529)
load data
infile 0211664.jpg "fix 9529"
append
into table PER_IMAGES
(
  image,
  parent_id constant 6598,
  table_name constant "PER_PEOPLE_F",
  image_id "PER_IMAGES_s.nextval"
)

这篇关于Oracle如何使用sqlldr将图片加载到Blob列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！