A new feature
An ORDS user (or application) can now upload multiple files as part of a multipart/form-data POST request under various conditions. How can this be achieved with an ORDS endpoint?
NOTE: Before you continue, it might be helpful to refresh your memory on, or learn more about multipart/form-data as a Content-Type. Details here.
First, you must become acquainted with some newly introduced PL/SQL procedures and functions. You can find these in your ORDS Metadata.
The single function and procedures used in this example.
The code
Now that you know where to look for these new procedures and functions, I’ll walk through my code.
Resource Handler
Here is the code I’m using for my Resource Handler (i.e., the ORDS API). It is a POST request that accepts the following parameters:
l_body_jsonl_parameter_namel_file_namel_content_typel_file_body
NOTE: These parameters shouldn't be confused with the required parameters of the procedures themselves. These are parameters that I'm declaring to be used later in the ORDS procedures.
Also, pay special attention to these three pieces of Resource Handler code:
| Line 2 | L_BODY_JSON CLOB := :BODY_JSON |
| Lines 9 and 10 | ORDS.BODY_FILE_COUNT |
| Line 11 | ORDS.GET_BODY_FILE |
DECLARE
L_BODY_JSON CLOB := :BODY_JSON;
L_PARAMETER_NAME VARCHAR2(4000);
L_FILE_NAME VARCHAR2(4000);
L_CONTENT_TYPE VARCHAR2(200);
L_FILE_BODY BLOB;
BEGIN
HTP.P( 'Number of files that were received: ' || ORDS.BODY_FILE_COUNT);
FOR i IN 1..ORDS.BODY_FILE_COUNT LOOP
ORDS.GET_BODY_FILE(
P_FILE_INDEX => i,
P_PARAMETER_NAME => L_PARAMETER_NAME,
P_FILE_NAME => L_FILE_NAME,
P_CONTENT_TYPE => L_CONTENT_TYPE,
P_FILE_BLOB => L_FILE_BODY
);
INSERT INTO BODYFILESDEMO (
FILENAME,
CONTENTTYPE,
FILEBODY
) VALUES ( L_FILE_NAME,
L_CONTENT_TYPE,
L_FILE_BODY );
HTP.P('Inserted File: ' || L_FILE_NAME );
END LOOP;
END;Expand for this example’s complete PL/SQL ORDS Resource Module definition.
-- Generated by ORDS REST Data Services 24.3.0.r2620924
-- Schema: ORDSDEMO Date: Fri Oct 04 07:28:00 2024
--
BEGIN
ORDS.DEFINE_MODULE(
p_module_name => 'body.files.demo',
p_base_path => '/v1/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'body.files.demo',
p_pattern => 'example',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'body.files.demo',
p_pattern => 'example',
p_method => 'POST',
p_source_type => 'plsql/block',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'DECLARE
L_BODY_JSON CLOB := :BODY_JSON;
L_PARAMETER_NAME VARCHAR2(4000);
L_FILE_NAME VARCHAR2(4000);
L_CONTENT_TYPE VARCHAR2(200);
L_FILE_BODY BLOB;
BEGIN
HTP.P( ''Number of files that were received: '' || ORDS.BODY_FILE_COUNT);
FOR i IN 1..ORDS.BODY_FILE_COUNT LOOP
ORDS.GET_BODY_FILE(
P_FILE_INDEX => i,
P_PARAMETER_NAME => L_PARAMETER_NAME,
P_FILE_NAME => L_FILE_NAME,
P_CONTENT_TYPE => L_CONTENT_TYPE,
P_FILE_BLOB => L_FILE_BODY
);
INSERT INTO BODYFILESDEMO (
FILENAME,
CONTENTTYPE,
FILEBODY
) VALUES ( L_FILE_NAME,
L_CONTENT_TYPE,
L_FILE_BODY );
HTP.P(''Inserted File: '' || L_FILE_NAME );
END LOOP;
END;');
COMMIT;
END;And here is the table I’m using:
And the DDL, should you choose to recreate the table:
CREATE TABLE BODYFILESDEMO
(
ID NUMBER (*,0) GENERATED BY DEFAULT AS IDENTITY
( START WITH 1 CACHE 20 ) NOT NULL ,
FILENAME VARCHAR2 (200) ,
CONTENTTYPE VARCHAR2 (200) ,
FILEBODY BLOB
)
;Practical example
In practice, here is how everything works. I’m using Postman as a proxy for my application (i.e., client); I have it set up like this:
multipart/form-data
Next, if I were to issue a POST request to my ORDS endpoint (using Postman), here are the results:
In short, where before, I couldn’t INSERT multiple files via an ORDS POST request, now I can.
Dissecting the POST
Using the :body_json implicit parameter (you can review all the ORDS implicit parameters here), in concert with the ORDS.body_file_count function and the ORDS.get_body_file procedure, you can now send multipart/form-data with files (as seen in this POST request). You might also include JSON form data in the POST request body in these requests, but it is not compulsory.
There are considerations though; read on.
A closer look
- When
POSTing a multipart/form-data request (such as the one in this example), I must bind this:BODY_JSONimplicit parameter to something, even if that something will be an empty property (i.e., even if myPOSTrequest doesn’t have form data in JSON format). - ORDS interprets this handler code as such:
- ORDS knows to receive form data in JSON format; however, even if no form data is present, it knows that there may be multiple files in the
POSTrequest.
- ORDS knows to receive form data in JSON format; however, even if no form data is present, it knows that there may be multiple files in the
- And if there are numerous files, ORDS can use the
ORDS.BODY_FILE_COUNTfunction to count how many files there are (using theFOR LOOP) and then with theORDS.GET_BODY_FILEfunction perform the next operation, which in this case is anINSERT INTOtable operation.
DECLARE
L_BODY_JSON CLOB := :BODY_JSON;
L_PARAMETER_NAME VARCHAR2(4000);
L_FILE_NAME VARCHAR2(4000);
L_CONTENT_TYPE VARCHAR2(200);
L_FILE_BODY BLOB;
BEGIN
HTP.P( 'Number of files that were received: ' || ORDS.BODY_FILE_COUNT);
FOR i IN 1..ORDS.BODY_FILE_COUNT LOOP
ORDS.GET_BODY_FILE(
P_FILE_INDEX => i,
P_PARAMETER_NAME => L_PARAMETER_NAME,
P_FILE_NAME => L_FILE_NAME,
P_CONTENT_TYPE => L_CONTENT_TYPE,
P_FILE_BLOB => L_FILE_BODY
);
INSERT INTO BODYFILESDEMO (
FILENAME,
CONTENTTYPE,
FILEBODY
) VALUES ( L_FILE_NAME,
L_CONTENT_TYPE,
L_FILE_BODY );
HTP.P('Inserted File: ' || L_FILE_NAME );
END LOOP;
END;TL;DR implicit bind parameter review
As far as a multipart/form-data with files POST request goes, ORDS “:BODY_" bind parameters now include and support the following:
| Impact Bind Parameter | About files… | About form data… | About JSON… |
|---|---|---|---|
:BODY | Accepts only one file (to be used with BLOB files). | Multiple files are accessible using the new ORDS.BODY_FILE function and procedures. | n/a |
:BODY_TEXT | Accepts only one file (to be used with CLOB files). | ORDS will automatically recognize form data and handle it accordingly. | n/a |
:BODY_JSON | ORDS will automatically recognize form data and handle it accordingly. | Form data will NOT be automatically recognized. | Will be treated accordingly where form data, in JSON format, exists in the POST body. |
multipart/form-data with files POST requests under various conditions.One final note
These changes only relate to multipart/form-data with files POST requests! Media types such as the following are not the focus of this article:
- application/x-url-urlencoded
- application/x-www-form-urlencoded
- application/json, and
- multipart/form-data without files
The end
That’s it for now. I’m sure there will be questions. I hope to put together a working example in a Flask app (a remix of our LiveLab workshop) soon. So stay tuned. I’ll update you as I learn more. Leave a comment if anything is unclear, and share if you think this is helpful.
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
What is new in Oracle REST Data Services 24.2?
