О работе с большими объектами в СУБД Oracle

Для работы с данными большого объема СУБД Oracle предоставляет типы данных BLOB, CLOB, NCLOB и BFILE. Здесь LOB означает large object, или большой объект, и далее по тексту термины LOB и "большой объект" взаимозаменяемы. По сути, большой объект - это абстрактный тип для манипуляции данными большого объема внутри БД, а типы BLOB, CLOB, NCLOB и BFILE - его конкретные реализации.

Указанные типы данных можно использовать в СУБД Oracle для определения столбцов таблиц, атрибутов объектных типов и переменных PL/SQL.

Вот краткая характеристика этих типов:

• BFILE (от binary file) - данные хранятся во внешнем по отношению к БД файле, а значение типа BFILE содержит указатель на файл; данные считаются двоичными.
• BLOB (от binary large object) - данные хранятся в базе данных в отдельном сегменте^*, а значение типа BLOB содержит указатель на них (LOB locator); данные считаются двоичными.
• CLOB (от character large object) - данные хранятся в базе данных в отдельном сегменте^*, а значение типа CLOB содержит указатель на них (LOB locator); данные интерпретируются как текст в кодировке базы данных (database character set).
• NCLOB (от national character large object) - данные хранятся в базе данных в отдельном сегменте^*, а значение типа CLOB содержит указатель на них (LOB locator); данные интерпретируются как текст в национальной кодировке (national character set)

* По умолчанию LOB'ы размером до 4000 байт хранятся непосредственно в строках таблицы (в табличном сегменте), а LOB'ы большего размера - в отдельном сегменте (возможно, в отдельном табличном пространстве). Это поведение регулируется опцией ENABLE|DISABLE STORAGE IN ROW команд CREATE TABLE и ALTER TABLE.

Итак, по месту хранения LOB'ы делятся на

• внутренние (BLOB, CLOB, NCLOB), данные которых хранятся в БД, и
• внешние (BFILE), данные которых хранятся в файлах операционной системы,

а по содержанию на

• двоичные (BFILE и BLOB), для хранения данных в двоичных форматах, например, MP3, JPG, объектный код программ, и
• текстовые (CLOB и NCLOB), для хранения данных в текстовых форматах, таких как XML, HTML, JSON, обычный текст.

Oracle 11g, согласно документации, работает с внутренними LOB'ами размером до 2³²-1 байт и с BFILE файлами размером до 2⁶⁴-1 байт.

Для работы с LOB'ами cоздам таблицу со столбцами соответствующих типов:

SQL> create table lobs_tab (
  2      id number(5),
  3      clob_col clob,
  4      nclob_col nclob,
  5      blob_col blob,
  6      bfile_col bfile
  7  );
Table created

Вместе с таблицей были созданы сегменты для хранения больших объектов:

SQL> select table_name, column_name, segment_name
  2  from user_lobs
  3  where table_name = 'LOBS_TAB';

TABLE_NAME          COLUMN_NAME         SEGMENT_NAME
------------------- ------------------- ------------------------------
LOBS_TAB             CLOB_COL            SYS_LOB0000370714C00002$$
LOBS_TAB             NCLOB_COL           SYS_LOB0000370714C00003$$
LOBS_TAB             BLOB_COL            SYS_LOB0000370714C00004$$

SQL> select segment_name, segment_type
  2  from user_segments 
  3  where segment_name in (
  4      select segment_name
  5      from user_lobs
  6      where table_name = 'LOBS_TAB');

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------
SYS_LOB0000370714C00002$$      LOBSEGMENT
SYS_LOB0000370714C00003$$      LOBSEGMENT
SYS_LOB0000370714C00004$$      LOBSEGMENT

Для столбца типа BFILE отдельный сегмент не создан - ведь данные этого типа хранятся во внешних файлах.

Значение типа LOB может быть

• NULL - неинициализировано, не содержит указателя на LOB,
• пустым (empty) - указатель на LOB указывает в никуда,
• непустым - указатель на LOB указывает на данные LOB'а.

Пустые LOB значения создаются функциями EMPTY_CLOB и EMPTY_BLOB:

SQL> insert into lobs_tab (
  2      id, clob_col, nclob_col, blob_col, bfile_col)
  3  values (
  4      1, null, empty_clob(), empty_blob(), null);
1 row inserted

SQL> commit;
Commit complete

SQL> select 'nulls'
  2  from lobs_tab
  3  where clob_col is null
  4      and bfile_col is null;
'NULLS'
-------
nulls

SQL> select 'empty'
  2  from lobs_tab
  3  where dbms_lob.getlength(nclob_col) = 0
  4      and dbms_lob.compare(blob_col, empty_blob()) = 0;
'EMPTY'
-------
empty

Последний запрос демонстрирует два способа проверить, является ли LOB пустым. Запрос использует пакет DBMS_LOB, содержащий процедуры и функции для работы с LOB'ами.

Начиная с версии Oracle 9i в SQL и PL/SQL поддерживается неявная конвертация между (N)CLOB и VARCHAR2, что позволяет манипулировать значениями в (N)CLOB столбцах и переменных так, как будто это значения типа VARCHAR2:

SQL> update lobs_tab set
  2      clob_col = 'привет, мир'
  3  where id = 1;
1 row updated

SQL> select length(clob_col), dbms_lob.getlength(clob_col), '"' || clob_col || '"'
  2  from lobs_tab
  3  where clob_col like '%мир%';

LENGTH(CLOB_COL) DBMS_LOB.GETLENGTH(CLOB_COL) '"'||CLOB_COL||'"'
---------------- ----------------------------  ------------------------------
              11                           11 "привет, мир"

Как видим, функции и операторы, работающие с VARCHAR2, перегружены для типа (N)CLOB! При этом преодолеваются ограничения в 4000 символов, свойственные SQL типу VARCHAR2:

SQL> update lobs_tab set
  2      clob_col = rpad(clob_col, 1000000, '!')
  3  where id = 1;
1 row updated

SQL> select length(clob_col), dbms_lob.getlength(clob_col), substr(clob_col, 500000, 10)
  2  from lobs_tab
  3  where clob_col like '%мир%';

LENGTH(CLOB_COL) DBMS_LOB.GETLENGTH(CLOB_COL) SUBSTR(CLOB_COL,500000,10)
---------------- ---------------------------- ----------------------------------
         1000000                      1000000 !!!!!!!!!!

SQL> update lobs_tab set
  2      nclob_col = rpad(to_nclob('hello, world'), 1000000, ')')
  3  where id = 1;
1 row updated

SQL> select length(nclob_col), dbms_lob.getlength(nclob_col), substr(nclob_col, 500000, 10)
  2  from lobs_tab
  3  where nclob_col like '%world%';

LENGTH(NCLOB_COL) DBMS_LOB.GETLENGTH(NCLOB_COL) '"'||NCLOB_COL||'"'
----------------- ----------------------------- --------------------------------
          1000000                       1000000 ))))))))))

А вот операторы сравнения для (N)CLOB работают только в PL/SQL и не работают в SQL:

SQL> select *
  2  from lobs_tab
  3  where clob_col > 'привет, мир!';

ORA-00932: inconsistent datatypes: expected - got CLOB

SQL> select *
  2  from lobs_tab
  3  where clob_col > to_clob('привет, мир!');

ORA-00932: inconsistent datatypes: expected - got CLOB

SQL> declare
  2      c1 clob := 'hello';
  3      c2 clob := 'hello world';
  4      c3 clob := rpad(to_clob('aloha'), 1000000, '!');
  5      c4 clob := rpad(to_clob('aloha'), 1000000, '!');
  6  begin
  7      if c1 < c2 then
  8          dbms_output.put_line('c1 < c2');
  9      end if;
 10      if c2 != c3 then
 11          dbms_output.put_line('c2 != c3');
 12      end if;
 13      if c3 = c4 then
 14          dbms_output.put_line('c3 = c4');
 15      end if;
 16  end;
 17  /

c1 < c2
c2 != c3
c3 = c4

PL/SQL procedure successfully completed

Выше я воспользовался функциями TO_NCLOB и TO_CLOB для явной конвертации значений VARCHAR2 в значения (N)CLOB. В следующей таблице представлены все функции для конвертации в LOB типы и обратно:

Функция	Где работает
TO_CLOB(character_data)	SQL и PL/SQL
TO_BLOB(raw_data)	SQL и PL/SQL
TO_LOB(long_data)	SQL and PL/SQL
TO_NCLOB(character_data)	SQL и PL/SQL
TO_RAW(blob_data)	только PL/SQL

Как видим, функция TO_RAW недоступна в SQL и, отсюда, возможности конвертации между BLOB и RAW в SQL ограничены. Например:

SQL> update lobs_tab set
  2      blob_col = HEXTORAW('00AABBCCDDEEFF')
  3  where id = 1;
1 row updated

SQL> select to_raw(blob_col)
  2  from lobs_tab
  3  where id = 1;

ORA-00904: "TO_RAW": invalid identifier

Зато в PL/SQL работают явная и неявная конвертации между BLOB и RAW:

SQL> declare
  2      b1 blob := HEXTORAW('00AABBCCDDEEFF');
  3      r1 raw(4000) := b1;
  4      r2 raw(4000) := to_raw(b1);
  5  begin
  6      dbms_output.put_line('r1 = ' || r1);
  7      dbms_output.put_line('r2 = ' || r2);
  8  end;
  9  /
  
r1 = 00AABBCCDDEEFF
r2 = 00AABBCCDDEEFF

PL/SQL procedure successfully completed

Рассмотренные возможности по работе со значениями LOB как с VARCHAR2 получили название SQL семантика для LOB'ов (SQL semаntics for LOBs). С их использованием связаны некоторые ограничения, как мы увидим ниже.

С точки зрения PL/SQL большие объекты делятся на:

• временные (temporary), время жизни которых не превышает сеанса работы с СУБД,
• постоянные (persistent), которые хранятся в базе данных или во внешнем файле.

Временные LOB'ы

• создаются либо с помощью DBMS_LOB.CREATETEMPORARY, либо простым присваиванием значения LOB переменной в PL/SQL коде,
• располагаются на диске во временном табличном пространстве (temporary tablespace),
• могут быть проверены с помощью DBMS_LOB.ISTEMPORARY,
• освобождаются с помощью DBMS_LOB.FREETEMPORARY, что приводит к инвалидированию указателя на LOB,
• в отличие от постоянных, изменяются без создания записей в журнале БД (logging) и не контролируются транзакциями,
• могут быть скопированы в постоянные LOB'ы c помощью DBMS_LOB.COPY.

В вышеприведенных примерах с PL/SQL мы имели дело с временными LOB'ами.

Для работы с постоянными LOB'ами в PL/SQL нужно сначала получить указатель на LOB, а затем с его помощью извлекать или изменять данные, используя пакет DBMS_LOB. Следующий пример демонстрирует получение постоянного LOB'а и его потерю(!) при попытке изменить его значение простым присваиванием:

SQL> declare
  2      c1 clob;
  3  begin
  4      select clob_col into c1 from lobs_tab where id = 1;
  5      dbms_output.put_line('1 - c1 временный? ' || dbms_lob.istemporary(c1));
  6  
  7      c1 := 'Новое значение';
  8      dbms_output.put_line('2 - c1 временный? ' || dbms_lob.istemporary(c1));
  9  
 10      -- еще раз!
 11      select clob_col into c1 from lobs_tab where id = 1;
 12      dbms_output.put_line('3 - c1 временный? ' || dbms_lob.istemporary(c1));
 13  
 14      c1 := upper(c1);
 15      dbms_output.put_line('4 - c1 временный? ' || dbms_lob.istemporary(c1));
 16  end;
 17  /
 
1 - c1 временный? 0
2 - c1 временный? 1
3 - c1 временный? 0
4 - c1 временный? 1

PL/SQL procedure successfully completed

Дело в том, что SQL семантика для LOB'ов всегда порождает временные LOB'ы - это и есть то ограничение, о котором я упоминал выше. Неявное приведение VARCHAR2 к LOB (строка 7) или функция, перегруженная для (N)CLOB (строка 14), дают нам временные LOB'ы. Как только переменной PL/SQL, указывающей на постоянный LOB, присваивается временный LOB, переменная начинает указывать на временный LOB. А связь переменной с постоянным LOB'ом утрачивается.

Значение временного LOB'а можно сохранить в БД - и тем самым сделать постоянным - либо с помощью SQL либо, как уже упоминалось, с помощью DBMS_LOB.COPY. Продемонстрирую обе возможности:

SQL> select clob_col, nclob_col from lobs_tab where id = 1;

CLOB_COL                               NCLOB_COL
-------------------------------------- -----------------------------------------
привет, мир!!!!!!!!!!!!!!!!!!!!!!!!!!! hello, world)))))))))))))))))))))))))))))

SQL> declare
  2      c1 clob := 'Я здесь на время';
  3      c2 nclob := 'I am a temporary one';
  4      c3 nclob;
  5  begin
  6      update lobs_tab set clob_col = c1 where id = 1;
  7  
  8      select nclob_col into c3 from lobs_tab where id = 1;
  9      dbms_lob.copy(
 10         dest_lob => c3,
 11         src_lob => c2,
 12         amount => dbms_lob.getlength(c2)
 13      );
 14  end;
 15  /
PL/SQL procedure successfully completed

SQL> select clob_col, nclob_col from lobs_tab where id = 1;

CLOB_COL                               NCLOB_COL
-------------------------------------- -----------------------------------------
Я здесь на время                       I am a temporary one)))))))))))))))))))))

Обратите внимание, что процедура DBMS_LOB.COPY заменила в постоянном NCLOB c3 только фрагмент, равный по размеру значению исходного NCLOB'а c2. Как вариант, можно было перед копированием очистить LOB назначения с помощью DBMS_LOB.ERASE.

Изменения внутренних постоянных LOB'ов (в отличие от внешних или временных) в СУБД Oracle подчиняются транзакциям. Убедимся в этом, отменив только что сделанные изменения:

SQL> rollback;
Rollback complete

SQL> select clob_col, nclob_col from lobs_tab where id = 1;

CLOB_COL                               NCLOB_COL
-------------------------------------- -----------------------------------------
привет, мир!!!!!!!!!!!!!!!!!!!!!!!!!!! hello, world)))))))))))))))))))))))))))))

Типичный алгоритм для чтения или изменения постоянного LOB'а с помощью PL/SQL таков:

1. Извлечь указатель на LOB из столбца таблицы с помощью SELECT.
2. Открыть большой объект с помощью DBMS_LOB.OPEN.
3. Получить оптимальный размер фрагмента для чтения (записи) LOB с помощью DBMS_LOB.GETCHUNKSIZE
4. Получить размер LOB'а в байтах (для BLOB и BFILE) или символах (для CLOB и NCLOB) с помощью DBMS_LOB.GETLENGTH.
5. Многократно вызывать DBMS_LOB.READ для чтения последовательных фрагментов LOB'а, пока не будут извлечены все данные
   ИЛИ
   многократно вызывать DBMS_LOB.WRITE, со смещением, или DBMS_LOB.WRITEAPPEND или иные процедуры DBMS_LOB для записи фрагментов данных.
6. Закрыть LOB с помощью DBMS_LOB.CLOSE.

В предыдущем примере с DBMS_LOB.COPY я не открывал и не закрывал постоянный LOB при помощи DBMS_LOB.OPEN и DBMS_LOB.CLOSE, однако, это стоит делать для улучшения производительности при изменениях больших объектов.

Приведу пример выгрузки данных из постоянного CLOB'а во внешний файл. Для доступа к внешним файлам потребуется создать директорию, например:

SQL> create directory files_dir as '/home/oracle/files';
Directory created

Следующий код выгружает содержимое столбца lobs_tab.clob_col в файл clob_col1.txt, используя пакет DBMS_LOB для чтения CLOB и пакет UTL_FILE для записи во внешний файл:

declare
    l_clob clob;
    l_clob_len pls_integer;

    l_file      utl_file.file_type;
    l_buffer    varchar2(32767);
    l_amount    pls_integer;
    l_pos       binary_integer := 1;
begin
    select clob_col into l_clob from lobs_tab where id = 1;

    l_clob_len := dbms_lob.getlength(l_clob);
    l_amount := least(dbms_lob.getchunksize(l_clob), floor(32767 / 4) /*utf-8*/);

    l_file := utl_file.fopen('FILES_DIR', 'clob_col1.txt', 'wb', 32767);

    while l_pos < l_clob_len loop
        dbms_lob.read(l_clob, l_amount, l_pos, l_buffer);
        utl_file.put_raw(l_file, utl_raw.cast_to_raw(l_buffer), true);
        l_pos := l_pos + l_amount;
    end loop;

    utl_file.fclose(l_file);
end;
/

Проверяю результат:

[oracle@tsuki files]$ ls -l
-rw-r--r-- 1 oracle oinstall 1000009 May 20 20:54 clob_col1.txt

Альтернативно, можно выгрузить CLOB во внешний файл, пользуясь SQL семантикой для LOB и не прибегая к DBMS_LOB:

declare
    l_clob      clob;
    l_file      utl_file.file_type;
    l_buffer    varchar2(32767);
    l_amount    binary_integer := floor(32767 / 4); /*utf-8*/
    l_pos       binary_integer := 1;
begin
    select clob_col into l_clob from lobs_tab where id = 1;

    l_file := utl_file.fopen('FILES_DIR', 'clob_col2.txt', 'wb', 32767);

    while l_pos < length(l_clob) loop
        l_buffer := substr(l_clob, l_pos, l_amount);
        utl_file.put_raw(l_file, utl_raw.cast_to_raw(l_buffer), true);
        l_pos := l_pos + l_amount;
    end loop;

    utl_file.fclose(l_file);
end;
/

Проверяю результат:

[oracle@tsuki files]$ ls -l
-rw-r--r-- 1 oracle oinstall 1000009 May 20 20:54 clob_col1.txt
-rw-r--r-- 1 oracle oinstall 1000009 May 20 21:07 clob_col2.txt

Для обратной операции - загрузки содержимого файла в LOB - также можно воспользоваться пакетами UTL_FILE и DBMS_LOB, циклически читая данные из файла и записывая в LOB. Но интереснее сделать это с помощью типа данных BFILE.

Тип данных BFILE содержит указатель на внешний файл, который

• состоит из двух частей: имя директории и имя файла,
• создается с помощью функции BFILENAME, например, BFILENMAE('FILES_DIR', 'novel.txt'),
• может указывать на несуществующий файл.

Пакет DBMS_LOB позволяет читать содержимое BFILE, но не изменять его. Чтение из BFILE возвращает двоичные данные как тип данных RAW. Для преобразования в VARCHAR2, при необходимости, используется функция UTL_RAW.CAST_TO_VARCHAR2.

Пример чтения BFILE и записи во временный BLOB:

declare
    l_bfile  bfile;
    l_bfile_len pls_integer;
    l_blob   blob;
    l_buffer raw(32767);
    l_amount pls_integer := 32767;
    l_pos    pls_integer := 1;
begin
    l_bfile := bfilename('FILES_DIR', 'clob_col2.txt');
    dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
    l_bfile_len := dbms_lob.getlength(l_bfile);

    dbms_lob.createtemporary(l_blob, TRUE);

    while l_pos < l_bfile_len loop
        dbms_lob.read(l_bfile, l_amount, l_pos, l_buffer);
        dbms_lob.writeappend(l_blob, l_amount, l_buffer);
        l_pos := l_pos + l_amount;
    end loop;

    dbms_output.put_line(l_bfile_len || ' : ' || dbms_lob.getlength(l_blob));

    dbms_lob.freetemporary(l_blob);
    dbms_lob.close(l_bfile);
end;
/

1000009 : 1000009

PL/SQL procedure successfully completed

В примере BFILE открывается и закрывается с помощью OPEN и CLOSE, аналогично внутренним LOB'ам. Также, пакет DBMS_LOB содержит несколько процедур и функций специально для работы с объектами BFILE:

Процедура / Функция	Что делает
FILEGETNAME	возвращает имя директории и файла BFILE
FILEEXISTS	проверяет, что файл BFILE существует
FILEOPEN	открывает файл BFILE
FILEISOPEN	проверяет, что файл BFILE открыт
FILECLOSE	закрывает файл BFILE
FILECLOSEALL	закрывает все открытые в сеансе файлы BFILE

Вместо чтения BFILE по частям пакет DBMS_LOB позволяет

• с помощью LOADCLOBFROMFILE загрузить содержимое BFILE в CLOB, указав, какую кодировку (набор символов) имеет содержимое,
• с помощью LOADBLOBFROMFILE загрузить содержимое BFILE в BLOB.

Пример загрузки текстового файла во временный CLOB (аналогично можно загрузить и в постоянный CLOB):

declare
    l_clob clob;
    l_bfile bfile;
    l_dest_offset number;
    l_src_offset number;
    l_lang_ctx number := 0; -- the default
    l_warning number;
begin
    l_bfile := bfilename('FILES_DIR', 'clob_col2.txt');

    dbms_lob.createtemporary(l_clob, true);
    l_dest_offset := 1; -- с начала
    l_src_offset := 1;  -- с начала
    
    dbms_lob.fileopen(l_bfile);
    dbms_lob.loadclobfromfile(
        dest_lob    => l_clob,
        src_bfile   => l_bfile,
        amount      => dbms_lob.lobmaxsize,
        dest_offset => l_dest_offset,
        src_offset  => l_src_offset,
        -- database csid by default or nls_charset_id('AL32UTF8')
        bfile_csid  => 0, 
        lang_context => l_lang_ctx,
        warning     => l_warning
    );
    dbms_lob.fileclose(l_bfile);
    
    dbms_output.put_line(
        'src_offset : ' || l_src_offset ||
        '; dest_offset : ' || l_dest_offset ||
        '; warning : ' || l_warning
    );
    
    dbms_lob.freetemporary(l_clob);
end;
/

src_offset : 1000010; dest_offset : 1000001; warning : 0

PL/SQL procedure successfully completed

Значения src_offset и dest_offset отличаются, поскольку первое, для BFILE, выражено в байтах, а второе, для CLOB, выражено в символах. В файле и CLOB'е имеются девять двухбайтовых русских букв - напомню, их содержимое начинается с "привет, мир".

Приведу неполный список процедур и функций DBMS_LOB для чтения, анализа и изменения значений BLOB, CLOB и NCLOB:

Процедура / Функция	Что делает
APPEND	добавляет один LOB в конец другого
COPY	копирует все или часть содержимого LOB'а в другой LOB
ERASE	удаляет все или часть содержимого LOB'а
GETLENGTH	возвращает размер LOB'а
INSTR	ищет "подстроку" в LOB'е
ISOPEN	проверяет, открыт ли LOB
ISTEMPORARY	проверяет, временный ли LOB
READ	читает данные LOB'а
SUBSTR	получает "подстроку" из LOB'а
TRIM	сокращает размер LOB'а до указанного
WRITE	записывает данные в LOB
WRITEAPPEND	записывает данные в конец LOB'а

Следующий эксперимент покажет разницу между внутренними и внешними постоянными LOB'ами. Помещу в поле bfile_col таблицы lobs_tab объект BFILE и скопирую единственную строку таблицы во вторую строку:

update lobs_tab set
    bfile_col = bfilename('FILES_DIR', 'clob_col1.txt')
where id = 1;

insert into lobs_tab (
    id, clob_col, nclob_col, blob_col, bfile_col)
select 2, clob_col, nclob_col, blob_col, bfile_col
from lobs_tab
where id = 1;

Команда INSERT привела к тому, что значения bfile_col в обеих строках связаны с одним и тем же внешним файлом, и его изменение отразится на обоих значениях.

SQL> declare
  2      l_dir varchar2(50);
  3      l_file varchar2(50);
  4  begin
  5      for r in (select id, bfile_col from lobs_tab) loop
  6          dbms_lob.filegetname(r.bfile_col, l_dir, l_file);
  7          dbms_output.put_line(r.id || ' : ' || l_dir || ' : ' || l_file);
  8      end loop;
  9  end;
 10  /

1 : FILES_DIR : clob_col1.txt
2 : FILES_DIR : clob_col1.txt

PL/SQL procedure successfully completed

А вот значения столбцов clob_col, nclob_col и blob_col для строк 1 и 2 стали независимы - не только указатели на LOB, но и данные внутренних LOB'ов в LOB-сегментах были скопированы. Продемонстрирую их независимость, изменив значения clob_col и nclob_col для строки 2:

SQL> select clob_col, nclob_col from lobs_tab;

CLOB_COL                               NCLOB_COL
-------------------------------------- -----------------------------------------
привет, мир!!!!!!!!!!!!!!!!!!!!!!!!!!! hello, world)))))))))))))))))))))))))))))
привет, мир!!!!!!!!!!!!!!!!!!!!!!!!!!! hello, world)))))))))))))))))))))))))))))

SQL> declare
  2      l_clob clob;
  3  begin
  4      select clob_col into l_clob from lobs_tab where id = 2;
  5      dbms_lob.write(
  6          lob_loc => l_clob,
  7          amount  => 11, -- chars
  8          offset  => 1,
  9          buffer  => 'ПРИВЕТ, МИР'
 10      );
 11  end;
 12  /
PL/SQL procedure successfully completed

SQL> update lobs_tab set
  2      nclob_col = upper(nclob_col)
  3  where id = 2;
1 row updated

SQL> select clob_col, nclob_col from lobs_tab;

CLOB_COL                               NCLOB_COL
-------------------------------------- -----------------------------------------
привет, мир!!!!!!!!!!!!!!!!!!!!!!!!!!! hello, world)))))))))))))))))))))))))))))
ПРИВЕТ, МИР!!!!!!!!!!!!!!!!!!!!!!!!!!! HELLO, WORLD)))))))))))))))))))))))))))))

Аналогично, при присваивании BLOB и (N)CLOB переменных в PL/SQL мы получаем независимые копии LOB'ов:

SQL> declare
  2      c1 clob := 'hello world';
  3      c2 clob := c1;
  4  begin
  5      c1 := 'привет, мир';
  6      dbms_output.put_line(c1);
  7      dbms_output.put_line(c2);
  8  end;
  9  /

привет, мир
hello world

PL/SQL procedure successfully completed

Итак, мы на примерах рассмотрели работу с большими объектами в SQL и PL/SQL. Работа с большими объектами имеет и другой аспект - это технология SecureFiles, позволяющая, в частности, сжимать хранимые в LOB-сегментах данные, свести к минимуму их дублирование, шифровать эти данные. Но эта тема выходит за рамки данного очерка.

В заключение, удаляю следы экспериментов:

SQL> drop table lobs_tab;
Table dropped