[oracle] BLOB, CLOB 데이터 오라클에서 직접 INSERT

1. DIRECTORY로서 경로를 지정 (직접 디스크상에 존재하는 파일을 LOB에 넣어줘야 하니까.)

    단, 디렉토리를 만들기 위해서는 권한 필요

         GRANT CREATE ANY DIRECTORY TO user

-------------------------------------------------------------

CREATE OR REPLACE DIRECTORY CLOB_DIR
AS 'c:\';

-------------------------------------------------------------

2. PROCEDURE 생성(CLOB)

-------------------------------------------------------------

CREATE OR REPLACE PROCEDURE PCD_TBL_LOB_INSERT
(V_FILENAME IN VARCHAR2)
IS    V_LOCATOR_CLOB  CLOB;
    
    V_SOURCE_DATA_FILE BFILE := BFILENAME('CLOB_DIR', V_FILENAME);    
    V_DEST_OFFSET    NUMBER := 1;
    V_SRC_OFFSET      NUMBER := 1;
    V_LANG_CONTEXT  NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
    V_WARNING           NUMBER;
    
BEGIN
         INSERT INTO TBL_LOB(NO, TXT_FILE)
         VALUES(SEQ_LOB.NEXTVAL, EMPTY_CLOB() )         RETURNING TXT_FILE INTO V_LOCATOR_CLOB;
         
         DBMS_LOB.OPEN(V_SOURCE_DATA_FILE, 
                                  DBMS_LOB.LOB_READONLY);
                                  
         DBMS_LOB.LOADCLOBFROMFILE(V_LOCATOR_CLOB,
                                                      V_SOURCE_DATA_FILE,
                                                      DBMS_LOB.GETLENGTH(V_SOURCE_DATA_FILE),
                                                      V_DEST_OFFSET,
                                                      V_SRC_OFFSET,
                                                      DBMS_LOB.DEFAULT_CSID,
                                                      V_LANG_CONTEXT,
                                                      V_WARNING);
                                                      
         DBMS_LOB.CLOSE(V_SOURCE_DATA_FILE);
         
         COMMIT;
END;

 -------------------------------------------------------------

복잡해보이지만, 굵은 글씨로 된부분을 자신에게 맞게 고처주면된다.

EXEC PCD_TBL_LOB_INSERT('test.sql')

참고로 물리적 DIRECTORY 에서 지정된 공간에서 파일이 삭제되더라도 DB에는 파일이 남아있다.

3. PROCEDURE 생성(BLOB)

 -------------------------------------------------------------

CREATE OR REPLACE PROCEDURE PCD_TBL_BLOB_INSERT
(V_FILENAME IN VARCHAR2)
IS
    V_LOCATOR_BLOB  BLOB;
    
    V_SOURCE_DATA_FILE BFILE := BFILENAME('BLOB_DIR', V_FILENAME);
    
    V_DEST_OFFSET    NUMBER := 1;
    V_SRC_OFFSET      NUMBER := 1;

    
BEGIN
         INSERT INTO TBL_LOB2(NO, IMG_FILE)
         VALUES(SEQ_LOB.NEXTVAL, EMPTY_BLOB() )
         RETURNING IMG_FILE INTO V_LOCATOR_CLOB;
         
         DBMS_LOB.OPEN(V_SOURCE_DATA_FILE, 
                                  DBMS_LOB.LOB_READONLY);
                                  
         DBMS_LOB.LOADBLOBFROMFILE(V_LOCATOR_BLOB,
                                                      V_SOURCE_DATA_FILE,
                                                      DBMS_LOB.GETLENGTH(V_SOURCE_DATA_FILE),
                                                      V_DEST_OFFSET,
                                                      V_SRC_OFFSET,
                                                      );
                                                      
         DBMS_LOB.CLOSE(V_SOURCE_DATA_FILE);
         
         COMMIT;
         
         DBMS_OUTPUT.PUT_LINE('CLOB 타입 데이터 입력 성공!!!');
         
         EXCEPTION
            WHEN OTHERS THEN 
                DBMS_OUTPUT.PUT_LINE('CLOB 타입 데이터 입력 실패!!!');
END;

 -------------------------------------------------------------

EXEC PCD_TBL_BLOB_INSERT('저장할파일이름', 'dog.jpg');

SELECT *
FROM TBL_LOB2

-출처: http://blog.daum.net/kwonmc/5918858