title: How to generate digital signature with PL/SQL when upgrade Google map service from v2 to v3?
body: <pre><code>I tried to generate digital signature for Google map service
  
   Maps for Business: Generating Valid Signatures - YouTube
  
  Generating an HMAC-SHA-1 Signature Using Only PL/SQL
  
    OAuth and the PL/SQL | Data Warehouse in the Cloud
  </code></pre>
  
  <p>but I got error message from Google:</p>
  
  <p>Unable to authenticate the request. Provided 'signature' is not valid for the provided client ID. Learn more: <a href="https://developers.google.com/maps/documentation/business/webservices/auth" rel="nofollow">https://developers.google.com/maps/documentation/business/webservices/auth</a></p>
  
  <p>I think there is something wrong with my code to generate signature, as if I remove the part regarding client and signature, it will work, can anyone help me on this problem?</p>
  
  <pre><code>/*Procedure Get_Geocoding is used to get geocoding with accuracy level for V3 business account, you can find Google map digital signature descrirption from
  https://developers.google.com/maps/documentation/business/webservices/auth#digital_signatures
  if geocoding is 0,0, procedure returns false to indicate failure of get geocoding*/
  
  procedure Get_Geocoding2(P_s_Address in varchar2, P_s_Geocoding out varchar2, P_n_accuracy out number, P_b_success out boolean) is
  
  
  
    --private key for Google business account, this is provided by Google with client name.
  
    l_private_key_src varchar2(200):='xxxxxxxxxxxxxxxxxxx';
  
    l_private_key_b64_alter varchar2(200):= translate(l_private_key_src,'-_','+/');
  
    l_private_key_bin raw(2000);
  
    l_client_name varchar2(100):='gme-xxx';
  
  
  
    l_signature_mac raw(2000);
  
    l_signature_b64 varchar2(200);
  
    l_signature_b64_alter_back varchar2(200);
  
  
  
    l_Google_service_domain varchar2(200):='http://maps.googleapis.com';
  
  
  
    l_address varchar2(4000);
  
    l_url varchar2(32000);
  
    l_path varchar2(32000);
  
    l_response varchar2(32000);
  
    l_page UTL_HTTP.HTML_PIECES;
  
  
  
    n_actual_length number;
  
    json_obj json;
  
    json_tempobj json;
  
    jl_listOfValues json_list;
  
    json_geom_obj json;
  
    json_loc json;
  
  
  
    l_lat  VARCHAR2(40);
  
    l_lng  VARCHAR2(40);
  
    l_status VARCHAR2(255);
  
    json_accuracy json;
  
  
  
    --temp_string varchar2(10000);
  
  
  
    n_first_comma number;
  
    n_second_comma number;
  
    n_level_length number;
  
  
  
    BEGIN
      /* TODO implementation required */
  
  
      l_private_key_bin := utl_encode.base64_decode(UTL_I18N.string_to_raw(l_private_key_b64_alter, 'AL32UTF8'));
  
      l_address:=APEX_UTIL.URL_ENCODE(P_s_Address);
      --dbms_output.put_line(l_address);
      l_address := replace(l_address,' ','+');
      l_path := '/maps/api/geocode/json?address='||l_address||'&amp;'||'sensor=true';
      dbms_output.put_line(l_path);
  
      l_signature_mac :=DBMS_CRYPTO.mac(UTL_I18N.string_to_raw(l_path, 'AL32UTF8'), DBMS_CRYPTO.hmac_sh1,l_private_key_bin);
  
  
      l_signature_b64:= UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(l_signature_mac));
      l_signature_b64_alter_back:=translate(l_signature_b64,'+/','-_');
      dbms_output.put_line(l_signature_b64_alter_back);
  
  
      --get response from Google map service
      l_url:=l_Google_service_domain||l_path||'&amp;client='||l_client_name||'&amp;signature='||l_signature_b64_alter_back;
      --l_url:=l_Google_service_domain||l_path;
      dbms_output.put_line(l_url);
  
  
      l_page:=utl_http.request_pieces( l_url, 99999);
  
  
      for i in 1..l_page.count loop
      l_response:=l_response||l_page(i);
      end loop;
  
  
      n_actual_length:=length(l_response);
      dbms_output.put_line(n_actual_length);
      dbms_output.put_line(l_response);
  
  
      --parse JSON result
      json_obj:=new json(l_response);
  
  
      l_status := json_ext.get_string(json_obj, 'status');
  
  
      IF l_status = 'OK' then
  
  
  
  
  
      jl_listOfValues := json_list(json_obj.get('results'));
  
      json_tempobj := json(jl_listOfValues.get(1));
      json_geom_obj := json(json_tempobj.get(3));
  
      json_loc := json_ext.get_json(json_geom_obj, 'location');
  
  
      l_lat := to_char(json_ext.get_number(json_loc, 'lat'));
      l_lng := to_char(json_ext.get_number(json_loc, 'lng'));
      P_s_Geocoding:=l_lat||','||l_lng;
      dbms_output.put_line('##########'||P_s_Geocoding);
  
  
  
      case json_ext.get_string(json_geom_obj, 'location_type')
  
      when 'ROOFTOP' then P_n_accuracy:=9;
      when 'RANGE_INTERPOLATED' then P_n_accuracy:=7;
      when 'GEOMETRIC_CENTER' then P_n_accuracy:=5;
      else P_n_accuracy:=3;
  
      end case;
  
      P_b_success:=true;
  
  
  
      else
  
  
      P_b_success:=false;
      P_n_accuracy:=0;
      P_s_Geocoding:='0,0';
  
      end if;
  
  
  
  
  
  
  
    END;
  </code></pre>