Upload a blob using p_parm_values in PL/SQL apex_web_service.make_rest_request

I am trying to use Apex (cloud) to run a PL/SQL script that fetches a BLOB object from a table and sends it via a POST API call to an Alfresco Server.

If I were using Postman/Insomnia for the API call, I would do the following:

1. Set the Content-Type to be "multipart/form-data"
2. Add a key in the body called "filedata"
3. For its value, I would select a file from the local filesystem

This I would send as a POST request with basic authentication.

I am having trouble replicating this using PL/SQL with the APEX_WEB_SERVICE module. Below is the code that I am trying:

DECLARE f_attachment BLOB; f_response CLOB; v_login VARCHAR2(100); v_password VARCHAR2(100); l_offset INT := 1; p_clob CLOB; BEGIN v_login := '***'; v_password := '***'; SELECT PDF INTO f_attachment FROM VIPUL_TEST WHERE ID=2; apex_web_service.g_request_headers.delete; apex_web_service.g_request_headers(1).name := 'Content-Type'; apex_web_service.g_request_headers(1).value := 'multipart/form-data'; f_response := apex_web_service.make_rest_request(p_url => ' p_http_method => 'POST', p_username => v_login, p_password => v_password, p_parm_name => apex_util.string_to_table('filedata'), p_parm_value => apex_util.string_to_table(apex_web_service.blob2clobbase64(f_attachment)) ); -- Print out the CLOB fetched from Database p_clob := apex_web_service.blob2clobbase64(f_attachment); dbms_output.put_line('Print CLOB'); loop exit when l_offset > dbms_lob.getlength(p_clob); dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) ); l_offset := l_offset + 255; end loop; -- Finish Printing END; 

I am getting the below error:

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 21 ORA-06512: at "SYS.DBMS_SQL", line 1721 1. DECLARE 2. f_attachment BLOB; 3. f_response CLOB; 4. v_login VARCHAR2(100); 

It seems that the clob can't be passed as a value in p_parm_value. Can you suggest a way to make this work? Is there a way to turn a Blob or a Clob into a string? I am reluctant to convert this into a varchar type, because that has an upper limit of 32kb (right?), whereas these attachments can be multiple gigabytes in size.

2 Answers

We solved this ourselves. Below is the code that worked.

DECLARE l_clob clob; l_json clob; lb_blob blob; l_body clob; lv_filename VARCHAR2(20) := 'my_file.pdf'; lm_multipart apex_web_service.t_multipart_parts; BEGIN SELECT PDF INTO lb_blob FROM VIPUL_TEST WHERE ID = 3; apex_web_service.APPEND_TO_MULTIPART ( p_multipart => lm_multipart, p_name => 'filedata', p_filename => lv_filename, p_content_type => 'application/octet-stream', p_body_blob => lb_blob ); l_clob := apex_web_service.make_rest_request( p_url => ' p_username => '***', p_password => '***', p_http_method => 'POST', p_body_blob => apex_web_service.generate_request_body(lm_multipart)); dbms_output.put_line(to_char(l_clob)); END; / 

You should try send it via following parameter of APEX_WEB_SERVICE.MAKE_REST_REQUEST function:

p_body_blob IN BLOB default empty_blob()