구글Map 주소로 좌표값얻기 PL/SQL

네이버는 POST방식도 허용 하던데 구글은 POST로 넘기면 오류메세지에 관련된 XML만 넘어오더군요

그래서 그냥 GET방식으로 처리하니 정상적인 XML값이 넘어오네요..ㅋㅋ

Java로 작업하고 나서 테스트 해볼려고 함 만들었네요..~~

vKey는 구글에서 생성하고 적용하십시요

==> 본인의 글 입니다. 이 글을 다른곳에 게재하는 경우 본문의 출처를 밝혀주시기 바람니다.

/**
  * 구글맵 키 생성 URL
  * http://code.google.com/intl/ko-KR/apis/maps/signup.html
  *
 **/

DECLARE
	vRequest		UTL_HTTP.req;
	vResponse		UTL_HTTP.resp;
	vResponseText	VARCHAR2(4000);
	vErrorText		VARCHAR2(4000);
	vTxt			VARCHAR2(10);
	vAddress		VARCHAR2(1000);
	vCoordinateX	VARCHAR2(100);
	vCoordinateY	VARCHAR2(100);
	vCoordinates	VARCHAR2(100);
	vXml			XMLType;
	i				BINARY_INTEGER	:= 0;
	
	--구글관련 설정내용
	vUrl			VARCHAR2(200) := 'http://maps.google.com/maps/geo';
	vKey			VARCHAR2(300) := 'ABQIAAAApvInj0M12LgrKqPVykdiShTRzhAzSQ3u4E7LYXha2CwyHvWJ_RTAzTyv3xCX_ja0mBuaI1HJQ9QLgQ';
	vQuery			VARCHAR2(200) := '서울구로구구로동222-12';
	vXmlns			VARCHAR2(100) := 'xmlns="http://earth.google.com/kml/2.0';

	--SPLIT 함수호출
	CURSOR STR_SPLIT(str IN VARCHAR2, sp IN VARCHAR2)
	IS
		SELECT COLUMN_VALUE FROM TABLE(SPLIT(str, sp));
		TYPE TABLE_SPLIT IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
	
	aCoordinates	TABLE_SPLIT;

BEGIN

	--vQuery    := convert(vQuery, 'KO16MSWIN949', 'UTF8');
	vUrl      := vUrl || '?q=' || vQuery || CHR(38) || 'gl=KR' || CHR(38) || 'output=xml' || CHR(38) || 'key=' || vKey;

	DBMS_OUTPUT.put_line('===================================================================');
	DBMS_OUTPUT.put_line('                         전송한 URL                                ');
	DBMS_OUTPUT.put_line('===================================================================');
	DBMS_OUTPUT.put_line(vUrl);
	
	vRequest := UTL_HTTP.begin_request(vUrl,  'GET', 'HTTP/1.1');
	UTL_HTTP.set_header(vRequest, 'Accept-Language',  'ko');
	UTL_HTTP.set_header(vRequest, 'Accept',           'text/xml');
	UTL_HTTP.set_header(vRequest, 'User-Agent',       'Mozilla/4.0');
	UTL_HTTP.set_body_charset('utf8');

	vResponse := UTL_HTTP.get_response(vRequest);

	IF vResponse.status_code = '200'
	THEN
		UTL_HTTP.read_text(vResponse, vResponseText);

		IF vResponseText NOT LIKE 'Result=OK%'
		THEN
			vXml     := XMLType.createXML(TRIM(vResponseText));
			vAddress := NULL;
			i        := 0;

			SELECT vXml.extract('//Response/Placemark/address/text()',           vXmlns).getstringval() INTO vAddress     FROM dual;
			SELECT vXml.extract('//Response/Placemark/Point/coordinates/text()', vXmlns).getstringval() INTO vCoordinates FROM dual;

			IF vAddress IS NOT NULL
			THEN
				FOR COLNAME_LIST IN STR_SPLIT(vCoordinates, ',')
				LOOP
					i := i + 1;
					aCoordinates(i) := COLNAME_LIST.COLUMN_VALUE;
				END LOOP;
				
				IF i > 1 THEN
					vCoordinateX := aCoordinates(1);
					vCoordinateY := aCoordinates(2);
					DBMS_OUTPUT.put_line(CHR(13) || CHR(13));
					DBMS_OUTPUT.put_line('===================================================================');
					DBMS_OUTPUT.put_line('                         주소/좌표 추출                            ');
					DBMS_OUTPUT.put_line('===================================================================');
					DBMS_OUTPUT.put_line('주소   >> ' || vAddress);
					DBMS_OUTPUT.put_line('X 좌표 >> ' || vCoordinateX);
					DBMS_OUTPUT.put_line('Y 좌표 >> ' || vCoordinateY);
				END IF;

			END IF; -- IF vAddress IS NOT NULL End.

			vErrorText := vResponseText;

		END IF; -- IF vResponseText NOT LIKE 'Result=OK%' End.

	ELSE
		vErrorText := 'HTTP status: '||vResponse.status_code||'-'||vResponse.reason_phrase;
	END IF; -- IF vResponse.status_code = '200' End.

	DBMS_OUTPUT.put_line(CHR(13) || CHR(13));
	DBMS_OUTPUT.put_line('===================================================================');
	DBMS_OUTPUT.put_line('                         전송받은 XML                              ');
	DBMS_OUTPUT.put_line('===================================================================');
	DBMS_OUTPUT.put_line(vErrorText);


	UTL_HTTP.end_response(vResponse);
END;

google_map.zip