Building a Web Service for Uploading and Downloading Files: The Video!

thatjeffsmith ORDS 6 Comments

Tell Others About This Story:

This video is a bit longer than most, but I’ll show you how to deploy a web service to:

  • get a list of files from a table – stored as BLOBs
  • get individual file details
  • download/render the file using the mime type
  • how to upload a file
  • how to generate LINKs in your {json} responses
  • how to set the HTTP Status Codes for your responses

The Video

Skip the Intro, Go Straight to the Demo.

The Slides

The Code

Here’s the table:

 
  CREATE TABLE "HR"."MEDIA" 
   (	"ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"FILE_NAME" VARCHAR2(256 BYTE) NOT NULL ENABLE, 
	"CONTENT_TYPE" VARCHAR2(256 BYTE) NOT NULL ENABLE, 
	"CONTENT" BLOB NOT NULL ENABLE, 
	 CONSTRAINT "MEDIA_PK" PRIMARY KEY ("ID");

If you’re on a version of Oracle older than 12c, you’ll need to create a sequence/trigger, or you’ll need to add the ID’s yourself in the POST Handler/Inserts.

Here’s the REST Module (you’ll need to run this in a REST Enabled Schema):

-- Generated by Oracle SQL Developer REST Data Services 18.3.0.276.0148
-- Exported REST Definitions from ORDS Schema Version 18.3.0.r2701456
-- Schema: HR   Date: Thu Nov 08 11:20:45 EST 2018
--
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/',
      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         => '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_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_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_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_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select CONTENT_TYPE,
       CONTENT
  from MEDIA
 where ID = :id'
      );
 
 
  COMMIT; 
END;

Related Posts

Tell Others About This Story:

Comments 6

  1. This is awesome!

    How can I make the service to wait untill the “contents” are fully loaded in the body?
    On a rest client I can upload a 1gb file and works great;yet on APEX the file is sent/uploaded but it has not finnished to be availble to be sent. After send via the web service the file seems to be corrupted/incomplete/truncated, yet I see the record in the table. Again, if i use a rest client works great.

    Thanks

    1. thatjeffsmith Post
      Author

      The file seems to be…corrupted?

      After you get the 201 from ORDS with the link, all should be ready to go.

      I’m not sure if what you’re seeing is an ORDS or an APEX issue, but it sounds like APEX at this point.

    2. It is an apex, the service works as ment to be on a rest client.

      On apex a big file has not yet been fully loaded when the request and body sent, like if the body is not done yet and the request goes.

      Thanks, I´ll see what to do on the apex side.

  2. Thanks Jeff.
    You say “If you’re on a version of Oracle older than 12c, you’ll need to create a sequence/trigger, or you’ll need to add the ID’s yourself in the POST Handler/Inserts.” Does that mean Oracle 11g also supports ORDS?
    Thanks

    1. thatjeffsmith Post
      Author

      11gR2 (11.2.0.4), yes!

      Most of our customers are still there, in terms of having at least 1 db on that version, but 12c adoption is very high and 11gR2 falls out of normal support pricing tier next month. So you should plan on upgrading SOON.

Leave a Reply

Your email address will not be published. Required fields are marked *