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
  • upload a file
  • generate LINKs in your {json} responses
  • 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)
             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;

Looking for more help on ORDS?

Try my ORDS Resource Page!

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.

10 Comments

  1. Hi Jeff,
    Thanks for the great work. I’m getting “405 Method Not Allowed” using POST.
    Everything came from your code.
    I’m running ORDS 23.1.
    Thank you.

  2. Ram Shankar Reply

    Hi Jeff,
    we are migrating our application and earlier we used mod pl/sql and now are using ORDS as the mod PLSQL not supported. we have a functionality where we need to down load a file from particular path. we are unable to do the same and getting 404 error. could you please help what need to be done in this case.
    Thanks

    • From what you’ve read in my post here, what are your questions?

      Are you trying to just call the procedure directly (the one responsible for the file – a blob in a table?) via the PL/SQL Gateway, or have you tried to build a modern REST API, like I’ve shown in this post?

      Either way, I need you to show me what you’re trying, and what happens when it doesn’t work.

  3. Raul Castro Reply

    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

    • 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.

    • Raul Castro

      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.

  4. 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

    • 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.

Reply To Ram Shankar Cancel Reply