Oracle PL/SQL HTTP File Upload With Post Request

More and more services these days are following a cloud-based hosting model. A cloud-hosted application is one where you pay a subscription fee and the vendor hosts the product for you. This way you don’t have to worry about buying/managing your own servers or paying IT staff to take care of them. This makes integrations more difficult in a sense. You can no longer simply connect in to the database. Instead, vendors usually provide you with API endpoints to use where you can send normal HTTP GET or POST requests.

We are working with a software package that requires sending PDF files through a POST request. This works great in languages like PHP where you can do a simple cURL call. It gets a bit more tricky in PL/SQL. But it is possible.

We’ll begin with the variable definitions. They’re all standard except for the file. This is where we will open the file we’re sending. Our send buffer will be 32K since that’s the limit in Oracle.

    v_request   utl_http.req;
    v_response  utl_http.resp;
    v_blob      blob;
    v_buffer    raw(32767);
    v_file      bfile := bfilename('FILE_STORAGE', 'examplefile.pdf');
    v_length    integer;
    v_offset    number(15) := 1;
    v_value     varchar2(1024);
    v_amount    number(15) := 32767;
    v_name      varchar2(1000);

Next, we have to load the file into a BLOB data type. To do this, we first create a temporary lob as a reference. If you’re loading the file to/from a table, you can use the blob field in the table as a reference instead. You can use the dbms_lob package to open both objects and load the file to the blob.

    dbms_lob.createtemporary(v_blob, true, dbms_lob.call);
    dbms_lob.open(v_file, dbms_lob.lob_readonly);
    dbms_lob.open(v_blob, dbms_lob.lob_readwrite);
    v_length := dbms_lob.getlength(v_file);
    dbms_lob.loadfromfile(v_blob, v_file, v_length);

Next, initiate the HTTP request. We will be using a POST request. You can send any additional headers you need here as well. We will be sending the username/password for the API, the content type, and a transfer header indicating that we will be sending the file in “chunks”.

    v_request := utl_http.begin_request('http://192.168.0.56:8008/api/v1/file/upload', 'POST', 'HTTP/1.1');
    utl_http.set_header(v_request, 'X-APIData-Username', 'user1');
    utl_http.set_header(v_request, 'X-APIData-Password', 'password');
    utl_http.set_header(v_request, 'Content-Type', 'application/octet-stream');
    utl_http.set_header(v_request, 'Transfer-Encoding', 'Chunked');

Since our file is greater than 32KB and we’ll be sending in “chunks”, this is where the buffer comes in to play. The utl_http package expects the RAW datatype. Our procedure will need to take a 32KB chunk from the BLOB buffer and convert it to RAW before it can be sent.

    begin
        while v_offset < v_length loop
            dbms_lob.read(v_blob, v_amount, v_offset, v_buffer);
            utl_http.write_raw(v_request, v_buffer);
            v_offset := v_offset + v_amount;
        end loop;
    end;

Always remember to close all blob/file resources. If you don’t, you’ll end up with a too many open files error after you run it a few times in the same session.

    dbms_lob.close(v_file);
    dbms_lob.close(v_blob);

Lastly, read back the response. Our API is returning all necessary data as response request headers. We will loop through them one by one and print out to the console. Additionally, we will check through the response body as well. This is where you would put any error checking, database updates, etc based on the API response. Again, make sure you close all open resources (the http request). This can be done once the END_OF_BODY exception is thrown.

    begin
        v_response := utl_http.get_response(v_request);
        
        for i in 1 .. utl_http.get_header_count(v_response) loop
            utl_http.get_header(v_response, i, v_name, v_value);
            dbms_output.put_line(v_name || ': ' || v_value);
        end loop;
		
		loop
            utl_http.read_line(v_response, v_value, true);
            dbms_output.put_line(v_value);
        end loop;
    exception when utl_http.end_of_body then
        utl_http.end_response(v_response);
    end;

Here’s the complete code snippet!

declare
    v_request   utl_http.req;
    v_response  utl_http.resp;
    v_blob      blob;
    v_buffer    raw(32767);
    v_file      bfile := bfilename('FILE_STORAGE', 'examplefile.pdf');
    v_length    integer;
    v_offset    number(15) := 1;
    v_value     varchar2(1024);
    v_amount    number(15) := 32767;
    v_name      varchar2(1000);
    
begin    
    dbms_lob.createtemporary(v_blob, true, dbms_lob.call);
    dbms_lob.open(v_file, dbms_lob.lob_readonly);
    dbms_lob.open(v_blob, dbms_lob.lob_readwrite);
    v_length := dbms_lob.getlength(v_file);
    dbms_lob.loadfromfile(v_blob, v_file, v_length);
    
    v_request := utl_http.begin_request('http://192.168.0.56:8008/api/v1/file/upload', 'POST', 'HTTP/1.1');
    utl_http.set_header(v_request, 'X-APIData-Username', 'user1');
    utl_http.set_header(v_request, 'X-APIData-Password', 'password');
    utl_http.set_header(v_request, 'Content-Type', 'application/octet-stream');
    utl_http.set_header(v_request, 'Transfer-Encoding', 'Chunked');
    
    begin
        while v_offset < v_length loop
            dbms_lob.read(v_blob, v_amount, v_offset, v_buffer);
            utl_http.write_raw(v_request, v_buffer);
            v_offset := v_offset + v_amount;
        end loop;
    end;
    
    dbms_lob.close(v_file);
    dbms_lob.close(v_blob);
    
    begin
        v_response := utl_http.get_response(v_request);
        
        for i in 1 .. utl_http.get_header_count(v_response) loop
            utl_http.get_header(v_response, i, v_name, v_value);
            dbms_output.put_line(v_name || ': ' || v_value);
        end loop;
		
		loop
            utl_http.read_line(v_response, v_value, true);
            dbms_output.put_line(v_value);
        end loop;
    exception when utl_http.end_of_body then
        utl_http.end_response(v_response);
    end;
end;

 

Leave a Reply