Upload BLOB files using PL/SQL HTTP

Question

Usually, I use the following structure to send POST request with contents of varchar2 and numbers .. etc.

content := '{"Original File Name":"'||V_HOMEBANNER_1_EN_NAME(indx)||'"}';
  url := 'https://api.appery.io/rest/1/db/Names'; 
  req     := utl_http.begin_request(url, 'POST',' HTTP/1.1');
  UTL_HTTP.set_header(req, 'X-Appery-Database-Id', '5f2dac54b02cc6402dbe');
  utl_http.set_header(req, 'content-type', 'application/json');
  UTL_HTTP.set_header(req, 'X-Appery-Session-Token', sessionToken);
  utl_http.set_header(req, 'Content-Length', LENGTH(content));
  utl_http.write_text(req, content);
  res := utl_http.get_response(req);


BEGIN
  LOOP
    utl_http.read_line(res, buffer);
 END LOOP;
 utl_http.end_response(res);
EXCEPTION
WHEN utl_http.end_of_body THEN
  utl_http.end_response(res);
END;

And It works just fine. However, now I want to send/upload a blob files (images of jpg) into some MongoDB collection called 'Files' (so url := ttps://api.appery.io/rest/1/db/Files). The collection guide has the following cURL as a general advice :

curl -X POST \
  -H "X-Appery-Database-Id: 5f2dac54b02cc6402dbe" \
  -H "X-Appery-Session-Token: <session_token>" \
  -H "Content-Type: <content_type>" \
  --data-binary '<file_content>' \
  https://api.appery.io/rest/1/db/files/<file_name>

But I could not translate this cURL into PL/SQL request. Specifically, the part (--data-binary '')

I have these BLOB files in Oracle table and they are stored with their names as follows:

+-----------+--------------+
| File_Name | File_content |
+-----------+--------------+
| PIC_1.jpg | BLOB         |
| PIC_2.jpg | BLOB         |
| PIC_3.jpg | BLOB         |
+-----------+--------------+

My question, how to upload these images into the targeted URL?

@JeffreyKemp Thanks, but I need to send content as BLOB not CLOB, the files are stored in BLOB and the API request to receive them as BLOB too. I could not find any example sending BLOB over chunks (with WRITE_RAW). — Hawk
– Hawk, Commented Mar 25, 2015 at 7:56
Did you try changing the content type and replacing write_text with write_raw? — Jeffrey Kemp
– Jeffrey Kemp, Commented Mar 25, 2015 at 8:02
@JeffreyKemp I did that, but I keep getting ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1525 ORA-29261: bad argument. With Clob it was sending successfully, but the contents received corrupted at the web server. I only changed the content to back BLOB and used write_raw instead of write_text — Hawk
– Hawk, Commented Mar 25, 2015 at 8:33
I gave you some hints but I think you answered your own question - I recommend you put your code as an answer and accept it. — Jeffrey Kemp
– Jeffrey Kemp, Commented Mar 25, 2015 at 8:52

Hawk · Accepted Answer · 2015-03-25 09:01:36Z

5

Inspired by this blog advised by @JeffreyKemp, I go it working by only replacing write_text() with write_raw() in order to send content body as BLOB (Requested by the API).

The following code is the critical part of my function with the changes needed:

  content :=  V_HOMEBANNER_1_EN(indx);
  file_name := 'test.jpg';
  url  := 'https://api.appery.io/rest/1/db/files/'||file_name;      
  req     := utl_http.begin_request(url, 'POST',' HTTP/1.1');
  UTL_HTTP.set_header(req, 'X-Appery-Database-Id', '53fae4b02cc4021dbe');
  UTL_HTTP.set_header(req, 'X-Appery-Session-Token', sessionToken);
  utl_http.set_header(req, 'content-type', 'image/jpeg');
  req_length := DBMS_LOB.getlength(CONTENT);
  DBMS_OUTPUT.PUT_LINE(req_length);


  --IF MSG DATA UNDER 32KB LIMIT:
  IF req_length <= 32767
  THEN 
  begin
  utl_http.set_header(req, 'Content-Length', req_length);
  utl_http.write_raw(req, content);
  exception
  when others then
  DBMS_OUTPUT.PUT_LINE(sqlerrm);
  end;

  --IF MSG DATA MORE THAN 32KB
  ELSIF req_length >32767
  THEN
  BEGIN
  DBMS_OUTPUT.PUT_LINE(req_length);
  utl_http.set_header(req, 'Transfer-Encoding', 'Chunked');
  WHILE (offset < req_length)
  LOOP
  DBMS_LOB.read(content, amount, offset, buffer);
  utl_http.write_raw(req, buffer);
  offset := offset + amount;
  END LOOP;
  exception
  when others then
  DBMS_OUTPUT.PUT_LINE(sqlerrm);
  end;


  END IF;

  l_http_response := UTL_HTTP.get_response(req);
  UTL_HTTP.read_text(l_http_response, response_body, 32767);
  UTL_HTTP.end_response(l_http_response);

Tried and tested for both greater and smaller than 32KB images/jpg.

answered Mar 25, 2015 at 9:01

Hawk

5,17014 gold badges55 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naeel Maqsudov Over a year ago

You should not use "Transfer-Encoding: Chunked" header in this way. It means that Content-Length header is ignored and each chunk should be preceded by "\r\n[chunk_length_in_hex]\r\n". See description Content-Type: application/octet-stream header is much better in this case

Collectives™ on Stack Overflow

Upload BLOB files using PL/SQL HTTP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related