0

I am using an Oracle database, version 12.2.0.1.0, apex version 22.1.0 . Error ORA-29259 end-of-input reached is constantly displayed after trying to make a https POST request to a specific API endpoint. File is stored server-side, around 5MB in size, .csv format and was normally created by using UTL_FILE package. It seems that remote server can be reached, but for some reason request fails every time because of mentioned error. Access control list, Oracle wallet and proxy configuration are properly set up also.

After creating a BLOB from a file, I've tried to send it by using UTL_HTTP. Regarding BLOB version, I also tried 'application/octet-stream' as a 'Content-Type' header. There is also a CLOB version of code below.

v_original_file := bfilename ('CSV_DIR', v_name_and_date) ; 

   DBMS_LOB.createtemporary(v_content, TRUE);
   DBMS_LOB.fileopen(v_original_file, DBMS_LOB.lob_readonly );
   DBMS_LOB.open(v_content, DBMS_LOB.lob_readwrite );
   DBMS_LOB.loadblobfromfile(v_content, v_original_file, DBMS_LOB.lobmaxsize, v_dst_offset, v_src_offset) ;
   v_request_length := DBMS_LOB.getlength(v_content) ;

   UTL_HTTP.set_proxy('IP_address:PORT') ; 
   UTL_HTTP.set_transfer_timeout(600) ; 
   UTL_HTTP.set_wallet('path/to/wallet/certificate_name.crt') ; 
      
   v_request := UTL_HTTP.begin_request ('https://api-test.xyz.si/fd-api/v1.0/uploadcsv','POST','HTTP/1.1');
      UTL_HTTP.SET_BODY_CHARSET('UTF-8');
      UTL_HTTP.set_header (v_request, 'Authorization', 'API_key_provided');
      UTL_HTTP.set_header (v_request, 'Accept', 'application/json');
      UTL_HTTP.set_header (v_request, 'X-Client-ID', 'Identifier') ; 
      UTL_HTTP.set_header (v_request, 'X-Correlation-ID', dbms_random.string('X', 32));
      UTL_HTTP.set_header (v_request, 'Content-Length', v_request_length);
      UTL_HTTP.set_header (v_request, 'Content-Type', 'multipart/form-data');
      UTL_HTTP.set_header (v_request, 'Transfer-Encoding', 'Chunked');

      WHILE(v_offset <= v_request_length)
      LOOP
         DBMS_LOB.read(v_content, v_amount, v_offset, v_buffer);
         UTL_HTTP.write_text(v_request, v_buffer);
         v_offset := v_offset + v_amount;
      END LOOP;

   DBMS_LOB.filecloseall ; 

   v_response := UTL_HTTP.get_response(v_request);
   UTL_HTTP.SET_BODY_CHARSET('UTF-8');

   FOR i IN 1 .. UTL_HTTP.get_header_count(v_response)
   LOOP
      UTL_HTTP.get_header(v_response, i, v_name, v_value);
      DBMS_OUTPUT.put_line(v_name || ': ' || v_value);
   END LOOP;

   LOOP
      UTL_HTTP.read_line(v_response, v_value, TRUE);
      DBMS_OUTPUT.put_line(v_value);
   END LOOP;
   
EXCEPTION

   WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response (v_response);
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('Error Stack: ' || DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line ('Backtrace: ' || DBMS_UTILITY.format_error_backtrace);
      DBMS_OUTPUT.put_line ('Call Stack: ' || DBMS_UTILITY.format_call_stack);

END; 

Since it hasn't worked, I also tried apex_web_service way, with append_to_multipart function and without it.

v_clob_length := DBMS_LOB.getlength(v_clob) ;

   apex_web_service.g_request_headers(1).name := 'Authorization' ; 
   apex_web_service.g_request_headers(1).value := 'APi_key_provided' ;
   apex_web_service.g_request_headers(2).name := 'Content-Length' ; 
   apex_web_service.g_request_headers(2).value := v_clob_length ; 
   apex_web_service.g_request_headers(3).name := 'Content-Type' ; 
   apex_web_service.g_request_headers(3).value := 'multipart/form-data' ; 
   apex_web_service.g_request_headers(4).name := 'Accept' ; 
   apex_web_service.g_request_headers(4).value := 'application/json' ; 
   apex_web_service.g_request_headers(5).name := 'X-Client-ID' ; 
   apex_web_service.g_request_headers(5).value := 'Identifier' ; 
   apex_web_service.g_request_headers(6).name := 'X-Correlation-ID' ; 
   apex_web_service.g_request_headers(6).value := dbms_random.string('X', 32) ; 

    /*
     apex_web_service.append_to_multipart (
      p_multipart => v_multipart,
      p_name => 'file',
      p_content_type => 'text/csv;charset=UTF-8',
      p_body => v_clob ) ;
    */ 

   dbms_lob.close(v_clob) ;

   v_response_clob := apex_web_service.make_rest_request(
      p_url => 'https://api-test.xzy.si/fd-api/v1.0/uploadcsv', 
      p_proxy_override => 'IP_address:PORT',
      p_http_method => 'POST', 
      p_wallet_path => 'path/to/wallet/certificate_name.crt', 
      p_body => v_content (or apex_web_service.generate_request_body(v_multipart) in case of using append_to_multipart function)  ) ; 

   DBMS_OUTPUT.put_line (v_response_clob) ; 

EXCEPTION

   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('Error Stack: ' || DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line ('Backtrace: ' || DBMS_UTILITY.format_error_backtrace);
      DBMS_OUTPUT.put_line ('Call Stack: ' || DBMS_UTILITY.format_call_stack);

END; 

That's basically it, whatever I do, same error ocurrs. Since I am running out of ideas what to do, any advice would be golden. If I left something uncovered, please ask.

Dino
  • 3
  • 5
  • Internet searches suggest this could be a problem with cipher suites or protocols, in that the API you're connecting to may be restricting access to only modern ones which Oracle 12c didn't support. Depending on what the API supports you might be able to apply patches to fix the problem, if that is what's happening. – Alex Poole Apr 06 '23 at 17:30

0 Answers0