function google_geocode( p_address varchar2 ) return sdo_geometry is
 l_http_req  utl_http.req;
 l_http_resp utl_http.resp;
 l_response long;
 l_latlon long;
begin

 l_http_req := utl_http.begin_request(
   url => 'http://maps.google.com/maps/geo' ||
          '?q=' || utl_url.escape( p_address ) ||  -- address to geocode
          '&output=csv' ||                         -- simplest return type
          '&key=abcdef' );                         -- Google API site key

 l_http_resp := utl_http.get_response( l_http_req );
 utl_http.read_text( l_http_resp, l_response );
 utl_http.end_response( l_http_resp );
 l_latlon := substr( l_response, instr( l_response, ',', 1, 2 ) + 1 );

 return sdo_geometry(
          2001, 8307,
          sdo_point_type( to_number( substr( l_latlon, instr( l_latlon, ',' )+1 )),
                          to_number( substr( l_latlon, 1, instr( l_latlon, ',' )-1 )),
                          null ),
          null, null );
end google_geocode;

declare
  geometry sdo_geometry;
begin
  if :p4_address is null then
    :p4_longitude := null;
    :p4_latitude := null;
  else
    geometry := google_geocode( :p4_address );
    :p4_longitude := geometry.sdo_point.x;
    :p4_latitude := geometry.sdo_point.y;
  end if;
end;