전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
SQL*Loader 2 0 0 48,848

by 구루비스터디 SQL Loader [2018.09.27]


  1. 14. 테스트12( Inline LOB 데이터 로딩 )
  2. 15. 테스트13( Out of Line LOB 데이터 로딩 )
  3. 16. 테스트14( 객체 컬럼에 LOB 데이터 로딩 )
  4. 17. 저장 프로시저에서 SQLLDR를 호출하는 방법
  5. 18. SQLLDR 사용시 주의사항


14. 테스트12( Inline LOB 데이터 로딩 )


SQL> truncate table dept;

테이블이 잘렸습니다.

SQL> alter table dept drop column comments;

테이블이 변경되었습니다.

SQL> alter table dept add comments clob;

테이블이 변경되었습니다.


$ cat demo21.dat
10,Sales,Virginia,This is the Sales
Office in Virginia|
20,Accounting,viginia,This is the Accounting
Office in Virginia|
30,Consulting,viginia,This is the Consulting
Office in Virginia|
40,Finance,viginia,"This is the Finance
Office in VIrginia. it has embedded comas
and is much longer than the other comments field.
If you feel the need to add double quoted text in here like
this: ""You will need to double up those quotes!"" to
preserve them in the stirng. This field keeps going for up to
1000000 bytes (because of the control file definition I Used)
or until we hit the magic end of record marker
the | followed by an end of line - it is right here ->"|




$ cat demo21.ctl
LOAD DATA
INFILE demo21.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME       "upper(:dname)"
, LOC          "upper(:loc)"
, COMMENTS     char(1000000)    -- 입력받고자 하는 가장 큰 데이터의 크기보다 크게함
)

-- 3번째 열이 CLOB 타입으로 긴 문자열임, 
-- 아래 조회에서는 데이터가 잘린 것으로 나오나 실제 토드상에서 조회 시 입력하고자 했던 데이터가 모두 입력되었음
-- 따옴표(")를 두번 입력했으나 SQLLDR에 의해 1개가 제거 되었음
SQL> select comments from dept;

COMMENTS
------------------------------
This is the Consulting
Office in Virginia

This is the Sales
Office in Virginia

"This is the Finance
Office in VIrginia. it has emb
edded comas
and is much lon

This is the Accounting
Office in Virginia




15. 테스트13( Out of Line LOB 데이터 로딩 )

  • LOB컬럼에 데이터가 존재하지 않고, 파일명만 존재함

SQL> create table lob_demo
  2  (owner varchar2(255),
  3   time_stamp date,
  4   filename varchar2(255),
  5   data blob
  6  )
  7  /

테이블이 생성되었습니다.

$ cat demo22.ctl
LOAD DATA
INFILE *
REPLACE
INTO TABLE LOB_DEMO
( owner    position(14:19),
  time_stamp position(30:42) date "Mon DD HH24:MI",
  filename position(44:64),
  data   LOBFILE(filename) TERMINATED BY EOF
)
BEGINDATA
-rw-r--r-- 1 oracle dba   17 12월 29 14:12 demo.bad
-rw-r--r-- 1 oracle dba  177 12월 29 14:12 demo.ctl
-rw-r--r-- 1 oracle dba 1483 12월 29 14:13 demo.log
-rw-r--r-- 1 oracle dba  199 12월 29 18:19 demo10.ctl
-rw-r--r-- 1 oracle dba 1568 12월 29 18:20 demo10.log
-rw-r--r-- 1 oracle dba  221 12월 29 18:22 demo11.ctl
-rw-r--r-- 1 oracle dba 1568 12월 29 18:23 demo11.log
-rw-r--r-- 1 oracle dba   30 12월 29 18:33 demo12.bad
-rw-r--r-- 1 oracle dba  195 12월 29 18:34 demo12.ctl
-rw-r--r-- 1 oracle dba 1568 12월 29 18:35 demo12.log
-rw-r--r-- 1 oracle dba    1 12월 29 18:41 demo13.bad
-rw-r--r-- 1 oracle dba  273 12월 29 18:53 demo13.ctl
-rw-r--r-- 1 oracle dba 1652 12월 29 18:53 demo13.log
-rw-r--r-- 1 oracle dba   91 12월 29 19:00 demo14.bad
-rw-r--r-- 1 oracle dba  328 12월 29 18:57 demo14.ctl
-rw-r--r-- 1 oracle dba 2182 12월 29 19:00 demo14.log
-rw-r--r-- 1 oracle dba  347 12월 29 19:03 demo15.ctl
-rw-r--r-- 1 oracle dba 1858 12월 29 19:03 demo15.log
-rw-r--r-- 1 oracle dba  574 12월 29 19:32 demo16.ctl
-rw-r--r-- 1 oracle dba 1951 12월 29 19:32 demo16.log

SQL > select * from lob_demo;


OWNER           TIME_STA FILENAME             DATA
--------------- -------- -------------------- --------------------------------------------------
oracle          14/12/29 demo15.log           0A53514C2A4C6F616465723A2052656C656173652031312E32
                                              2E302E312E30202D2050726F64756374696F6E206F6E20BFF9
                                              203132BFF92032392031393A30333A333020323031340A0A43
                                              6F70797269

oracle          14/12/29 demo16.ctl           4C4F414420444154410A494E46494C45202A0A494E544F2054
                                              41424C4520444550540A5245504C4143450A4649454C445320
                                              5445524D494E4154454420425920272C270A545241494C494E
                                              47204E554C

oracle          14/12/29 demo16.log           0A53514C2A4C6F616465723A2052656C656173652031312E32
                                              2E302E312E30202D2050726F64756374696F6E206F6E20BFF9
                                              203132BFF92032392031393A33323A343520323031340A0A43
                                              6F70797269




16. 테스트14( 객체 컬럼에 LOB 데이터 로딩 )


SQL> create table image_load (
  2  id number,
  3  name varchar2(255),
  4  image ordsys.ordimage
  5  )
  6  /

테이블이 생성되었습니다.

-- 인터미디어 설치(오라클에서 멀티미디어 파일을 사용 가능하게 함)와 설정이 되어 있어야 한다
-- 참고URL : http://docs.oracle.com/cd/B19306_01/appdev.102/b14302/ch_intr.htm

SQL> desc image_load
 이름                                                  널?      유형
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             NUMBER
 NAME                                                           VARCHAR2(255)
 IMAGE                                                          ORDSYS.ORDIMAGE


SQL> desc ordsys.ordimage
 이름                                                  널?      유형
 ----------------------------------------------------- -------- ------------------------------------
 SOURCE                                                         ORDSYS.ORDSOURCE
 HEIGHT                                                         NUMBER(38)
 WIDTH                                                          NUMBER(38)
 CONTENTLENGTH                                                  NUMBER(38)
 FILEFORMAT                                                     VARCHAR2(4000)
 CONTENTFORMAT                                                  VARCHAR2(4000)
 COMPRESSIONFORMAT                                              VARCHAR2(4000)
 MIMETYPE                                                       VARCHAR2(4000)

METHOD
------
 FINAL CONSTRUCTOR FUNCTION ORDIMAGE RETURNS SELF AS RESULT
 인수명                         유형                    기본 내부/외부?
 ------------------------------ ----------------------- --------- --------
 DATA                           BLOB                    IN
 SETPROPERTIES                  NUMBER                  IN     DEFAULT

METHOD
------
 FINAL CONSTRUCTOR FUNCTION ORDIMAGE RETURNS SELF AS RESULT
 인수명                         유형                    기본 내부/외부?
 ------------------------------ ----------------------- --------- --------
 SOURCE_TYPE                    VARCHAR2                IN     DEFAULT
 SOURCE_LOCATION                VARCHAR2                IN     DEFAULT
 SOURCE_NAME                    VARCHAR2                IN     DEFAULT
 SETPROPERTIES                  NUMBER                  IN     DEFAULT

-- 이미지는 구조체임을 설명해줌(IMAGE.SOURCE.LOCALDATA)
$ cat demo23.ctl
LOAD DATA
INFILE *
REPLACE
INTO TABLE image_load
FIELDS TERMINATED BY ','
( id,
  name,
  file_name FILLER,
  IMAGE column object
  (
    SOURCE column object
    (
      LOCALDATA LOBFILE (file_name) TERMINATED BY EOF
        NULLIF file_name = 'NONE'
     )
   )
)
BEGINDATA
1,test,test.jpg
I have introduced two new constructs here:


SQL> SELECT * FROM IMAGE_LOAD;

        ID NAME                 IMAGE(SOURCE(LOCALDATA, SRCTYPE, SRCLOCATION, SRCN
---------- -------------------- --------------------------------------------------
         1 test                 ORDIMAGE(ORDSOURCE('FFD8FFE000104A4649460001010000
                                0100010000FFFE005843524541544F523A2058562056657273
                                696F6E20332E31306120205265763A2031322F32392F393420
                                28504E4720706174636820312E3229', NULL, NULL, NULL,
                                 NULL, NULL), NULL, NULL, NULL, NULL, NULL, NULL,
                                NULL)

-- 이미지의 나머지 속성값을 설정하도록 함
DECLARE
 v_image ORDSYS.ORDImage;
BEGIN
 SELECT image INTO v_image FROM image_load where id = 1;

-- 속성값을 자동으로 설정함
 v_image.setProperties();

 UPDATE image_load SET image = v_image WHERE id = 1; 
 COMMIT;
END;
/

-- 속성값 조회해 보기
-- 참고URL : http://docs.oracle.com/cd/E11882_01/appdev.112/e10776/ch_imgref.htm#AIVUG80491
SQL>  declare
  2  v_image ordsys.ordimage;
  3   begin
  4     select image
  5       into v_image
  6       from image_load
  7     where id = 1;
  8
  9   DBMS_OUTPUT.PUT_LINE('image width = ' || v_image.getWidth());
 10   DBMS_OUTPUT.PUT_LINE('image height = ' || v_image.getHeight());
 11   DBMS_OUTPUT.PUT_LINE('image size = ' || v_image.getContentLength());
 12   DBMS_OUTPUT.PUT_LINE('image file type = ' || v_image.getFileFormat());
 13   DBMS_OUTPUT.PUT_LINE('image type = ' || v_image.getContentFormat());
 14   DBMS_OUTPUT.PUT_LINE('image compression = ' || v_image.getCompressionFormat());
 15   DBMS_OUTPUT.PUT_LINE('image mime type = ' || v_image.getMimeType());
 16   end;
 17  /
image width = 157
image height = 45
image size = 2546
image file type = JFIF
image type = 24BITRGB
image compression = JPEG
image mime type = image/jpeg

PL/SQL 처리가 정상적으로 완료되었습니다.



17. 저장 프로시저에서 SQLLDR를 호출하는 방법

참고

*SQLLDR은 API가 아니므로 PL/SQL에서 호출할 수 없다


18. SQLLDR 사용시 주의사항

주의

  • truncate 옵션 사용 시 작동방식 : 빈 공간이라는 표시만 해두고, extent를 반환하지 않음, 비슷한 공간을 사용한다는 가정하에 truncate가 작동하기 때문
    • truncate table t reuse storage
  • SQLLDR 기본값은 char(255) : 입력하려는 컬럼의 길이에 유의해야 함
  • 명령어는 control 파일보다 우선됨 (예: sqlldr datafile=test.dat 라고 했을 경우 control 파일내의 데이터 파일위치는 무시됨)

"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4072

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입