It seems kind of weird to do a blog post around a feature that’s fairly well documented, but this came up twice this week, so I figured I’d throw it out there.
Scenario: I want to post up a record to be inserted into a table. The record will consist of a couple of regular values, but also a file, which will be stored as a BLOB.
Ok, how does this happen?
Let’s start from the POST request perspective.
We’re going to send the ‘regular’ data via the header as parameters.
The file is going to go up in the BODY of the request.
What does ORDS do with this?
The secret sauce bits, which are discussed in the docs here, is that ORDS auto binds the data from the body to :body.
It’s only avail on a POST or PUT.
This makes doing our INSERT very easy.
declare
image_id integer;
begin
insert into gallery (title,content_type,image)
values (:title,:content_type,:body)
returning id into image_id;
:status := 201;
:location := image_id;
end;
The other two values are plucked off the request headers:
And that’s it, really.
So the request from POSTMAN goes up, and into my table goes the BLOB.
Step by Step Example
I go into MUCH more detail, and I share all the code for how to insert a ROW via REST API here. It covers all sorts of data, not just LOBs.
You can build this same scenario yourself – exact same code I used, it’s in our DOCS.
21 Comments
Hi Jeff! Very good explanation! I have a problem uploading a pdf file: everything works fine but when I download back the uploaded pdf it result in a blank pdf file, more specifically it it has the same number of pages but they are all blank. PDF metadata are all fine but it seems that the content itself gets corrupted or somehow “encoded” differently. Also the BLOB size is bigger than the file size: if I upload a 26kb pdf, it’s saved as a 43kb BLOB in the Oracle table.
I also tried with a xlsx file and it seems to be corrupted as well when I download it. This is not happening when I upload a text file, in this case the txt file is perfectly fine once downloaded back and has the same size in the BLOB field.
Is it possible that the implicit parameter “:body” must be encoded somehow before proceding with the INSERT? Thank you very much!
What happens if you add the file/BLOB outside of your REST API, does it also get corrupted?
If I add the file outside the REST API it’s fine. No corruption at all. As a test, I also wrote a Python script to read the pdf file in binary mode and add it with a simple insert statement, no problem at all. It seems that I get the problem only using my POST endpoint.
Ok, so then i’d want to see both your POST and GET handlers
Ok, I’ll paste below some code. There’s no GET handler as I download the BLOB directl from SQL Developer datagrid.
Here’s the POST handler definition and the INSERT statement used within the procedure called by the handler.
—– ORDS DEFINITION —–
BEGIN
ORDS.DEFINE_TEMPLATE(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_priority => 0,
p_etag_type => ‘HASH’,
p_etag_query => NULL,
p_comments => ”);
ORDS.DEFINE_HANDLER(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_method => ‘POST’,
p_source_type => ‘plsql/block’,
p_items_per_page => 0,
p_mimes_allowed => ”,
p_comments => ”,
p_source =>
‘BEGIN
RICONOSCI_ESAME (pi_codice => :codice,
pi_data_esame => to_date(:data_esame,”DD/MM/YYYY”),
pi_voto => :voto,
pi_nomefile => :nomefile,
pi_immagine => :body,
pi_matricola => :matricola,
pi_regcarriera => :regcarriera,
po_error_cod => :result_cod,
po_error_msg => :result_msg);
END;’
);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_method => ‘POST’,
p_name => ‘codice’,
p_bind_variable_name => ‘codice’,
p_source_type => ‘HEADER’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_method => ‘POST’,
p_name => ‘data_esame’,
p_bind_variable_name => ‘data_esame’,
p_source_type => ‘HEADER’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => ‘Data esame in formato DD/MM/YYYY’);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_method => ‘POST’,
p_name => ‘ErrorCod’,
p_bind_variable_name => ‘result_cod’,
p_source_type => ‘RESPONSE’,
p_param_type => ‘INT’,
p_access_method => ‘OUT’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_method => ‘POST’,
p_name => ‘ErrorMsg’,
p_bind_variable_name => ‘result_msg’,
p_source_type => ‘RESPONSE’,
p_param_type => ‘STRING’,
p_access_method => ‘OUT’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_method => ‘POST’,
p_name => ‘matricola’,
p_bind_variable_name => ‘matricola’,
p_source_type => ‘HEADER’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_method => ‘POST’,
p_name => ‘nomefile’,
p_bind_variable_name => ‘nomefile’,
p_source_type => ‘HEADER’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_method => ‘POST’,
p_name => ‘regcarriera’,
p_bind_variable_name => ‘regcarriera’,
p_source_type => ‘HEADER’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘libretto’,
p_pattern => ‘riconosci_esame’,
p_method => ‘POST’,
p_name => ‘voto’,
p_bind_variable_name => ‘voto’,
p_source_type => ‘HEADER’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => NULL);
COMMIT;
END;
—- INSERT STATEMENT WITHIN THE PROCEDURE —-
INSERT INTO esami_da_riconoscere (registra_in_carriera,
codice,
voto,
matricola,
data_esame,
nome_allegato,
allegato)
VALUES (pi_regcarriera,
pi_codice,
pi_voto,
pi_matricola,
pi_data_esame,
pi_nomefile,
pi_immagine);
Thanks!
Hi Jeff, we’re investigating further with our sys admins and we found out we deployed ORDS on Tomcat 9 within a docker container running RedHat image “ubi8/ubi:8.4” which seems to be a quite “small” image. Do you think there is any linux package missing which is needed for the container to process/encode the binary data correctly?
Many thanks!
It should either work or not work, not something weird in the middle…what java is being used?
It’s OpenJDK 11
It shouldn’t matter, but we don’t technically support OpenJDK.
very good example .. helped a lot to understand things …
Thanks, I’m glad it helped!
super blog .. helped a lot …
Hello Jeff,
Thank you for showing me this process. It seems to be working, however i cannot get the blob to display.
this is how i would normally display a blob in the browser:
DBMS_LOB.createtemporary (l_blob, TRUE, DBMS_LOB.SESSION);
select CONTENT into l_blob
from SU_DOCUMENTS
where SU_DOC_ID= 14;
OWA_UTIL.mime_header (‘application/pdf’, bclose_header => FALSE);
————————————————————————
— set content length
————————————————————————
HTP.p (‘Content-length: ‘ || DBMS_LOB.getlength (l_blob));
OWA_UTIL.http_header_close;
————————————————————————
— download the file and display in browser
————————————————————————
WPG_DOCLOAD.download_file (l_blob);
————————————————————————
— release resources
————————————————————————
DBMS_LOB.freetemporary (l_blob);
however, something is going wrong and it is not displaying when I upload blobs using the api. Any suggestions?
— Cole
You only need to select the blob.. the trick is to set the service as a media resource and have the first column of your query be the mine type so your browser knows what’s coming. I have more than a few examples on here.
Hello,
Nice example. Is it possible via restfull download or open blob (.pdf, .xls) in delohi firemonkey app? GL
If that app can call out via HTTP…sure, probably
Do you have any examle?
I don’t even know what delohi firemonkey is, sorry.
Hi Jeff,
it works fine for me. Thank for the useful Information.
I have another Question : How to POST Blob and another Parameters in the Body. We want to have Blob and some Parameters in the Body. Is there a way to have Blob and another parameters in the Body? Or must we set the Others Parameters in the Headers?
Regards and Thanks
Pierre
Hi Dietmar
thank you so much to provide this “hack”
I have the same problem, and with this view we have now be able to upload document like in mod_plsql 🙂
Just another little thing, it is necessary to CREATE PUBLIC SYNONYM FOR APEX_RELEASE and a GRANT SELECT on APEX_RELEASE TO PUBLIC
Best regards
Thomas
Hi Jeff,
many people try to switch from mod_plsql to ORDS with their old owa_toolkit applications. Some might have APEX installed, some not. Some might use both at the same time, some not.
Anyhow, the parameter to make it work is apex.docTable in the defaults.xml in ORDS.
Unfortunately it does not work as expected. As soon as ORDS detects that APEX is installed in the database ( APEX_UTIL_CHECK = “select version_no from apex_release”; ), it basically ignores the setting in apex.docTable for the connection pool and uses the APEX table. But the owa_toolkit application cannot access the uploaded file from FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ because there is no apex session.
Unfortunately it doesn’t even work when apex is not installed because the check statement (( APEX_UTIL_CHECK = “select version_no from apex_release”; )) throws an error:
Jun 27, 2016 11:52:10 PM oracle.dbtools.apex.hooks.fileUpload.ApexFileLoader checkDocMethod
SCHWERWIEGEND: ORA-00942: table or view does not exist
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
A very simple “hack” for a schema that is used in a plsql gateway (owa_toolkit) only application is the following:
CREATE OR REPLACE FORCE VIEW APEX_RELEASE AS
select ” version_no,
” api_compatibility,
” patch_applied
from dual;
This way the statement does not bomb but it doesn’t return a valid APEX release number either => thus ORDS “believes” that APEX is not installed and the apex.docTable parameter is used and it works.
But this is not really a good suggestion. You might run into issues when using APEX later in this schema.
Cheers,
~Dietmar.