- OCI account
- OCI user which has read/write access to the Bucket
- On that User create an API key
- Download the private key and click on “Add“
- Keep OCI window open
- Open APEX screen
- Go to Workspace Utility
- find web credentials
- create new web credentials
give it a name like OCI_AUTH
Authentication Type: Oracle Cloud Infrastructure
OCI UserID : the OCID from your user
OCI Private key: which is the one just download, open it with text editor so u can copy and paste it here
OCI tenancy ID: this should be found on the screen from OCi console.
OCI Public Key FingerFrint: this should be found on the screen from OCi console. - Apply Changes..
To Upload/Replace
if versioning is NOT enabled , it will replace file with the same name
Issues with HTTPs calls (Wallet issue) ?
declare
l_blob blob;
l_file_name varchar2(10) := 'test.mp3';
l_response clob;
cursor c_audio is
select t.file_content
from audios_ldff t
where t.ldff_id = 3;
begin
open c_audio;
fetch c_audio
into l_blob;
close c_audio;
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'audio/mp3';
l_response := apex_web_service.make_rest_request(p_url => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frpnibrn7ulj/b/public/o/' ||
l_file_name
,p_http_method => 'PUT'
,p_body_blob => l_blob
,p_credential_static_id => 'OCI_AUTH');
if apex_web_service.g_status_code != 200
then
dbms_output.put_line('failed with code ' || apex_web_service.g_status_code);
else
dbms_output.put_line('success uploaded');
end if;
end;To list objects
declare
l_response clob;
begin
l_response := apex_web_service.make_rest_request(p_url => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frpnibrn7ulj/b/public/o/'
,p_http_method => 'GET'
,p_credential_static_id => 'OCI_AUTH');
if apex_web_service.g_status_code != 200
then
dbms_output.put_line('failed with code ' || apex_web_service.g_status_code);
else
dbms_output.put_line(l_response);
end if;
end;
Results are JSON string
{"objects":[{"name":"test.mp3"},{"name":"transform_van_schiphol.mp3"}]}To delete
for delete if successful , a 204 is returned.
declare
l_file_name varchar2(10) := 'test.mp3';
l_response clob;
begin
l_response := apex_web_service.make_rest_request(p_url => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frpnibrn7ulj/b/public/o/' ||
l_file_name
,p_http_method => 'DELETE'
,p_credential_static_id => 'OCI_AUTH');
if apex_web_service.g_status_code != 204
then
dbms_output.put_line('failed with code ' || apex_web_service.g_status_code);
else
dbms_output.put_line('success deleted' || l_response);
end if;
end;
To get file as blob
declare
l_file_name varchar2(10) := 'test.mp3';
l_response blob;
begin
l_response := apex_web_service.make_rest_request_b(p_url => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frpnibrn7ulj/b/public/o/' ||
l_file_name
,p_http_method => 'GET'
,p_credential_static_id => 'OCI_AUTH');
dbms_output.put_line(apex_web_service.g_status_code || ' ' || round(dbms_lob.getlength(l_response) / 1024 / 1024) ||
' Mb');
end;Outcome : 200 41 Mb
How to Create Pre-Authenticated Request
For Buckets in Private model, access to the objects are secured. One can choose to create Pre-authenticated request for objects in the bucket. Here is how.
Offical Docs about the POST Request
The code below, does a call to OCI with JSON in the request body. Once successful, a json is returned with the URL you can call.
declare
l_json_payload clob;
l_response clob;
l_access_url clob;
cursor c_json(cp_json in clob) is
select accessuri
,timecreated
,timeexpires
from json_table(cp_json
,'$' columns(accessuri path '$."accessUri"'
,timecreated path '$."timeCreated"'
,timeexpires path '$."timeExpires"'));
r_json c_json%rowtype;
l_base_url constant varchar2(100) := 'https://objectstorage.eu-frankfurt-1.oraclecloud.com';
l_bucket_namespace constant varchar2(100) := '/n/frpnibrn7ulj/b/public';
l_json_obj json_object_t;
begin
apex_json.initialize_clob_output(p_preserve => true);
apex_json.open_object;
apex_json.write('accessType'
,'ObjectReadWrite'); /*[ObjectRead,ObjectWrite,ObjectReadWrite,AnyObjectWrite,AnyObjectRead,AnyObjectReadWrite]*/
--apex_json.write('bucketListingAction','Deny'); -- not required - default on Deny
apex_json.write('name'
,'myRequest' || to_char(systimestamp at time zone 'UTC'
,'yyyyddmmhh24miss')); -- required, but arbitrary. must be unqiue
apex_json.write('objectName'
,'test.mp3'); -- required if access type is ObjectRead, Duhhhhh..
apex_json.write('timeExpires'
,to_char(systimestamp at time zone 'UTC' + numtodsinterval(6
,'HOUR')
,'yyyy-mm-dd"T"hh24:mi:ss"Z"')); -- required, why UTC, because OCI works UTC. What is T and Z, T is just a sperator where Z is UTC 0 ( PAR expiration must conform to RFC 3339: 2023-05-2000:42:59)
apex_json.close_object;
l_json_payload := apex_json.get_clob_output;
/*POST /n/{namespaceName}/b/{bucketName}/p/*/
l_response := apex_web_service.make_rest_request(p_url => l_base_url || l_bucket_namespace || '/p/'
,p_http_method => 'POST'
,p_body => l_json_payload
,p_credential_static_id => 'OCI_AUTH');
dbms_output.put_line(l_response);
/*
l_json_obj := json_object_t.parse(l_response);
dbms_output.put_line('URL: ' || l_base_url || l_json_obj.get_string('accessUri'));
*/
if apex_web_service.g_status_code = 200
then
open c_json(l_response);
fetch c_json
into r_json;
close c_json;
dbms_output.put_line('Full Url: ' || l_base_url || r_json.accessuri || chr(10) || ' created at: ' ||
r_json.timecreated || chr(10) || ' expires at: ' || r_json.timeexpires);
end if;
end;