Procedure to download file from a given url in Ora

问题:

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?

回答1:

you need see documentation for package UTL_HTTP

this is the sample of using

this is the code from the sample above

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;
/