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
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.
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.
And now let’s call it in my browser.
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.
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.
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;
10 Comments
When I try to serve an mp4 with either Media or PL/SQL from ORDS, it works fine in Chrome, but not in Safari.
When I just serve the video from a URL from the webserver it works fine in Safari.
Have you seen this behavior? Any solutions?
I’ve definitely seen where people are using Safari instead of Chrome, but I’m not sure why.
I can confirm what you’re seeing, I think it’s a Safari thing – see this.
Follow-up question, could one satisfy this requirement with an ORDS based REST API? I think the answer is yes…via a PL/SQL handler, but I haven’t tried it (yet).
Hi Jeff, thanks for all your helpful blog posts here.
We are facing the problem that we run into http-500 / java.lang. OutOfMemoryError when users attempt to download larger media files (e.g. videos) from our application. Unfortunately, the error occurs even for file sizes way smaller than our Tomcat heap. With -Xmx1536m we failed to download a 300 MB file; with a 6 GB heap we start failing with file sizes beyond 1 GB.
We’ve ensured that the source is declared as “ORDS.source_type_media”. And after analyzing the GCs we tried -XX:NewRatio=1 to extend the young generation space – but even that didn’t enable us to download larger files.
Is there a way to configure ORDS, that such BLOB contents are streamed directly to the ServletOutputStream instead of being materialized completely in the RAM? The problem is that our users would like to use the system for even larger files with 10 GB+ and we run multiple copies for different tenants.
Environment: ORDS 21, Oracle 19c, Tomcat 8.5.34 with JVM 1.8.0
The stack trace is as follows, wher we can see that the array involved is being copied, which kind of explains why double the space will be needed during the copy operation:
…
Caused by: java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:3236)
at java.io.ByteArrayOutputStream.grow(ByteArrayOutputStream.java:118)
at java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.java:93)
at java.io.ByteArrayOutputStream.write(ByteArrayOutputStream.java:153)
at java.nio.channels.Channels$WritableByteChannelImpl.write(Channels.java:458)
at oracle.dbtools.common.util.IOStreamsProvider.copy(IOStreamsProvider.java:193)
at oracle.dbtools.common.util.IOStreamsProvider.copy(IOStreamsProvider.java:152)
at oracle.dbtools.common.util.StreamCopy.drain(StreamCopy.java:34)
at oracle.dbtools.http.etags.EntityTagsProvider.etag(EntityTagsProvider.java:131)
at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate.service(ModulesDispatcherDelegate.java:62)
at oracle.dbtools.rest.resource.jdbc.JDBCResourceDispatcher.service(JDBCResourceDispatcher.java:81)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
Apparently the problem is due to us generating eTags. We need the payload to do that, hence the memory consumption. Can you disable eTags for your API and try again?
-- DEFINE TEMPLATE
BEGIN
ORDS.DEFINE_TEMPLATE(
p_module_name => '101',
p_pattern => 'something/',
p_priority => 0,
p_etag_type => 'NONE',
p_comments=> ''
);
commit;
END;
Hi Jeff,
I want to use an ORDS endpoint to store an image in the database. No problem when image is in a file. But I am receiving image as a blob from another service which has captured it from a picture sent from a mobile. I need to test using postman or curl, and storing a “filename” in the table, but can’t seem to get it to work….
curl -X POST –data-binary “4oCwUE….+w4cfX” -H “Content-Type: image/png” -H “filename: test.png” https://…/ords/rest/media/files2/
It doesn’t pick up the content type properly and has a not found error. If i replace the image string with an @file it works fine.
Thanks,
Rob
Not sure what you’re asking me… To help you with your cURL or your app that’s making the HTTPS POST?
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.
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.
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
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!