I have followed the link at https://blogs.oracle.com/oraclemagazine/better-file-storage-in-oracle-cloud to upload and download image files and it is all working as expected.
However, I cannot seem to display the image from OCI object storage as a column in a Classic Report region.
In HTML Expression attribute of the column, I have the following:
<img src="#OWNER#.oci_display_image?p_lref_number=#LREF_NUMBER#&p_doc_type_code=PROIM&p_oci_web_credential=&G_OCI_WEB_CREDENTIAL." height="100" />
I have granted execute access to Public for the procedure oci_display_image and also created a public synonym.
I am using Apex 20.2 on Autonomous Transaction Processing (ATP) on the Oracle Cloud Infrastructure (OCI) and the image files are stored and accessed from Object Storage buckets on the OCI.
Below is the code for my oci_display_image procedure. The HLS_DOCUMENT table contains a reference to the documents stored on OCI. Entries into this table are done when a doc is uploaded to the OCI so I just reference it to get the url and other required info that are stored there.
Can anyone please review this and tell me what I am doing wrong. I suspect that there is a permission issue to access the procedure from public but cannot test it out as I do not know how to execute this procedure in the url of Apex that hosted on ATP OCI.
--------------------------------------------------------------------------------------------------
create or replace procedure OCI_DISPLAY_IMAGE (p_lref_number IN VARCHAR2,
p_doc_type_code IN VARCHAR2,
p_oci_web_credential IN VARCHAR2)
is
l_request_url varchar2(200);
l_doc_type_id number;
l_response blob;
l_mime_type varchar2(50);
l_oci_object_name varchar2(50);
download_failed_exception exception;
l_content_type varchar2(32767);
l_content_length varchar2(32767);
begin
select id into l_doc_type_id from HLS_USER_DEFINED_CODE where upper(kind) = 'DOCUMENT_TYPE' and upper(code) = upper(p_doc_type_code);
SELECT URL,MIME\_TYPE, oci\_object\_name into l\_request\_url, l\_mime\_type, l\_oci\_object\_name
FROM HGDCPNG.HLS\_DOCUMENT
where lref\_number = p\_lref\_number and document\_type\_id = l\_doc\_type\_id
and rownum = 1
order by created\_date desc;
l_response := apex_web_service.make_rest_request_b(
p_url => l_request_url
, p_http_method => 'GET'
, p_credential_static_id => p_oci_web_credential
);
if apex_web_service.g_status_code != 200 then
raise download_failed_exception;
end if;
select DBMS_LOB.getlength(l_response) into l_content_length from dual;
sys.htp.init;
sys.owa_util.mime_header(NVL(l_mime_type, 'application/octet'), FALSE);
sys.htp.p('Content-Disposition: inline; filename="'|| l_oci_object_name || '"' );
sys.htp.p('Cache-Control: max-age=3600'); -- if desired
sys.htp.p('Content-length: ' || l_content_length);
sys.owa_util.http_header_close;
sys.wpg_docload.download_file(l_response);
exception
when others then
raise;
end;
----------------------------------------------------------------------------------------------------