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

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

Assumptions and Comments
Create a Test Database User
Create an API
Enable ORDS
Create a Web Service
Test It
ORDS Media Source Type

Assumptions and Comments

This article assumes the following.

You already have a functioning installation of ORDS, using an application server or standalone mode.
The paths for the ORDS configuration match those from the ORDS installation article listed above.
You have an Oracle database available. In this article I will be using a 12cR1 database, but it works just the same with an 11g database unless otherwise stated.
You have a way to call the web services. I used "curl" for POST calls and a browser for GET calls.
You are already familiar with creating basic RESTful web services using PL/SQL.

Create a Test Database User

We need a new database user for our testing.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

-- DROP USER testuser1 CASCADE;

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


CONN testuser1/testuser1@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 upload (p_file_name     IN  media.file_name%TYPE,
                    p_content_type  IN  media.content_type%TYPE,
                    p_content       IN  media.content%TYPE);
  
  PROCEDURE download (p_file_name  IN  media.file_name%TYPE);

END;
/

The UPLOAD procedure inserts the media into the test table and the DOWNLOAD procedures return the specified media using the classic PL/SQL web toolkit method, and the ORDS method respectively.

CREATE OR REPLACE PACKAGE BODY media_api AS

  PROCEDURE upload (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 download (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 testuser1/testuser1@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'TESTUSER1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'hr',
    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');

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

  COMMIT;
END;
/

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

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

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

  ORDS.define_parameter(
    p_module_name        => 'media',
    p_pattern            => 'files/',
    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',
    p_pattern            => 'files/',
    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 DOWNLOAD procedure in the API. The file name is identified by the bind variable defined in the template. The custom output message is defined as an output parameter, for use in the event of a failure.

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

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

  ORDS.define_parameter(
    p_module_name        => 'media',
    p_pattern            => 'files/: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/hr/media/files/
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/hr/media/files/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/hr/media/files/
{"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/hr/media/files/
{"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/hr/media/files/
{"message":"ORA-00001: unique constraint (TESTUSER1.MEDIA_UK) violated"}
$

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

http://localhost:8080/ords/hr/media/files/virtualbox.jpg

http://localhost:8080/ords/hr/media/files/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/hr/media/files/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',
    p_pattern        => 'files2/:filename');

  ORDS.define_handler(
    p_module_name    => 'media',
    p_pattern        => 'files2/: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/hr/media/files2/virtualbox.jpg

http://localhost:8080/ords/hr/media/files2/json-in-the-database.png

For more information see:

Hope this helps. Regards Tim...

Back to the Top.