从Oracle 11g中的给定URL下载文件并将其保存到Blob类型列的过程 [英] Procedure to download file from a given url in Oracle 11g and save it into the blob type column

问题描述

我遇到了问题.我需要在Oracle 11g中创建一个过程，该过程将从给定行获取URL并从该URL下载文件，并将其保存在blob类型列中.你们能告诉我我要达到这个目标的方法吗?

I am stuck with a problem. I need to create a procedure in Oracle 11g which will get the URL from a given row and download the file from that URL and will save it in a blob type column. Can you guys tell me what my approach should be to achieve this?

推荐答案

，您需要查看软件包这是使用的示例

这是上面示例中的代码

begin

  load_binary_from_url('http://www.oracle.com/us/hp07-bgf3fafb-db12c-2421053.jpg');
end;  


CREATE TABLE http_blob_test (
  id    NUMBER(10),
  url   VARCHAR2(255),
  data  BLOB,
  CONSTRAINT http_blob_test_pk PRIMARY KEY (id)
);

CREATE SEQUENCE http_blob_test_seq;

CREATE OR REPLACE PROCEDURE load_binary_from_url (p_url  IN  VARCHAR2) AS
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
  l_blob           BLOB;
  l_raw            RAW(32767);
BEGIN
  -- Initialize the BLOB.
  DBMS_LOB.createtemporary(l_blob, FALSE);

  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(p_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, 32767);
      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 the data into the table.
  INSERT INTO http_blob_test (id, url, data)
  VALUES (http_blob_test_seq.NEXTVAL, p_url, l_blob);

  -- Relase the resources associated with the temporary LOB.
  DBMS_LOB.freetemporary(l_blob);
EXCEPTION
  WHEN OTHERS THEN
    UTL_HTTP.end_response(l_http_response);
    DBMS_LOB.freetemporary(l_blob);
    RAISE;
END load_binary_from_url;
/

这篇关于从Oracle 11g中的给定URL下载文件并将其保存到Blob类型列的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！