Custom connection to Xero using Oracle PL/SQL

Question

I am having a problem with Xero API when the API establishes the connection, in the first attempt to establish the connection, it returns an error of Unexpected character "<" while in the very 2nd attempt to establish a connection gets successful.

The Code I am using is in the form of a Function as below:

function base64encode(t in varchar2) return varchar2
  is
  begin
    return translate(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t))), 'd'||CHR(10)||CHR(13), 'd');
  end base64encode;

  function get_access_token return varchar2
  is
    
    g_client          varchar2(200);
    v_secret          varchar2(200);
    l_result          clob;
    token_not_found   exception;
    l_token           clob;
    
    
  begin
    
    g_client := mis_system_parameters.get_string_value('XERC');
    
    v_secret := mis_system_parameters.get_string_value('XERS');
 
    g_xero_revenue_account_code := mis_system_parameters.get_string_value('XENC');
    
    while l_result is null or l_result like '%<%' loop

      apex_web_service.g_request_headers.delete();
      apex_web_service.g_request_headers(1).name  := 'authorization';
      apex_web_service.g_request_headers(1).value := 'Basic '|| base64encode(g_client||':'||v_secret);
      apex_web_service.g_request_headers(2).name  := 'Content-Type';
      apex_web_service.g_request_headers(2).value := 'application/x-www-form-urlencoded';
    
      l_result := apex_web_service.make_rest_request(
        p_url            => 'http://localhost/identity-xero-api-proxy/connect/token'
       ,p_http_method    => 'POST'
       ,p_body           => 'grant_type=client_credentials&scope=accounting.transactions accounting.contacts assets files accounting.settings'
       );
    
    end loop;
    
    apex_json.parse(l_result);

    l_token := apex_json.get_varchar2('access_token');
    
    if apex_json.get_varchar2('access_token') is null then
      raise token_not_found;
    end if;

    return l_token;
  exception
    when token_not_found then
      mis_errors.ins_err
       ('mis_xero_customer_api.get_access_token','Token Not Found');
    when others then
      mis_errors.ins_err
       ('mis_xero_customer_api.get_access_token',l_result||' - '||SQLERRM);
      raise;
  end get_access_token;

Any kind of advise and help will be highly appreciated.

Thanks

Custom connection to Xero using Oracle PL/SQL

0 Answers0