8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : RESTful Web Services Handling Media (Documents and Images)

This article demonstrates how to create RESTful web services that handle media, such as documents and images, using Oracle REST Data Services (ORDS).

Related articles.

Assumptions and Comments

This article assumes the following.

Create a Test Database User

We need a new database user for our testing.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

-- DROP USER test_user CASCADE;

CREATE USER test_user IDENTIFIED BY test_user
  QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO test_user;


CONN test_user/test_user@pdb1

-- DROP SEQUENCE media_seq;
-- DROP TABLE media PURGE;

CREATE TABLE media (
  id             NUMBER(10)     NOT NULL,
  content_type   VARCHAR2(100)  NOT NULL,
  file_name      VARCHAR2(100)  NOT NULL,
  content        BLOB           NOT NULL
);

ALTER TABLE media ADD (
  CONSTRAINT media_pk PRIMARY KEY (id)
);

ALTER TABLE media ADD (
  CONSTRAINT media_uk UNIQUE (file_name)
);

CREATE SEQUENCE media_seq;

Create an API

We need a PL/SQL API to load and retrieve media.

CREATE OR REPLACE PACKAGE media_api AS

  PROCEDURE load (p_file_name     IN  media.file_name%TYPE,
                  p_content_type  IN  media.content_type%TYPE,
                  p_content       IN  media.content%TYPE);
  
  PROCEDURE retrieve (p_file_name  IN  media.file_name%TYPE);

END;
/

The LOAD procedure inserts the media into the test table and the RETRIEVE procedure returns the specified media using the classic PL/SQL web toolkit method.

CREATE OR REPLACE PACKAGE BODY media_api AS

  PROCEDURE load (p_file_name     IN  media.file_name%TYPE,
                  p_content_type  IN  media.content_type%TYPE,
                  p_content       IN  media.content%TYPE) IS
  BEGIN
    INSERT INTO media (id, content_type, file_name, content)
    VALUES (media_seq.NEXTVAL, p_content_type, p_file_name, p_content);
    COMMIT;
  END;

  
  PROCEDURE retrieve (p_file_name  IN  media.file_name%TYPE) IS
    l_rec  media%ROWTYPE;
  BEGIN
    SELECT *
    INTO   l_rec
    FROM   media 
    WHERE  file_name = p_file_name;
  
    OWA_UTIL.mime_header(l_rec.content_type, FALSE);
    HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_rec.content));
    HTP.p('Content-Disposition: filename="' || l_rec.file_name || '"');
    OWA_UTIL.http_header_close;
  
    WPG_DOCLOAD.download_file(l_rec.content);
  END;

END;
/

Enable ORDS

We need to enable ORDS for the test user.

CONN test_user/test_user@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'TEST_USER',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'test_user',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Create a Web Service

The following module contains templates to load and retrieve media by calling the respective procedures in the API. It could be done in a single block, but we will split it up to make it a little clearer.

Create a new module to hold multiple templates to test the functionality.

BEGIN
  ORDS.delete_module(p_module_name => 'media_module');

  ORDS.define_module(
    p_module_name    => 'media_module',
    p_base_path      => 'media_module/',
    p_items_per_page => 0);

  COMMIT;
END;
/

The following POST handler calls the LOAD procedure in the API. The file name is specified by a custom request header parameter. The content type and document contents are specified by ORDS-specific bind variables. The call status and a custom output message are defined as output parameters.

BEGIN
  ORDS.define_template(
   p_module_name     => 'media_module',
   p_pattern         => 'media/');

  ORDS.define_handler(
    p_module_name    => 'media_module',
    p_pattern        => 'media/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[BEGIN
                             media_api.load(
                               p_file_name    => :filename,
                               p_content_type => :content_type,
                               p_content      => :body
                             );
                             :status := 201;
                             :message := 'Created ' || :filename;
                           EXCEPTION
                             WHEN OTHERS THEN
                               :status := 400;
                               :message := SQLERRM;                          
                           END;]',
    p_items_per_page => 0);

  ORDS.define_parameter(
    p_module_name        => 'media_module',
    p_pattern            => 'media/',
    p_method             => 'POST',
    p_name               => 'filename',
    p_bind_variable_name => 'filename',
    p_source_type        => 'HEADER',
    p_param_type         => 'STRING',
    p_access_method      => 'IN'
  );

  ORDS.define_parameter(
    p_module_name        => 'media_module',
    p_pattern            => 'media/',
    p_method             => 'POST',
    p_name               => 'X-APEX-STATUS-CODE',
    p_bind_variable_name => 'status',
    p_source_type        => 'HEADER',
    p_access_method      => 'OUT'
  );

  ORDS.define_parameter(
    p_module_name        => 'media_module',
    p_pattern            => 'media/',
    p_method             => 'POST',
    p_name               => 'message',
    p_bind_variable_name => 'message',
    p_source_type        => 'RESPONSE',
    p_access_method      => 'OUT'
  );

  COMMIT;
END;
/

The following GET handler calls the RETRIEVE procedure in the API. The file name is identified by the bind variable defined in the template. The call status and a custom output message are defined as output parameters, for use in the event of a failure.

BEGIN
  ORDS.define_template(
   p_module_name    => 'media_module',
   p_pattern        => 'media/:filename');

  ORDS.define_handler(
    p_module_name    => 'media_module',
    p_pattern        => 'media/:filename',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[BEGIN
                             media_api.retrieve (p_file_name  => :filename);
                           EXCEPTION
                             WHEN OTHERS THEN
                               :status := 404;
                               :message := SQLERRM;                          
                           END;]',
    p_items_per_page => 0);

  ORDS.define_parameter(
    p_module_name        => 'media_module',
    p_pattern            => 'media/:filename',
    p_method             => 'GET',
    p_name               => 'X-APEX-STATUS-CODE',
    p_bind_variable_name => 'status',
    p_source_type        => 'HEADER',
    p_access_method      => 'OUT'
  );

  ORDS.define_parameter(
    p_module_name        => 'media_module',
    p_pattern            => 'media/:filename',
    p_method             => 'GET',
    p_name               => 'message',
    p_bind_variable_name => 'message',
    p_source_type        => 'RESPONSE',
    p_access_method      => 'OUT'
  );

  COMMIT;
END;
/

To load media we would expect a call with the following information.

URL        : http://localhost:8080/ords/test_user/media_module/media/
Method     : POST
Header     : Content-Type: ContentType
             filename: FileName
Raw Payload: MediaFile

To retrieve an media we would expect to use a call to the following URL.

http://localhost:8080/ords/test_user/media_module/media/FileName

Test It

To test the POST call we use the curl utility, passing local files on my PC as the media to load. In this case we load two separate images, each with a different mime type.

CONTENT_TYPE=image/jpg
FILE_NAME=virtualbox.jpg

curl -X POST --data-binary @./${FILE_NAME} \
  -H "Content-Type: ${CONTENT_TYPE}" \
  -H "filename: ${FILE_NAME}" \
  http://localhost:8080/ords/test_user/media_module/media/
{"message":"Created virtualbox.jpg"}
$


CONTENT_TYPE=image/png
FILE_NAME=json-in-the-database.png

curl -X POST --data-binary @./${FILE_NAME} \
  -H "Content-Type: ${CONTENT_TYPE}" \
  -H "filename: ${FILE_NAME}" \
  http://localhost:8080/ords/test_user/media_module/media/
{"message":"Created json-in-the-database.png"}
$

If we attempt to load an image with the same name we get an error.

CONTENT_TYPE=image/png
FILE_NAME=json-in-the-database.png

curl -X POST --data-binary @./${FILE_NAME} \
  -H "Content-Type: ${CONTENT_TYPE}" \
  -H "filename: ${FILE_NAME}" \
  http://localhost:8080/ords/test_user/media_module/media/
{"message":"ORA-00001: unique constraint (TEST_USER.MEDIA_UK) violated"}
$

The following URLs will display the specified media in a browser.

http://localhost:8080/ords/test_user/media_module/media/virtualbox.jpg

http://localhost:8080/ords/test_user/media_module/media/json-in-the-database.png

If we try to display media that doesn't exist we get an error status.

curl http://localhost:8080/ords/test_user/media_module/media/missing-image.jpg
{"message":"ORA-01403: no data found"}
$

ORDS Media Source Type

We don't have to manually code the retrieval of media from the database. Instead we can make use of the ORDS media source type. To use this you must specify a query returning the content type and a binary value. ORDS does everything else. The following example adds a new template to the previous module.

BEGIN
  ORDS.define_template(
   p_module_name    => 'media_module',
   p_pattern        => 'media2/:filename');

  ORDS.define_handler(
    p_module_name    => 'media_module',
    p_pattern        => 'media2/:filename',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_media, -- 'resource/lob'
    p_source         => 'SELECT content_type, content FROM media WHERE file_name = :filename'
  );

  COMMIT;
END;
/

The following URLs will display the specified image in a browser.

http://localhost:8080/ords/test_user/media_module/media2/virtualbox.jpg

http://localhost:8080/ords/test_user/media_module/media2/json-in-the-database.png

For more information see:

Hope this helps. Regards Tim...

Back to the Top.