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.

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.

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”.

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.

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.

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.

Here’s the complete code snippet!


Leave a Reply