오라클에서 우편번호 넣기

 

 

 

 

전에 포스팅한 우편번호 파일을 우리가 만든 테이블에 넣는 방식은 sqlldr유틸을 이용한 방식이다.

 

참고 (SQLLDR 유틸이용한 방법 http://blog.daum.net/kwonmc/5918853 )

 

그러나 이 방법은 꽤 불편하다. 외부 csv파일을 INSERT 하여 우편번호에 대한 데이터를 넣어줄때 우린 매번 컨트롤 파일을 만들어야

했기 때문에 만일 우편번호가 업데이트 된다면 우린 매번 컨트롤파일을 수정하고 이를 다시 sqlldr 유틸을 도스 프롬프트에서 실행해서

적용도 시켜줘야하므로 불편하다.

 

만일 오라클에서 그냥 csv 파일이있는 위치만 알려주고 바로 연결만 시켜줄수 있다면 곧바로 변경사양도 적용되고 편리할것이다.

이런방식이 실제로 가능하다 바로 EXTERNAL TABLE을 이용하는 것이다.

.
오라클에서는 EXTERNAL TABLE 을 만들수 있는데 이것은 호스트내에 특정 하드디스크 위치에 저장된파일을 직접 읽도록 테이블처럼

만든것이라고 할수 있다.. 단 테이블은 SELECT는 가능하지만 INSERT,UPDATE, DELETE는 불가능하다. 데이터변경하고자한다면

직접 파일을 에디트해서 변경해주어야한다..

 

 

1. 디렉토리만들기

 

우리는 EXTERNAL TABLE사용전에 외부파일이 저장된 경로를 오라클 서버가 알도록 해야한다.

오라클에서는 외부파일경로를 c:\file...\...  이런식으로 접근할 수가 없다 대신, 파일 경로를 DIRECTORY 를 이용해 지정해 만들어

줘야한다.

DIRECTORY 또한 SYS 에서 권한을 줘야 생성가능 하다.  SYS 계정에서 아래와같이 명령어를 처주자.

 

 

GRANT CREATE ANY DIRECTORY TO NEWSCOTT

 

이제 csv파일이있는 정보를 오라클에게 알려주기위해 디렉토리를 만들어 보자.

 

CREATE OR REPLACE DIRECTORY 디렉토리이름
AS
'c:\TEXTDATA\';

 

위에서 디렉토리란것을 만들었다.

제일 밑에 경로명은 cvs 파일이 있는 곳이다. 자신에게 맞게 설정해주면된다. 이제 디렉토리 이름을 을 이용해서 경로 접근이 가능

해졌다.

 

DIRECTORY 정보 조회는

 

SELECT *
FROM ALL_DIRECTORIES;

 

 

 

 

디렉토리 이름으로 DIRZIP이라고 줬을때의 조회된 모습니다. 그 오른쪽의 경로명이 CSV 파일이 있는 경로다.

 

2. 우편번호를 저장할 테이블을 만들때 다음과 같이 추가해준다. 
  
 

CREATE TABLE TBL_EXT_ZIPINFO
(ZIPCODE   VARCHAR2(500)
,SIDO       VARCHAR2(500)   
,GUGUN      VARCHAR2(500)
,DONG      VARCHAR2(500)
,RI        VARCHAR2(500)
,BUNJI     VARCHAR2(500)
,SEQ       NUMBER(5)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER      -- SQLLDR 유틸을 사용하겠다는 의미
 DEFAULT DIRECTORY DIRZIP  -- 정해준 디렉토리 이름을 넣는다 여기서는 DIRZIP이라고 이름 지어준상태다.
 ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE  -- CVS의 행간구분이 새로운줄로 구분되므로
       BADFILE 'ZIPINFO.BAD' 
       -- 만일 CVS파일중에 칼럼명과같은 양식이 맞지 않는 것들은 BAD파일로 보내 뺴버린다' 배드파일 정보를 저장할 파일 이름)
       LOGFILE 'ZIPINFO.LOG'   -- 로그파일을 저장할 파일이름
       FIELDS TERMINATED BY ',' --필드 구분은 ',' (컴마)로 한다는 의미
        (ZIPCODE   CHAR
        ,SIDO       CHAR   
        ,GUGUN     CHAR
        ,DONG      CHAR
        ,RI       CHAR
        ,BUNJI    CHAR
        ,SEQ      CHAR  -- INTEGER EXTERNAL 써줘야하지만 CHAR 로 해도 문제는 없다.
        )
       )
  LOCATION('postcode.csv')  --
 )
 REJECT LIMIT UNLIMITED;   -- badfile 의 갯수를 제한하지 않는다는 의미