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 => 'https://systest.eisenvault.net/alfresco/api/-default-/public/alfresco/versions/1/nodes/2b9d7fc9-a12e-4e0b-89b2-e556ddfd2778/children',  
                                                     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.

CodePudding user response：

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 => 'https://systest.eisenvault.net/alfresco/api/-default-/public/alfresco/versions/1/nodes/2b9d7fc9-a12e-4e0b-89b2-e556ddfd2778/children',
              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;
/

CodePudding user response：

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

p_body_blob IN BLOB default empty_blob()