Oracle UTP_HTTP를 이용한 API 호출

728x90

◎ 개요

- Oracle 내부 패키지인 UTL_HTTP를 이용하여 웹 서버에 대한 호출을 생성하고 데이터베이스로 결과를 반환할 수 있다.

● Oracle UTL_HTTP 패키지 설정 및 테스트

1. Oracle 11g부터 패키지에 대한 사용 권한에 대해서 설정이 필요

- 참고 문서 : DBMS_NETWORK_ACL_ADMIN (oracle.com)

-- 권한 부여

begin

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

acl => 'network_services.xml',

description => 'NETWORK ACL',

principal => 'PUBLIC',

is_grant => true,

privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(

acl => 'network_services.xml',

principal => 'PUBLIC',

is_grant => true,

privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(

acl => 'network_services.xml',

host => '*');

commit;

end;

-- 권한 확인

select * from DBA_NETWORK_ACLS;

select * from DBA_NETWORK_ACL_PRIVILEGES ;

2. UTL_HTTP를 이용한 API 호출 및 결과값(XML, JSON) 파싱

※ 사용된 API는 당일 예보 API 이므로 해당일 일자만 호출 가능

- 결과 값 XML 호출 및 파싱

-- XML 방식(세로)

WITH TBL_CATE AS (

SELECT 'T1H' AS CODE, '기온' AS NAME FROM DUAL

UNION

SELECT 'RN1' AS CODE, '1시간 강수량' AS NAME FROM DUAL

UNION

SELECT 'UUU' AS CODE, '동서바람성분' AS NAME FROM DUAL

UNION

SELECT 'VVV' AS CODE, '남북바람성분' AS NAME FROM DUAL

UNION

SELECT 'REH' AS CODE, '습도' AS NAME FROM DUAL

UNION

SELECT 'PTY' AS CODE, '강수형태' AS NAME FROM DUAL

UNION

SELECT 'VEC' AS CODE, '풍향' AS NAME FROM DUAL

UNION

SELECT 'WSD' AS CODE, '풍속' AS NAME FROM DUAL

), RST AS (

SELECT DBMS_LOB.SUBSTR(REPLACE(REPLACE(EXTRACT(VALUE(B),'/item/baseDate').getClobVal(),'',''),'',''),4000,1) AS DATE_YYYYMMDD,

DBMS_LOB.SUBSTR(REPLACE(REPLACE(EXTRACT(VALUE(B),'/item/baseTime').getClobVal(),'',''),'',''),4000,1) AS TIME_HHMM,

DBMS_LOB.SUBSTR(REPLACE(REPLACE(EXTRACT(VALUE(B),'/item/nx').getClobVal(),'',''),'',''),4000,1) AS LOC_X,

DBMS_LOB.SUBSTR(REPLACE(REPLACE(EXTRACT(VALUE(B),'/item/ny').getClobVal(),'',''),'',''),4000,1) AS LOC_Y,

DBMS_LOB.SUBSTR(REPLACE(REPLACE(EXTRACT(VALUE(B),'/item/category').getClobVal(),'',''),'',''),4000,1) AS CATE,

DBMS_LOB.SUBSTR(REPLACE(REPLACE(EXTRACT(VALUE(B),'/item/obsrValue').getClobVal(),'',''),'',''),4000,1) AS VALUE

FROM (XMLSEQUENCE(EXTRACT(xmltype(UTL_HTTP.REQUEST('http://apis.data.go.kr/1360000/VilageFcstInfoService/getUltraSrtNcst?serviceKey=NPib6%2FwBg1IQG6Z3bCR1beiEEes3FaaTJ%2FWYwA0kZjWHF%2BTZW47jDfhRnnghg5BGQfGwy2EGzkm2yIJ1VT3PvQ%3D%3D&numOfRows=10&pageNo=1&base_date=20210629&base_time=0800&nx=55&ny=127&dataType=XML')),'response/body/items/item'))) B

)

SELECT R.DATE_YYYYMMDD,

R.TIME_HHMM,

R.LOC_X,

R.LOC_Y,

R.CATE,

C.NAME AS NAME,

R.VALUE

FROM TBL_CATE C

JOIN RST R ON C.CODE = R.CATE;

- 결과값 JSON 호출 및 파싱

-- JSON 방식(가로)

WITH A AS (

SELECT UTL_HTTP.REQUEST('http://apis.data.go.kr/1360000/VilageFcstInfoService/getUltraSrtNcst?serviceKey=NPib6%2FwBg1IQG6Z3bCR1beiEEes3FaaTJ%2FWYwA0kZjWHF%2BTZW47jDfhRnnghg5BGQfGwy2EGzkm2yIJ1VT3PvQ%3D%3D&numOfRows=10&pageNo=1&base_date=20210629&base_time=1300&nx=55&ny=127&dataType=JSON') AS RST_VALUE

FROM DUAL

)

SELECT JSON_VALUE(RST_VALUE,'$.response.header.resultCode') AS resultCode,

JSON_VALUE(RST_VALUE,'$.response.header.resultMsg') AS resultMsg,

JSON_VALUE(RST_VALUE,'$.response.body.dataType') AS dataType,

JSON_VALUE(RST_VALUE,'$.response.body.items.item[0].baseDate') AS baseDate,

JSON_VALUE(RST_VALUE,'$.response.body.items.item[0].baseTime') AS baseTime,

JSON_VALUE(RST_VALUE,'$.response.body.items.item[0].nx') AS nx,

JSON_VALUE(RST_VALUE,'$.response.body.items.item[0].ny') AS ny,

JSON_VALUE(RST_VALUE,'$.response.body.items.item[0].obsrValue') AS PTY, -- category : PTY , 강수형태

JSON_VALUE(RST_VALUE,'$.response.body.items.item[1].obsrValue') AS REH, -- category : REH , 습도

JSON_VALUE(RST_VALUE,'$.response.body.items.item[2].obsrValue') AS RN1, -- category : RN1 , 1시간 강수량

JSON_VALUE(RST_VALUE,'$.response.body.items.item[3].obsrValue') AS T1H, -- category : T1H , 기온

JSON_VALUE(RST_VALUE,'$.response.body.items.item[4].obsrValue') AS UUU, -- category : UUU , 동서바람성분

JSON_VALUE(RST_VALUE,'$.response.body.items.item[5].obsrValue') AS VEC, -- category : VEC , 풍향

JSON_VALUE(RST_VALUE,'$.response.body.items.item[6].obsrValue') AS VVV, -- category : VVV , 남북바람성분

JSON_VALUE(RST_VALUE,'$.response.body.items.item[7].obsrValue') AS WSD -- category : WSD , 풍속

FROM A;

3. 입력 테이블 생성

-- "C##TEST".TB_WEATHER definition

CREATE TABLE "C##TEST"."TB_WEATHER"

( "RESULTCODE" VARCHAR2(2),

"RESULTMSG" VARCHAR2(100),

"DATATYPE" VARCHAR2(10),

"BASEDATE" VARCHAR2(8),

"BASETIME" VARCHAR2(4),

"NX" VARCHAR2(10),

"NY" VARCHAR2(10),

"PTY" VARCHAR2(10),

"REH" VARCHAR2(10),

"RN1" VARCHAR2(10),

"T1H" VARCHAR2(10),

"UUU" VARCHAR2(10),

"VEC" VARCHAR2(10),

"VVV" VARCHAR2(10),

"WSD" VARCHAR2(10),

"INSERT_DTTM" DATE,

"INSERT_USER" VARCHAR2(100)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ;

COMMENT ON TABLE "C##TEST".TB_WEATHER IS '기청상 동네예보 조회서비스 _ 초단기실황';

COMMENT ON COLUMN "C##TEST".TB_WEATHER.RESULTCODE IS '결과코드'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.RESULTMSG IS '결과메세지'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.DATATYPE IS '리턴 결과 타입'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.BASEDATE IS '일자(yyyyMMdd)'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.BASETIME IS '시간(HHmm)'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.NX IS '좌표X'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.NY IS '좌표Y'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.PTY IS '강수형태'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.REH IS '습도'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.RN1 IS '1시간 강수량'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.T1H IS '기온'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.UUU IS '동서바람성분'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.VEC IS '풍향'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.VVV IS '남북바람성분'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.WSD IS '풍속'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.INSERT_DTTM IS '입력일'

COMMENT ON COLUMN "C##TEST".TB_WEATHER.INSERT_USER IS '입력자';

4. Oracle JOB에 등록 하기 위한 Procedure 생성

CREATE OR REPLACE PROCEDURE "C##TEST".P_WEATHER_JOB

v_rst_xml VARCHAR2(32767);

v_api_key VARCHAR2(1000); -- API 인증 KEY 값

v_data_type VARCHAR(20); -- RETURN DATA TYPE (XML,JSON)

v_date VARCHAR(8); -- 일자(yyyyMMdd)

v_time varchar(4); -- 시간(HHmm)

v_loc_x varchar(5); -- x좌표

v_loc_y varchar(5); -- y좌표

v_url VARCHAR2(32767);

BEGIN

v_api_key := 'NPib6%2FwBg1IQG6Z3bCR1beiEEes3FaaTJ%2FWYwA0kZjWHF%2BTZW47jDfhRnnghg5BGQfGwy2EGzkm2yIJ1VT3PvQ%3D%3D';

v_data_type := 'JSON';

v_loc_x := '55';

v_loc_y := '127';

SELECT to_char(SYSDATE,'yyyyMMdd'),to_char(SYSDATE,'HH24') || '00' INTO v_date,v_time FROM dual;

v_url := 'http://apis.data.go.kr/1360000/VilageFcstInfoService/getUltraSrtNcst?serviceKey=' || v_api_key || '&numOfRows=10&pageNo=1&base_date=' || v_date || '&base_time=' || v_time || '&nx=' || v_loc_x || '&ny=' || v_loc_y || '&dataType=' || v_data_type;

DBMS_OUTPUT.PUT_LINE(v_url);

--JSON 방식(가로)

INSERT INTO TB_WEATHER(RESULTCODE, RESULTMSG, DATATYPE, BASEDATE, BASETIME, NX, NY, PTY, REH, RN1, T1H, UUU, VEC, VVV, WSD, INSERT_DTTM, INSERT_USER)

WITH A AS (

SELECT UTL_HTTP.REQUEST(v_url) AS RST_VALUE