Multi files loading in ORDS POST request

One of many new features introduced in ORDS 24.3 is the ability to upload multiple files as part of POST request (multipart/form-data) to an ORDS endpoint.
 
To get into an example, first we need to familiar ourselves with the recent enhancements to procedures and functions introduced in ORDS supplied packages.
 
Method to save the file being uploaded into database session for later use.
 
PROCEDURE add_body_file (
  p_parameter_name    IN varchar2,
  p_file_name         IN varchar2,
  p_content_type      IN varchar2,
  p_file_blob         IN blob);
 
method to return the number of files saved in the session.
 
FUNCTION body_file_count RETURN NUMBER
 
Method to return the file specified at the index, that was saved into database session.
 
PROCEDURE get_body_file (
  p_file_index        IN NUMBER,
  p_parameter_name    OUT varchar2,
  p_file_name         OUT varchar2,
  p_content_type      OUT varchar2,
  p_file_blob         OUT blob);
 
method to clear the files that were stored in the session. Files are also cleared automatically when the connection is returned to pool.
 
PROCEDURE clear_body_files;
 
 
Here is the Implementation for the REST module using the above methods.
 
demo@FREEPDB1> create table t( file_id number generated always as identity
  2     , parameter_name varchar2(80)
  3     , file_name varchar2(80)
  4     , content_type varchar2(80)
  5*    , file_blob blob );
 
Table T created.
 
demo@FREEPDB1> -- Generated by Oracle SQL Developer REST Data Services 24.3.0.284.2209
demo@FREEPDB1> -- Exported REST Definitions from ORDS Schema Version 24.3.0.r2620924
demo@FREEPDB1> -- Schema: DEMO   Date: Fri Oct 25 17:44:53 IST 2024
demo@FREEPDB1> --
  2  BEGIN
  3    ORDS.ENABLE_SCHEMA(
  4        p_enabled             => TRUE,
  5        p_schema              => 'DEMO',
  6        p_url_mapping_type    => 'BASE_PATH',
  7        p_url_mapping_pattern => 'demo',
  8        p_auto_rest_auth      => FALSE);
  9
 10    ORDS.DEFINE_MODULE(
 11        p_module_name    => 'body.files.demo',
 12        p_base_path      => '/V1/',
 13        p_items_per_page =>  25,
 14        p_status         => 'PUBLISHED',
 15        p_comments       => NULL);
 16    ORDS.DEFINE_TEMPLATE(
 17        p_module_name    => 'body.files.demo',
 18        p_pattern        => 'example',
 19        p_priority       => 0,
 20        p_etag_type      => 'HASH',
 21        p_etag_query     => NULL,
 22        p_comments       => NULL);
 23    ORDS.DEFINE_HANDLER(
 24        p_module_name    => 'body.files.demo',
 25        p_pattern        => 'example',
 26        p_method         => 'POST',
 27        p_source_type    => 'plsql/block',
 28        p_items_per_page =>  0,
 29        p_mimes_allowed  => '',
 30        p_comments       => NULL,
 31        p_source         =>
 32  'declare
 33      l_json clob := :body_json;
 34      l_parameter_name varchar2(4000) ;
 35      l_file_name varchar2(4000) ;
 36      l_content_type varchar2(4000) ;
 37      l_file_blob blob;
 38  begin
 39      htp.p(''Number of files received = ''|| ords.body_file_count);
 40
 41      for k in 1..ords.body_file_count
 42      loop
 43          ords.get_body_file
 44              ( p_file_index => k
 45              , p_parameter_name => l_parameter_name
 46              , p_file_name => l_file_name
 47              , p_content_type => l_content_type
 48              , p_file_blob => l_file_blob );
 49
 50         insert into t(
 51              parameter_name
 52             , file_name
 53             , content_type
 54             , file_blob )
 55          values( l_parameter_name
 56              , l_file_name
 57              , l_content_type
 58              , l_file_blob );
 59         htp.p('' Processed file #''||k) ;
 60      end loop;
 61  end;'
 62        );
 63
 64
 65    COMMIT;
 66  END;
 67* /
 
PL/SQL procedure successfully completed.
 
demo@FREEPDB1>
 
In practice, here is how the things work. I’m using the Postman as a proxy for my application                 (i.e, the client) 
 
 

 
The header part of the request looks something like this

 
 
The output from the database after the above POST call looks like this
 
demo@FREEPDB1> select parameter_name, dbms_lob.getlength(file_blob) from t;
 
PARAMETER_NAME       DBMS_LOB.GETLENGTH(FILE_BLOB)
_________________ ________________________________
file_one                                        70
file_two                                      1604
 
 
when posting multipart/form-data request, we must bind :BODY_JSON implicit parameter to something, even if that something will be an empty property.