How to Upload Files with an ORDS POST Handler

Uploading a file through a REST API is a POST whose body is the file itself. In an ORDS PL/SQL handler, the raw body arrives as a BLOB in the :body bind, and its MIME type in :content_type. A header can carry the file name. This guide stores uploaded files in a table and shows the one rule that is easy to miss: :body can be read only once.

Before You Start

You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.

ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.

The handler joins the lounge module from How to Write a POST Handler That Inserts Rows in ORDS.

Syntax

declare
  v_file blob := :body;          -- read the body once, into a variable
begin
  insert into files (name, mime_type, content) values (:file_name, :content_type, v_file);
end;

A Table for Files

Example:

create table offer_files (
  file_id      number generated always as identity primary key,
  file_name    varchar2(200) not null,
  mime_type    varchar2(100) not null,
  content      blob not null,
  uploaded_at  timestamp default systimestamp
);

The Upload Handler

The X-File-Name request header is mapped to the :file_name bind with ORDS.DEFINE_PARAMETER. The handler answers 201 with the new file ID and its size.

Example:

begin
  ords.define_template(p_module_name => 'lounge', p_pattern => 'files');

  ords.define_handler(p_module_name => 'lounge',
                      p_pattern     => 'files',
                      p_method      => 'POST',
                      p_source_type => ords.source_type_plsql,
                      p_source      => q'[
declare
  v_file blob := :body;        -- the request body; :body can be read only once
  v_id   offer_files.file_id%type;
begin
  insert into offer_files (file_name, mime_type, content)
  values (:file_name, :content_type, v_file)
  returning file_id into v_id;
  :status_code := 201;
  owa_util.mime_header('application/json', true);
  htp.p(json_object('file_id' value v_id, 'bytes' value dbms_lob.getlength(v_file)));
end;]');

  ords.define_parameter(p_module_name => 'lounge', p_pattern => 'files', p_method => 'POST',
                        p_name => 'X-File-Name', p_bind_variable_name => 'file_name',
                        p_source_type => 'HEADER', p_param_type => 'STRING',
                        p_access_method => 'IN');
  commit;
end;
/

Upload Two Files

curl sends a file as the raw body with --data-binary; -d would alter it.

Example:

curl -k -X POST https://localhost:8443/ords/nimbus/lounge/files \
  -H "Content-Type: image/png" \
  -H "X-File-Name: dxb-lounge-offer.png" \
  --data-binary @dxb-lounge-offer.png

Output (HTTP 201 Created):

{
    "file_id": 1,
    "bytes": 17879
}

A CSV file goes the same way with Content-Type: text/csv, and the table then holds both:

Example:

select file_id, file_name, mime_type, dbms_lob.getlength(content) as bytes from offer_files;

Output:

   FILE_ID FILE_NAME               MIME_TYPE       BYTES
__________ _______________________ ____________ ________
         1 dxb-lounge-offer.png    image/png       17879
         2 fuel_prices.csv         text/csv          279

Read :body Only Once

:body is a stream: the first reference consumes it. A first version of this handler inserted :body and then returned DBMS_LOB.GETLENGTH(:body); the insert worked, but the second reference was empty and the response said "bytes": null. Copying :body into a variable first, as above, avoids that.

Things to Know

• For multipart form uploads from HTML forms, ORDS provides the files through ORDS.BODY_FILE_COUNT and ORDS.GET_BODY_FILE instead.
• Limit the size of uploads at the web server or ORDS level, and check MIME types before storing files.
• Protect upload endpoints with a privilege; an open upload URL invites abuse.

Related Guides

• How to Set HTTP Status Codes and Headers in ORDS Handlers
• How to Work with Temporary LOBs in PL/SQL (DBMS_LOB)

Conclusion

ORDS hands a POST body to PL/SQL as the :body BLOB, with :content_type for its MIME type, and request headers can carry details such as the file name. Read :body once into a variable, store it, and answer 201 with what was saved.