How to upload multiple files in 1 call: multipart/form-data

One of my more popular posts on ORDS and REST APIs here is how to upload files. And in fact, I’ve written like 3-4 versions or variations of that post, but I think this is probably the best one:

Definitive example for REST API to add a record to a table

In that example, we do a single row insert with one of the columns being a FILE which is stored in a BLOB.

But, what if you want to make a single HTTP request to upload…multiple ‘things?’ That’s not REST per se, but just straight-up HTML and multipart/form-data (W3 Schools.)

ORDS supports this type of operation. Earlier this year, we added a new implicit bind to the BODY family:

:body – treat the post body as a BLOB
:body_text – treat the post body as a CLOB
:body_json – tread the post body as a JSON object

Example from the Docs

3.1.4 About the :body_json Parameter

There are a few typos in the docs, hopefully those are fixed before you see/read this post, and don’t even notice 🙂

Here’s our table:

SQL

CREATE TABLE BODY_JSON_DEMO_TABLE (
    ID              NUMBER(*, 0)
        GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 CACHE 20 )
    NOT NULL,
    FILE_NAME       VARCHAR2(200),
    FILE_BODY       BLOB,
    CONTENT_TYPE    VARCHAR2(200),
    FILE_VISIBILITY VARCHAR2(10),
    SUBMITTED_BY    VARCHAR2(200),
    SUBMITTED_ON    TIMESTAMP DEFAULT SYSTIMESTAMP
);

Here’s our REST API definition:

PLSQL


-- Generated by ORDS REST Data Services 24.4.0.r3451601
-- Schema: HR  Date: Fri Dec 13 05:51:07 2024 
--
        
BEGIN
 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'json-body',
      p_base_path      => '/json-body/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'json-body',
      p_pattern        => 'multi-part',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'json-body',
      p_pattern        => 'multi-part',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'DECLARE 
    L_PARAMETER_NAME VARCHAR2(4000);
    L_FILE_NAME      VARCHAR2(4000);
    L_CONTENT_TYPE   VARCHAR2(200);
    L_FILE_BODY      BLOB;
    L_BODY_JSON      CLOB;
BEGIN
    L_BODY_JSON := :BODY_JSON;
    HTP.PARAGRAPH;
    HTP.PRINT(''Submitted by: '' || JSON_VALUE(L_BODY_JSON, ''$.submitted_by''));
    HTP.BR;
    HTP.PARAGRAPH;
    HTP.PRINT(''File visibility status: '' || JSON_VALUE(L_BODY_JSON, ''$.file_visibility''));
    HTP.BR;
    HTP.PARAGRAPH;
    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
        );
        HTP.PARAGRAPH;
        HTP.PRINT(''Inserted file #'' || i || '': '' || L_FILE_NAME);
        HTP.BR;
        INSERT INTO BODY_JSON_DEMO_TABLE (
            FILE_NAME,
            FILE_BODY,
            CONTENT_TYPE,
            SUBMITTED_BY,
            FILE_VISIBILITY
        ) VALUES ( L_FILE_NAME,
                   L_FILE_BODY,
                   L_CONTENT_TYPE,
                   JSON_VALUE(L_BODY_JSON, ''$.submitted_by''),
                   JSON_VALUE(L_BODY_JSON, ''$.file_visibility'')
                 );
    END LOOP;
END;');

    
        
COMMIT;

END;
/

Let’s call the API

Note that using multi-part form calls in GUI REST Clients can be…tricky, when it comes to working with 1 or more files, so instead of showing you that, i’m going to go with straight-up cURL, and this from my Mac:

Bash

curl --location 'http://your-url-here.com/ords/hr/json-body/multi-part' \
--form 'files=@"login.sql"' \
--form 'files=@"regions_copy.sql"' \
--form 'submitted_by="thatjeffsmith"' \
--form 'file_visibility="public"'

And the response –

HTML

<p>
Submitted by: thatjeffsmith
<br />
<p>
File visibility status: public
<br />
<p>
<p>
Inserted file #1: login.sql
<br />
<p>
Inserted file #2: regions_copy.sql
<br />

And if we go inspect our table –

How it works

Using the :body_json implicit bind, activates a code path in ORDS such that the REST handler can process the contents of the request body in JSON attributes, but also accounts for arrays of things.

This is the most interesting part of the POST REST Handler code:

PLSQL

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
        );
        HTP.PARAGRAPH;
        HTP.PRINT(''Inserted file #'' || i || '': '' || L_FILE_NAME);
        HTP.BR;
        INSERT INTO BODY_JSON_DEMO_TABLE (
            FILE_NAME,
            FILE_BODY,
            CONTENT_TYPE,
            SUBMITTED_BY,
            FILE_VISIBILITY
        ) VALUES ( L_FILE_NAME,
                   L_FILE_BODY,
                   L_CONTENT_TYPE,
                   JSON_VALUE(L_BODY_JSON, ''$.submitted_by''),
                   JSON_VALUE(L_BODY_JSON, ''$.file_visibility'')
                 );
    END LOOP;

So there’s a FOR LOOP that iterates that array of things passed on the request. And we have some new ORDS helper functions, you can see we’re calling: