You have stored or generated some bits that you want to stream down to your web client from your Oracle Database. And while ORDS likes to format things as JSON for you, you really don’t want that to happen in this case.

Here, I have ALREADY handled the output processing via my SQL, I just wants ORDS to pass it down DIRECTLY to the client.

When you define your HTTP (GET) handler as a ‘Media Resource‘ or ‘Media‘ – this tells ORDS to simply stream the content directly back down to the client.

This is half the battle. You have one more thing you need to do –

Your client needs to know what it’s getting, so it knows what to do with it. This is handled by the HTTP Response Header for Content-Type.

Like most things in ORDS – we make that EASY.

The SQL for that BLOCK is quite simple! The first column is the content-type, and the second column is the BLOB (or CLOB) –

SELECT CONTENT_TYPE,
       CONTENT
  FROM MEDIA
 WHERE ID = :id

The code assumes a few things. One, that the content type for the data is variable – in this case I’m storing it as a column in the table, so it can be anything I want.

If your use case is that ‘this will ALWAYS BE JSON’, then you’d simply replace the table column with a ‘application/json’ literal in your SQL.

The other assumption is that there is only ONE row returned. You can have a query that returns a ton of rows, but ORDS will stream only the first one…but DON’T DO THAT – use a WHERE CLAUSE to bring back just the one row.

In my case, my template for the module has the ID in it – ‘ora_magazine/media/:id/content’

So let’s make a request for record 161

Record 161 has CONTENT_TYPE value of ‘application/PDF’, Chrome knows what to do with that.

But Jeff, I’m using PL/SQL

Then the EAISEST thing to do is have a version of that PL/SQL implemented as a FUNCTION, and simply call that in a SQL statement, and carry on.

Forgive my PL/SQL – it’s extremely rudimentary – we don’t really CARE what your PL/SQL does, so long as it returns a BLOB.

CREATE OR REPLACE FUNCTION GET_MEDIA 
(
  MEDIA_ID IN NUMBER 
) RETURN BLOB AS 
 payload blob;
BEGIN
  SELECT content INTO payload FROM media WHERE ID = MEDIA_ID;
		RETURN payload;
END GET_MEDIA;

Then I create a new template/handler combo to use the FUNCTION call vs ‘just’ the straight up SQL.

Handler remains a GET and SQL based, but uses PL/SQL to retrieve the BLOB.

Now, let’s go get one of my files, in this case, it’s an animated GIF, using our new endpoint powered by the FUNCTION.

But Jeff, I ONLY WANT TO USE PL/SQL!

This is still possible.

You’re just going to write ALL of the code to make that happen.

And I’m going to borrow someone else’s implementation.

CREATE OR REPLACE PROCEDURE download_file
(
    media_id NUMBER
)
AS
    vMIMETYPE VARCHAR2(256);
    vLENGTH NUMBER;
    vFILENAME VARCHAR2(2000);
    vBLOB BLOB;
BEGIN
    SELECT file_name, content_type, content INTO vFILENAME, vMIMETYPE, VBLOB
				FROM media
				WHERE id = media_id;
 
    vLENGTH := DBMS_LOB.GETLENGTH(vBLOB);
    owa_util.mime_header(NVL(vMIMETYPE, 'application/octet'), FALSE);
    htp.p('Content-length: ' || vLENGTH);
    htp.p('Content-Disposition: attachment; filename=' || SUBSTR(vFILENAME, INSTR(vFILENAME, '/') + 1) || '');
    owa_util.http_header_close;
    wpg_docload.download_file(vBLOB);
END download_file;
/

I won’t explain the code to you, it’s quite elegant and easy to work through – Kudos to the original author! I did make some simple updates, making the filename and content-type dynamic based on the table records vs hard-coding them.

Then we need to create yet another template and GET handler, and this time it’s of type PL/SQL.

Call that either 1 or 3 lines of code, either way, it’s pretty darn simple.

And now let’s call it in my browser.

This does an actual file download…

One last thing on Source Types

There are MANY source types in addition to MEDIA. I’ve inventoried them here.

The most common will be the Collection Query, where we take query results, page them, and re-write them as JSON.

Fine for probably 80+% of your use cases.

Where you don’t want this is where you have –

  • just ONE record
  • MEDIA – just let me stream it
  • the output is already in JSON, back to MEDIA

Everything I’ve written above is for BLOBs. If you’re dealing with CLOBs, then you can still use these techniques assuming you’re ok with just passing TEXT down to your clients.

I’ve written a few posts about how to deal with JSON in your resultsets, whether that’s the entire output, or just one column in the output.

My Code

Again, this is provided as an example, you’ll need to make it ‘production worthy’ and TEST, TEST, TEST.

My MODULE handles much more than getting the file. It lets you UPLOAD the file, get a ‘directory listing’ of the files, get the meta for each file, and get the file itself.

I’ve published this previously, but now I have the two PL/SQL implementations in addition to the pure, easy SQL one.

 
-- Generated by ORDS REST Data Services 20.4.1.r0071440
-- Schema: HR  Date: Thu Mar 18 11:38:31 2021 
--
 
BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'ora_magazine',
      p_base_path      => '/ora_magazine/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/:id/contentPLSQL',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/:id/contentPLSQL',
      p_method         => 'GET',
      p_source_type    => 'resource/lob',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select content_type, get_media(:id)
from media
where id = :id');
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/:id/contentPUREPLSQL',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/:id/contentPUREPLSQL',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'BEGIN
  DOWNLOAD_FILE(MEDIA_ID => :id);
END;');
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select ID ,
FILE_NAME ,
CONTENT_TYPE,
''./'' || id "$record" -- the $ tells ORDS to render this as a LINK
from media
order by id asc -- optional if you want insertion order');
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page => 0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'declare 
 image_id integer; 
 
begin
 
 insert into media (file_name,content_type,content) 
             values  (:file_name,:file_type,:body) -- :body is defined by ORDS
             returning id into image_id;
 :status := 201; -- http status code
 :location := ''./'' || image_id; -- included in the response to access the new record
 
end;');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'ora_magazine',
      p_pattern            => 'media/',
      p_method             => 'POST',
      p_name               => 'X-ORDS-STATUS-CODE',
      p_bind_variable_name => 'status',
      p_source_type        => 'HEADER',
      p_param_type         => 'INT',
      p_access_method      => 'OUT',
      p_comments           => NULL);
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'ora_magazine',
      p_pattern            => 'media/',
      p_method             => 'POST',
      p_name               => 'file_name',
      p_bind_variable_name => 'file_name',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'ora_magazine',
      p_pattern            => 'media/',
      p_method             => 'POST',
      p_name               => 'file_type',
      p_bind_variable_name => 'file_type',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'ora_magazine',
      p_pattern            => 'media/',
      p_method             => 'POST',
      p_name               => 'location',
      p_bind_variable_name => 'location',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/:id',
      p_method         => 'GET',
      p_source_type    => 'json/item',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select FILE_NAME,
      CONTENT_TYPE,
      ID || ''/content'' "$file"
 from MEDIA
where ID = :id');
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/:id',
      p_method         => 'DELETE',
      p_source_type    => 'plsql/block',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'delete from media
where id = :id');
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/:id/content',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'ora_magazine',
      p_pattern        => 'media/:id/content',
      p_method         => 'GET',
      p_source_type    => 'resource/lob',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select CONTENT_TYPE, 
       CONTENT
  from MEDIA
 where ID = :id');
 
 
 
COMMIT;
END;
thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

4 Comments

  1. Avatar

    Hi Jeff,

    I have managed to upload/download files using unprotected web services. But, when i create a button and use below API to download content with unprotected web services then it throws me error:

    API : apex_web_service.make_rest_request(
    p_url => ‘example.com/ords/apex_schema/ora_magazine/media/24/purePLSQL’,
    p_http_method => ‘GET’)

    Error : ORA-22921: length of input buffer is smaller than amount requested

    What is right method to call such media based web service in Oracle APEX ? Do you have any idea about it? Later on , I also want to protect is using OAuth 2.0. But, I am not sure how this media based web service will work.

    • thatjeffsmith

      Sorry, I’m not an APEX guy…but if you Google that message you’ll see there was a known issue/bug in apex that’s been ffx fixed.

  2. Avatar

    We used to host our files in the DB till we reached a dead point. I think in 95% of cases files should be stored in S3 or Oracle Storage — Never in DB

    • thatjeffsmith

      When the files are data, this becomes more interesting conversation.

      I’m showing how to use our technology, I’ll leave it you too figure out best way to take advantage of it.

      Thanks for starting off the conversation with a great point!

Write A Comment