Search
  • Bheem

POST a BLOB to an Oracle Table via REST API

It seems kind of weird to do a blog post around a feature that’s fairly well documented, but this came up twice this week, so I figured I’d throw it out there.


Scenario: I want to post up a record to be inserted into a table. The record will consist of a couple of regular values, but also a file, which will be stored as a BLOB.


OK, how does this happen?


Create Table

CREATE TABLE  "DOCUMENTS1" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"FILE_NAME" VARCHAR2(100), 
	"BLOB_CONTENT" BLOB, 
	"MIME_TYPE" VARCHAR2(255), 
	"CHAR_SET" VARCHAR2(128), 
	"LAST_UPDATED" DATE
   )
/


Step 1 : Create Post API in oracle apex by navigating to below options


Step 2 : Select ORDS Based Restful Services

Step 3 : Create Module

Step 4 : Enter Module details & click Create Module Button


Step 5: Click on Create Template & enter template details.

Step 6: Create Handler & Enter below details.

Method Post, Source Type PL/SQL

Source code

DECLARE
    image_id   NUMBER;
    l_blob     BLOB := :body;
BEGIN
    IF l_blob IS NULL THEN
        :status := 'Please upload valid image'; --if blob is not attached
        :image_id := NULL;
    ELSE
        IF :id IS NULL THEN
            INSERT INTO documents1 (
                file_name,
                mime_type,
                blob_content
            ) VALUES (
                :file_name,
                :mime_type,
                l_blob
            ) RETURNING id INTO image_id;

            :status := 'Image Uploaded Successfully.';-- created
            :image_id := image_id;
            COMMIT;
        ELSE
            NULL;
        END IF;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        :status := 'Error Occured While Uploading Image. ' || sqlerrm;
        image_id := NULL;
END;

Step 7: Once handle is created then add parameter like below.

Click on Apply Changes


Steps to Call above API from postman.


Let’s start from the POST request perspective.

We’re going to send the ‘regular’ data via the header as parameters.

mime_type & file_name are columns in my database table.


The file is going to go up in the BODY of the request.

Click Send Button.


Output



192 views0 comments

Recent Posts

See All

How to create network ACL in oracle database.

when you are calling third party API in oracle using UTL_HTTP then you will face Network access denied by ACL error if ACL is not created. To create ACLfirst login with sys account then run below code