Develop ORDS API for read and write file using SQLDeveloper

For a customer I had to design and developer a bunch of Database API’s using ORDS. I faced may challenges, because of the specific requirements and the large documentation of ORDS with less detailed explanations.

One of the challenges is uploading a file to a BLOB and reading the file from the frontend.

I will try to explain in details how to develop a REST API in ORDS using SQLDeveloper. Why from SQLDeveloper? this because it is visual and it helps the Oracle classic developers to understand services and also to be able to develop it easily.

First of all download the latest version of SQLDeveloper.

• Create a table with a BLOB content

CREATE TABLE “REST_BLOB”(
“ID” NUMBER,
“FILECONTENT” BLOB,
“FILENAME” varchar2(225),
“MIME_TYPE” VARCHAR2(225)
);

– Connect to the schema and right click > REST Services > Enable REST Services..

On the next page you click FINISH.

Check the schema, you have REST Data Services component.

• Expand it and see the content

Module: you create folders

Privileges: define privileges to provide access to your API

Roles: define roles to leve the API access.

Right click Modules to create the Module give a name FileUploadDownload

Check the content of Modules

Right click the URI Pattern and add Handler use POST

• Add query

__—__—__—__—__—

DECLARE
l_blob_file   BLOB;
BEGIN
 l_blob_file := pkg_utl_base64.decode_base64(:data);
INSERT INTO rest_blob ( id, filecontent, filename, mime_type ) 
     VALUES (:id, l_blob_file, :filename, :mime_type );

:r_result := 'success';

EXCEPTION 
WHEN OTHERS THEN
:r_result := 'file upload failure';
:r_message := sqlerrm;

END;

 

• Add parameters

Test you API.

The URL is : http://localhost:8080/ords/lab/files/api/UploadFile

Use an attached file as JSON body.

it should look like

{
"id" : 2,
"filename": "myzip.zip",
"mime_type": "application/zip",
"data": "UEsDBBQAAAAIAIeaektzTjVlAE4BAGhbAQAKABwAbG9nby0yLnBuZ1VUCQAD3gUbWo5lHVp1eAsAAQT1AQAABBQAAAC8vFN0ZWG0JRzbqdi2bdtJ5cS2bVsV267Ytm3bScW2u+r26Hu7+/b/+j......etc...
}

It should be a BASE64 coded FILE. ( I downloaded a PLSQL package to encode and decode the file).

The response should be:

{
"result": "success"
}

And A file should be uploaded into the database.

NOW we gonna create API to read the uploaded file

Create second Template and Add a GET handler

Use the below query and set parameter

-_-_–_-_–_-_–_-_–_-_–_-_-

Begin

open :files for
    select filename, 
      mime_type,
      pkg_utl_base64.encode_base64(filecontent) data
  from rest_blob where id =:fileid;

r_result := 'success';

EXCEPTION
  WHEN OTHERS THEN
:r_result := 'failure' ;
:r_message := sqlerrm;
end;

-_-_–_-_–_-_–_-_–_-_–_-_-

Now test the API by calling this URL in POSTMAN

http://localhost:8080/ords/lab/files/api/DownloadFile?fileid=1

The response should look like below

Now you are done.. by reading this blog and doing it by example, you are ORDS API specialist 🙂

 

 

BTW. if you receive error “Error 500 – Internal Server Error.”, check the log of ORDS because the query should have errors that’s why you have this error.