Below the step for load a file binary into a blob field in a oracle table.
- STEP - 1 : Enable the schema oracle for using a REST ORDS.
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => 'TEST',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'test',
p_auto_rest_auth => FALSE);
commit;
END;
- STEP -2 : Create a table
CREATE TABLE "TEST"."MEDIA_BLOB"
( "ID" NUMBER
GENERATED BY DEFAULT
AS IDENTITY MINVALUE 1
MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1
BOCACHE ORDER
NOCYCLE NOKEEP
NOSCALE NOT NULL ENABLE,
"TITLE" VARCHAR2(100 BYTE),
"CONTENT_TYPE" VARCHAR2(100 BYTE),
"FILE_NAME" VARCHAR2(100 BYTE),
"CONTENT" BLOB
)
- STEP - 3: Create Module REST
-- Generated by Oracle SQL Developer REST Data Services 19.2.1.247.2212
-- Exported REST Definitions from ORDS Schema Version 19.2.0.r1991647
-- Schema: TEST Date: Sun Nov 29 18:22:54 IST 2020
--
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'TEST',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'test',
p_auto_rest_auth => FALSE);
ORDS.DEFINE_MODULE(
p_module_name => 'loadblob',
p_base_path => '/blob/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'loadblob',
p_pattern => 'file',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'loadblob',
p_pattern => 'file',
p_method => 'POST',
p_source_type => 'plsql/block',
p_items_per_page => 0,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'declare
image_id integer;
begin
insert into media_blob (title,content_type,content)
values (:title,:content_type,:body)
returning id into image_id;
:status_code := 201;
:location := image_id;
end;'
);
ORDS.DEFINE_PARAMETER(
p_module_name => 'loadblob',
p_pattern => 'file',
p_method => 'POST',
p_name => 'location',
p_bind_variable_name => 'location',
p_source_type => 'RESPONSE',
p_param_type => 'INT',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'loadblob',
p_pattern => 'file',
p_method => 'POST',
p_name => 'title',
p_bind_variable_name => 'title',
p_source_type => 'HEADER',
p_param_type => 'STRING',
p_access_method => 'IN',
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'loadblob',
p_pattern => 'file',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 25,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'select * from media_blob'
);
COMMIT;
END;
- STEP - 4 : Insert file into table using a curl
curl -s -o response.txt -w "%{http_code}"
--location
--request POST 'http://localhost:9999/ords/test/blob/file'
--header 'title: test'
--header 'Content-Type: image/gif'
--data-binary '@/home/oracle/export.xlsx'
- STEP - 5 : In the table
