EXTERNAL TABLE 만들기 (외부파일을 직접 테이블로)

 외부 파일을 직접 테이블 처럼 읽어오기!

DROP TABLE TBL_MYHOMPI_LOG;

-- SYS에서 권한 설정
GRANT CREATE ANY DIRECTORY TO DB402USER1;

 

CREATE OR REPLACE DIRECTORY MYHOMPIDIR
AS 'D:\PROJECTDATA\';

CREATE TABLE TBL_MYHOMPI_LOG
(NALJA VARCHAR2(20),
SIGAN VARCHAR2(20),
CSMETHOD VARCHAR2(20),
FILENAME VARCHAR2(100),
CIP VARCHAR2(30)
)
ORGANIZATION EXTERNAL
  (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY MYHOMPIDIR
  ACCESS PARAMETERS
          (RECORDS DELIMITED BY NEWLINE
          BADFILE 'MYHOMPI.BAD'
          LOGFILE 'MYHOMPI.LOG'
          FIELDS TERMINATED BY ','
                      (NALJA CHAR,
                      SIGAN CHAR,
                      CSMETHOD CHAR,
                      FILENAME CHAR,
                      CIP CHAR
                      )
          )
  LOCATION ('MYHOMPI.CSV')
  )
REJECT LIMIT UNLIMITED;

SELECT * FROM TBL_MYHOMPI_LOG;

--F30

SELECT CIP, COUNT(CIP)
FROM TBL_MYHOMPI_LOG
WHERE UPPER(FILENAME)='/DEFAULT.HTM'
GROUP BY ROLLUP(CIP);

DELETE TBL_MYHOMPI_LOG;
--31

--외부 파일에서 직접!! 삭제, 추가가 해야함!!
--32 33

SELECT CIP, COUNT(CIP)
FROM TBL_MYHOMPI_LOG
WHERE UPPER(FILENAME)='/DEFAULT.HTM'
GROUP BY ROLLUP(CIP);

--F34

--우편 주소를 EXTERNAL로!
CREATE TABLE TBL_ZIP_EX
(ZIPCODE VARCHAR2(10),
 SIDO VARCHAR2 (200),
 GUGUN VARCHAR2(200),
 DONG VARCHAR2(200),
 RI VARCHAR2(200),
 BLDG VARCHAR2(200),
 BUNJI VARCHAR2(200),
 SEQ NUMBER)
ORGANIZATION EXTERNAL
 (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY MYHOMPIDIR
    ACCESS PARAMETERS
     (RECORDS DELIMITED BY NEWLINE
        BADFILE 'ZIPEX.BAD'
        LOGFILE 'ZIPEX.LOG'
        FIELDS TERMINATED BY ','
         (ZIPCODE CHAR,
             SIDO CHAR,
             GUGUN CHAR,
             DONG CHAR,
             RI CHAR,
             BLDG CHAR,
             BUNJI CHAR,
             SEQ CHAR)
        )
    LOCATION ('ZIPCODE.csv')
)REJECT LIMIT UNLIMITED;

SELECT * FROM TBL_ZIP_EX;

--F35