SQL> truncate table dept;
테이블이 잘렸습니다.
SQL> alter table dept drop column comments;
테이블이 변경되었습니다.
SQL> alter table dept add comments clob;
테이블이 변경되었습니다.
[oracle@mydream test]$ 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 ->"|
[oracle@mydream test]$ 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
*LOB컬럼에 데이터가 존재하지 않고, 파일명만 존재함
SQL> create table lob_demo
2 (owner varchar2(255),
3 time_stamp date,
4 filename varchar2(255),
5 data blob
6 )
7 /
테이블이 생성되었습니다.
[oracle@mydream test]$ 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
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)
[oracle@mydream test]$ 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 처리가 정상적으로 완료되었습니다.
참고
*SQLLDR은 API가 아니므로 PL/SQL에서 호출할 수 없다
주의