New ORDS feature: handling multiple files from a multipart/form-data POST request

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_json
• l_parameter_name
• l_file_name
• l_content_type
• l_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:

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;

-- 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:

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

1. When POSTing a multipart/form-data request (such as the one in this example), I must bind this :BODY_JSON implicit parameter to something, even if that something will be an empty property (i.e., even if my POST request doesn’t have form data in JSON format).
2. 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 POST request.
3. And if there are numerous files, ORDS can use the ORDS.BODY_FILE_COUNT function to count how many files there are (using the FOR LOOP) and then with the ORDS.GET_BODY_FILE function perform the next operation, which in this case is an INSERT INTO table 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:

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!

• Twitter
• YouTube
• LinkedIn
• Instagram
• TikTok
• GitHub
• Mail
• Facebook
• Medium