프로토타입

파라미터

예외 상황

예제

DECLARE
    dest_lob CLOB := 'All''s fair in ';
    src_lob CLOB := 'love and war';
BEGIN
    DBMS_LOB.APPEND(dest_lob, src_lob);
    DBMS_OUTPUT.PUT_LINE('Result = ' || dest_lob);
END;
/
Result = All's fair in love and war

PSM completed
SQL> 

프로토타입

파라미터

예외 상황

예제

DECLARE
    dest_lob CLOB := 'It you would be loved, ';
    src_lob CLOB := 'be worthy to be loved';
BEGIN
    DBMS_LOB.COPY(dest_lob, src_lob, length(src_lob), 
                  length(dest_lob) + 1, 1);
    DBMS_OUTPUT.PUT_LINE('Result = ' || dest_lob);
END;
/
Result = It you would be loved, be worthy to be loved

PSM completed
SQL> 

프로토타입

DBMS_LOB.CONVERTTOBLOB
(
    dest_lob        IN OUT NO COPY  BLOB,
    src_lob         IN              CLOB,
    amount          IN              INTEGER,
    dest_offset     IN OUT          INTEGER,
    src_offset      IN OUT          INTEGER,
    blob_csid       IN              NUMBER,
    lang_context    IN OUT          INTEGER,
    warning         OUT             INTEGER
);

파라미터

예외 상황

예제

declare
    src_clob                clob;
    dest_blob               blob;
    dest_clob               clob;
    amount                  integer;
    dest_offset             integer;
    src_offset              integer;
    blob_csid               integer;
    lang_context            integer;
    warning                 integer;

begin
    src_clob := 'abcdefghijklmn';
    dbms_lob.createtemporary(dest_blob, false);
    dbms_lob.createtemporary(dest_clob, false);

    amount := 10;
    dest_offset := 1;
    src_offset := 1;
    blob_csid := 0;
    lang_context := 0;
    warning := 0;

    dbms_lob.CONVERTTOBLOB(dest_blob, src_clob, amount,dest_offset, 
                      src_offset, blob_csid, lang_context, warning);
end;
/

PSM completed
SQL> 

프로토타입

DBMS_LOB.CONVERTTOCLOB
(
    dest_lob        IN OUT NO COPY  CLOB,
    src_lob         IN              BLOB,
    amount          IN              INTEGER,
    dest_offset     IN OUT          INTEGER,
    src_offset      IN OUT          INTEGER,
    blob_csid       IN              NUMBER,
    lang_context    IN OUT          INTEGER,
    warning         OUT             INTEGER
);

파라미터

예외 상황

예제

declare
    src_clob                clob;
    dest_blob               blob;
    dest_clob               clob;

    amount                  integer;
    dest_offset             integer;
    src_offset              integer;
    blob_csid               integer;
    lang_context            integer;
    warning                 integer;

begin
    src_clob := 'abcdefghijklmn';
    dbms_lob.createtemporary(dest_blob, false);
    dbms_lob.createtemporary(dest_clob, false);

    amount := 10;
    dest_offset := 1;
    src_offset := 1;
    blob_csid := 0;
    lang_context := 0;
    warning := 0;

    dbms_output.put_line('org clob : ' || src_clob);

    dbms_lob.CONVERTTOBLOB(dest_blob, src_clob, amount,
                           dest_offset, src_offset, blob_csid,
                           lang_context, warning);

    dbms_output.put_line('dest offset : ' || dest_offset);
    dbms_output.put_line('srct offset : ' || src_offset);

    amount := 20;
    dest_offset := 1;
    src_offset := 1;
    blob_csid := 0;
    lang_context := 0;
    warning := 0;

    dbms_lob.CONVERTTOCLOB(dest_clob, dest_blob, amount,
                           dest_offset, src_offset, blob_csid,
                           lang_context, warning);

    dbms_output.put_line('dest clob : ' || dest_clob);
    dbms_output.put_line('dest offset : ' || dest_offset);
    dbms_output.put_line('srct offset : ' || src_offset);
end;
/

org clob : abcdefghijklmn
dest offset : 11
srct offset : 11
dest clob : abcdefghij
dest offset : 11
srct offset : 11

PSM completed.

SQL> 

프로토타입

파라미터

예외 상황

예제

DECLARE
    lob_1 CLOB;
    lob_2 CLOB := 'tibero';
BEGIN
    DBMS_LOB.CREATETEMPORARY(lob_1, false);
    DBMS_LOB.APPEND(lob_1, lob_2);
    DBMS_OUTPUT.PUT_LINE(lob_1);
END;
/
tibero

PSM completed
SQL> 

프로토타입

파라미터

예외 상황

예제

DECLARE
    lob CLOB := 'Tmaxsoft Tibero';
    amount NUMBER := 7;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Length of original LOB = ' || length(lob));
    DBMS_LOB.ERASE(lob, amount, 9);
    DBMS_OUTPUT.PUT_LINE('Value of erased LOB = ' || lob);
    DBMS_OUTPUT.PUT_LINE('Length of erased LOB = ' || length(lob));
END;
/
Length of original LOB = 15
Value of erased LOB = Tmaxsoft
Length of erased LOB = 15

PSM completed
SQL> 

프로토타입

파라미터

예외 상황

예제

DECLARE
    lob CLOB;
BEGIN
    DBMS_LOB.CREATETEMPORARY(lob, false);
    DBMS_LOB.FREETEMPORARY(lob);
END;
/

PSM completed
SQL> 

프로토타입

파라미터

예외 상황

예제

SQL> create directory BFILETESTDIR as '/home/tests/test_bfile/';

Directory 'BFILETESTDIR' created.

SQL> DECLARE 
    f UTL_FILE.FILE_TYPE; 

    buffer RAW(16) := '000102030405060708090A0B0C0C0E0F';
begin 
    f := UTL_FILE.FOPEN('BFILETESTDIR', 'test001.bin', 'wb');
    UTL_FILE.PUT_RAW(f, buffer);
    UTL_FILE.FCLOSE(f);
end;
/

PSM completed.

SQL> DECLARE 
    f1 BFILE;
    bl1 BLOB := HEXTORAW('AAAAAA');
    amount NUMBER;
    buffer RAW(16);
    result RAW(16) := '000102030405060708090A0B0C0C0E0F';
    buffer2 RAW(32);
    result2 RAW(32) := '000102030405060708090A0B0C0D0E0F';
begin
   f1 := BFILENAME('BFILETESTDIR', 'test001.bin');

   DBMS_LOB.FILEOPEN(f1, DBMS_LOB.FILE_READONLY);

   amount := 16;
   DBMS_LOB.LOADFROMFILE(bl1, f1, amount);
   DBMS_LOB.READ(bl1, amount, 1, buffer);

   DBMS_OUTPUT.PUT_LINE(amount);
   DBMS_LOB.FILECLOSE(f1);
end;
/
16

PSM completed.

SQL> DECLARE 
    lob CLOB;
BEGIN
    DBMS_LOB.CREATETEMPORARY(lob, false);
    DBMS_LOB.LOADFROMFILE(lob);
END;
/

PSM completed.

SQL> DECLARE
    f UTL_FILE.FILE_TYPE; 

    buffer RAW(16) := '00610062006300640065006600670068';
BEGIN 
    f := UTL_FILE.FOPEN('BFILETESTDIR', 'ucs2_file.bin', 'wb');
    UTL_FILE.PUT_RAW(f, buffer);
    UTL_FILE.FCLOSE(f);
END;
/

PSM completed.

SQL> SET SERVEROUTPUT ON 
SQL> DECLARE
    f1 BFILE;
    bl1 CLOB := 'init'; 
    amount NUMBER;
BEGIN
   F1 := BFILENAME('BFILETESTDIR', 'ucs2_file.bin');

   DBMS_LOB.FILEOPEN(f1, DBMS_LOB.FILE_READONLY);

   AMOUNT := 8;
   DBMS_LOB.LOADFROMFILE(bl1, f1, amount);

   DBMS_OUTPUT.PUT_LINE ('abcdefgh');

   DBMS_LOB.FILECLOSE(f1);
END;
/
bcdefgh

PSM completed.

SQL> 

프로토타입

DBMS_LOB.LOADBLOBFROMFILE
(
    dest_lob    IN OUT NOCOPY BLOB,
    src_file    IN            BFILE,
    amount      IN            INTEGER,
    dest_offset IN            INTEGER,
    src_offset  IN            INTEGER
);

파라미터

예외 상황

예제

SQL> create directory BFILETESTDIR as '/home/tests/test_bfile/';

Directory 'BFILETESTDIR' created.

SQL> DECLARE 
    f UTL_FILE.FILE_TYPE; 

    buffer RAW(16) := '000102030405060708090A0B0C0C0E0F';
begin 
    f := UTL_FILE.FOPEN('BFILETESTDIR', 'test001.bin', 'wb');
    UTL_FILE.PUT_RAW(f, buffer);
    UTL_FILE.FCLOSE(f);
end;
/

PSM completed.

SQL> DECLARE 
    f1 BFILE;
    bl1 BLOB := HEXTORAW('AAAAAA');
    amount NUMBER;
    buffer RAW(16);
    result RAW(16) := '000102030405060708090A0B0C0C0E0F';
    buffer2 RAW(32);
    result2 RAW(32) := '000102030405060708090A0B0C0D0E0F';
    src_ofst NUMBER;
    dst_ofst NUMBER;
begin
   f1 := BFILENAME('BFILETESTDIR', 'test001.bin');

   DBMS_LOB.FILEOPEN(f1, dbms_lob.file_readonly);

   amount := 16;
    src_ofst := 1;
    dst_ofst := 1;

   DBMS_LOB.LOADBLOBFROMFILE(bl1, f1, amount, dst_ofst,  src_ofst);
   DBMS_LOB.READ(bl1, amount, 1, buffer);

   DBMS_OUTPUT.PUT_LINE(amount);
   DBMS_OUTPUT.PUT_LINE(src_ofst);
   DBMS_OUTPUT.PUT_LINE(dst_ofst);

    
   DBMS_LOB.FILECLOSE(f1);
end;
/
16
17
17

PSM completed.

SQL> 

프로토타입

DBMS_LOB.OPEN
(   
    file_loc     IN OUT NOCOPY   BFILE,
    open_mode    IN              BINARY_INTEGER := file_readonly
);

파라미터

예외 상황

예제

create directory BFILETESTDIR as '/mybasepath/bfiletest/';

declare 
    f utl_file.file_type; 

    buffer raw(16) := '000102030405060708090A0B0C0D0E0F';
begin 
    f := utl_file.fopen('BFILETESTDIR', 'test001.bin', 'wb');
    utl_file.put_raw(f, buffer);
    utl_file.fclose(f);
end;
/

declare
    f1 bfile;
    amount number;
    buffer raw(16);
    result raw(16) := '000102030405060708090A0B0C0C0E0F';
begin
   f1 := bfilename('BFILETESTDIR', 'test001.bin');

   dbms_lob.open(f1, dbms_lob.file_readonly);

   amount := 16;
   dbms_lob.read(f1, amount, 1, buffer);

   dbms_output.put_line(amount);
   dbms_output.put_line(buffer); 
    
   dbms_lob.close(f1);
end;
/ 

16
000102030405060708090A0B0C0D0E0F

프로토타입

파라미터

예외 상황

예제

DECLARE
    lob CLOB := 'TIBERO fighting!!!';
    buffer VARCHAR2(256);
    amount BINARY_INTEGER := 8;
BEGIN
    DBMS_LOB.READ(lob, amount, 8, buffer);
    DBMS_OUTPUT.PUT_LINE('Value to be read = ' || buffer);
END;
/
Value to be read = fighting

PSM completed
SQL> 

프로토타입

파라미터

예외 상황

예제

DECLARE
    lob CLOB := 'A pity beyond all telling is in the heart of love';
BEGIN
    DBMS_LOB.TRIM(lob, 25);
    DBMS_OUTPUT.PUT_LINE('Value = ' || lob);
    DBMS_OUTPUT.PUT_LINE('Length = ' || length(lob));
END;
/
Value = A pity beyond all telling
Length = 25

PSM completed
SQL> 

프로토타입

파라미터

예외 상황

예제

DECLARE
    lob CLOB;
    buffer VARCHAR2(100);
BEGIN
    DBMS_LOB.CREATETEMPORARY(lob, false);
    buffer := 'Love is friendship set on fire';
    DBMS_LOB.WRITE(lob, length(buffer), 1, buffer);
    DBMS_OUTPUT.PUT_LINE(lob);
END;
/
Love is friendship set on fire

PSM completed
SQL> 

프로토타입

파라미터

예외 상황

예제

DECLARE
    lob CLOB := 'Parting is such ';
    buffer VARCHAR2(100) := 'sweet sorrow';
BEGIN
    DBMS_LOB.WRITEAPPEND(lob, length(buffer), buffer);
    DBMS_OUTPUT.PUT_LINE('Result = ' || lob);
END;
/
Result = Parting is such sweet sorrow

PSM completed
SQL> 

프로토타입

파라미터

반환값

예제

DECLARE
    lob_1 CLOB := 'abcdefgh';
    lob_2 CLOB := 'abcdefgg';
BEGIN
    IF DBMS_LOB.COMPARE(lob_1, lob_2) = 0 then
        DBMS_OUTPUT.PUT_LINE('LOB_1 equals LOB_2');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('LOB_1 does not equals LOB_2');
    END IF;
END;
/
LOB_1 does not equals LOB_2

PSM completed
SQL> 

프로토타입

파라미터

반환값

COPY, ERASE, WRITE 등의 프러시저에 의해 채워진 0(BLOB 데이터) 또는 공백(CLOB 데이터) 문자도 대상 LOB 데이터의 길이에 포함된다.

예제

DECLARE
    lob CLOB := 'architecture';
BEGIN
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(lob));
END;
/
12

PSM completed
SQL> 

프로토타입

파라미터

반환값

예제

DECLARE
    lob CLOB := 'Corporate floor';
    result NUMBER;
BEGIN
    result := DBMS_LOB.INSTR(lob, 'or', 3, 2);
    DBMS_OUTPUT.PUT_LINE('Result offset = ' || result);
END;
/
Result offset = 14

PSM completed
SQL> 

프로토타입

파라미터

반환값

예제

DECLARE
    lob CLOB;
BEGIN
    DBMS_LOB.CREATETEMPORARY(lob, false);
    IF DBMS_LOB.ISTEMPORARY(lob) = 1THEN
        DBMS_OUTPUT.PUT_LINE('TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('FALSE');
    END IF;
END;
/
TRUE

PSM completed
SQL> 

프로토타입

파라미터

반환값

예제

DECLARE
    lob CLOB := 'Your friend is too young';
    buffer VARCHAR2(100);
BEGIN
    buffer := DBMS_LOB.SUBSTR(lob, 6, 6);
    DBMS_OUTPUT.PUT_LINE('My favorite word is ' || UPPER(buffer));
END;
/
My favorite word is FRIEND

PSM completed
SQL>