SQL 将图像插入 BLOB Oracle 10g
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21855935/
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
Inserting Image Into BLOB Oracle 10g
提问by devdar
I am trying to insert an image into an BLOB field in a signatures which i will then select from the table and render on a report. I cannot seem to figure how to get the image into the table. I did an insert however when i render only the path to the image was shown on the report and not the image itself.
我正在尝试将图像插入到签名中的 BLOB 字段中，然后我将从表中进行选择并呈现在报告上。我似乎无法弄清楚如何将图像放入表格中。但是，当我渲染报告中仅显示图像的路径而不是图像本身时，我做了一个插入。
Table
桌子
CREATE TABLE esignatures (
office NUMBER(6,0) NOT NULL,
username VARCHAR2(10) NOT NULL,
iblob BLOB NOT NULL
)
INSERT Statement (SQL)
插入语句 (SQL)
INSERT INTO esignatures
VALUES (100, 'BOB', utl_raw.cast_to_raw('C:\pictures\image1.png'));
I know for sure i am inserting the String location in the form of a HEX value how can i get the image HEX value in the table so when i render i will see the image being displayed.
我确定我正在以十六进制值的形式插入字符串位置，如何在表格中获取图像的十六进制值，以便在渲染时看到正在显示的图像。
回答by tbone
You cannot access a local directory from pl/sql. If you use bfile, you will setup a directory (create directory) on the server where Oracle is running where you will need to put your images.
您无法从 pl/sql 访问本地目录。如果您使用 bfile，您将在运行 Oracle 的服务器上设置一个目录（创建目录），您需要在其中放置图像。
If you want to insert a handful of images from your local machine, you'll need a client side app to do this. You can write your own, but I typically use Toad for this. In schema browser, click onto the table. Click the data tab, and hit + sign to add a row. Double click the BLOB column, and a wizard opens. The far left icon will load an image into the blob:
如果您想从本地计算机插入少量图像，则需要一个客户端应用程序来执行此操作。您可以自己编写，但我通常使用 Toad 来完成此操作。在模式浏览器中，单击表格。单击数据选项卡，然后按 + 号添加一行。双击 BLOB 列，将打开一个向导。最左边的图标会将图像加载到 blob 中：
SQL Developer has a similar feature. See the "Load" link below:
SQL Developer 具有类似的功能。请参阅下面的“加载”链接：
If you need to pull images over the wire, you can do it using pl/sql, but its not straight forward. First, you'll need to setup ACL list access (for security reasons) to allow a user to pull over the wire. See this articlefor more on ACL setup.
如果您需要通过网络拉取图像，您可以使用 pl/sql 来完成，但它不是直接的。首先，您需要设置 ACL 列表访问权限（出于安全原因）以允许用户拉断线路。有关ACL 设置的更多信息，请参阅此文章。
Assuming ACL is complete, you'd pull the image like this:
假设 ACL 已完成，您可以像这样拉取镜像：
declare
l_url varchar2(4000) := 'http://www.oracleimg.com/us/assets/12_c_navbnr.jpg';
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_raw RAW(2000);
l_blob BLOB;
begin
-- Important: setup ACL access list first!
DBMS_LOB.createtemporary(l_blob, FALSE);
l_http_request := UTL_HTTP.begin_request(l_url);
l_http_response := UTL_HTTP.get_response(l_http_request);
-- Copy the response into the BLOB.
BEGIN
LOOP
UTL_HTTP.read_raw(l_http_response, l_raw, 2000);
DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;
insert into my_pics (pic_id, pic) values (102, l_blob);
commit;
DBMS_LOB.freetemporary(l_blob);
end;
Hope that helps.
希望有帮助。
回答by Dmitry Nikiforov
You should do something like this:
你应该做这样的事情：
1) create directory object what would point to server-side accessible folder
1）创建将指向服务器端可访问文件夹的目录对象
CREATE DIRECTORY image_files AS '/data/images'
/
2) Place your file into OS folder directory object points to
2）将您的文件放入操作系统文件夹目录对象指向
3) Give required access privileges to Oracle schema what will load data from file into table:
3) 授予 Oracle 模式所需的访问权限，以便将数据从文件加载到表中：
GRANT READ ON DIRECTORY image_files TO scott
/
4) Use BFILENAME, EMPTY_BLOB functions and DBMS_LOB package (example NOT tested - be care) like in below:
4) 使用 BFILENAME、EMPTY_BLOB 函数和 DBMS_LOB 包（示例未测试 - 请注意）如下所示：
DECLARE
l_blob BLOB;
v_src_loc BFILE := BFILENAME('IMAGE_FILES', 'myimage.png');
v_amount INTEGER;
BEGIN
INSERT INTO esignatures
VALUES (100, 'BOB', empty_blob()) RETURN iblob INTO l_blob;
DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY);
v_amount := DBMS_LOB.GETLENGTH(v_src_loc);
DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount);
DBMS_LOB.CLOSE(v_src_loc);
COMMIT;
END;
/
After this you get the content of your file in BLOB column and can get it back using Java for example.
在此之后，您可以在 BLOB 列中获取文件的内容，并且可以使用例如 Java 将其取回。
edit: One letter left missing: it should be LOADFROMFILE.
编辑：遗漏了一个字母：它应该是 LOADFROMFILE。
