DBA Hub

📋Steps in this guide1/7

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

oracle miscconfigurationintermediate
by OracleDba
13 views
1

Assumptions and Comments

This article assumes the following.
2

Create a Test Database User

We need a new database user for our testing.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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;
3

Create an API

We need a PL/SQL API to load and retrieve media. The procedure inserts the media into the test table and the procedures return the specified media using the classic PL/SQL web toolkit method, and the ORDS method respectively.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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;
/

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;
/
4

Enable ORDS

We need to enable ORDS for the test user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;
/
5

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. The following handler calls the 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. The following handler calls the 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. To load media we would expect a call with the following information. To retrieve an media we would expect to use a call to the following URL.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
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;
/

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;
/

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;
/

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

http://localhost:8080/ords/hr/media/files/FileName
6

Test It

To test the call we use the 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. If we attempt to load an image with the same name we get an error. The following URLs will display the specified media in a browser. If we try to display media that doesn't exist we get an error status.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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"}
$

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"}
$

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

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

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

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. The following URLs will display the specified image in a browser. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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;
/

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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!