Oracle записать clob в blob
I will write a procedure clob_blob_proc which convert description column of test table of type clob to blob. Here is the procedure.
1)Create a Table with Clob Column:
——————
SQL> create table test(id number, description clob);
Table created.
2)Insert a Value in it:
———————
SQL> insert into test values(1,’Hi’);
Commit complete.
3)Add a Blob Column:
——————-
SQL> alter table test add description1 BLOB;
Table altered.
4)Run clob_blob_proc Procedure.
—————————-
SQL> create or replace procedure clob_blob_proc is
v_clob Clob;
v_blob Blob;
v_in Pls_Integer := 1;
v_out Pls_Integer := 1;
v_lang Pls_Integer := 0;
v_warning Pls_Integer := 0;
v_id number(10);
begin
for num in ( select id,description from test)
loop
v_id:=num.id ;
if num.description is null then v_blob:=null;
else v_clob:=num.description;
v_in:=1;
v_out:=1;
dbms_lob.createtemporary(v_blob,TRUE);
DBMS_LOB.convertToBlob(v_blob,v_clob,DBMS_lob.getlength(v_clob),
v_in,v_out,DBMS_LOB.default_csid,v_lang,v_warning);
end if;
update test set description1=v_blob where />end loop;
commit;
end;
/
SQL> exec tg_task_proc
PL/SQL procedure successfully completed.
SQL> desc test;
Name Null? Type
—————————————– ——– —————————-
ID NUMBER
DESCRIPTION CLOB
DESCRIPTION1 BLOB
5) Drop Old Column and Rename New Column:
———————————————–
SQL> alter table test drop column description;
Table altered.
SQL> desc test;
Name Null? Type
—————————————– ——– —————————-
ID NUMBER
DESCRIPTION1 BLOB
SQL> alter table test rename column description1 to description;
Table altered.
6)Test the Result:
————————-
SQL> desc test;
Name Null? Type
—————————————– ——– —————————-
ID NUMBER
DESCRIPTION BLOB
SQL> select id, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(description,4000,1)) description from test;
vCharset number;
vWarning number;
vErrCode number;
vInputLength number;
vCurrentPlace number := 1;
vPieceMaxSize number := 1000; -- the max size of each piece
vIndex number := 0;
vLangContext number := DBMS_LOB.default_lang_ctx;
vPiece varchar2(1000);
vErrMessage varchar2(250);
cursor cCursor is
select id
, col_clob
, col_blob
from test_table;
SELECT nls_charset_id('AL32UTF8')
INTO vCharset
FROM dual;
for vCursor in cCursor
loop
vIndex := vIndex + 1;
vCurrentPlace := 1;
vBlob := vCursor.col_blob;
vClob := vCursor.col_clob;
-- find the lenght of the clob
vInputLength := DBMS_LOB.getLength(vClob);
if (vInputLength > 0) then
-- loop through each piece
loop
-- get the next piece and add it to the clob
vPiece := DBMS_LOB.substr(vClob, vPieceMaxSize, vCurrentPlace);
-- append this piece to the BLOB
DBMS_LOB.writeappend(vBlob, length(vPiece)/2, HEXTORAW(vPiece));
vCurrentPlace := vCurrentPlace + vPieceMaxSize ;
EXIT
WHEN vInputLength < vCurrentPlace;
end loop;
end if;
end loop;
exception
when OTHERS then
vErrCode := SQLCODE;
vErrMessage := substr(SQLERRM, 1, 250);
SYSTEMTECHNIK.Logging.writeLog('CLOB2BLOB', 'ORACLE-Exception in : ', null, null, vErrCode, vErrMessage);
SYSTEMTECHNIK.Logging.writeLog('CLOB2BLOB', 'Prozedur mit Fehler beendet');
null;
As a matter of fact the procedure works fine without any error - and the processing of each record takes some time. At least that shows me that something is happening at all :-)
But when I run this query afterwards
select col_clob, length(clob), col_blob length(col_blob)
from test_table;
only just a few columns of col_blob are filled with something, havaing a length > 0.
Pretty strange to me, but I currently don't know why the columns of col_blob don't get filled with anything.
I am trying to convert clob into blob through following code.
/*****************************************/
CREATE OR REPLACE FUNCTION clob_to_blob (clob_in IN CLOB)
RETURN BLOB
AS
v_blob BLOB;
v_varchar RAW(32767);
v_start BINARY_INTEGER := 1;
v_buffer BINARY_INTEGER := 32767;
BEGIN
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(clob_in) / v_buffer)
LOOP
v_varchar := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(clob_in, v_buffer, v_start)) ;
DBMS_OUTPUT.PUT_LINE('DATA :' ||DBMS_LOB.SUBSTR(clob_in, v_buffer, v_start));
DBMS_OUTPUT.PUT_LINE(' V_VARCHAR :'|| v_VARCHAR);
DBMS_OUTPUT.PUT_LINE(' V_VARCHAR LENGTH :'|| LENGTH(v_VARCHAR));
DBMS_LOB.WRITEAPPEND(v_blob, LENGTH(v_VARCHAR), v_varchar);
DBMS_OUTPUT.PUT_LINE('after append');
v_start := v_start + v_buffer;
END LOOP;
declare
var clob;
begin
var:='3433534534de';
testblob(var);
end;
It gives me following error:
declare
*
ERROR at line 1:
ORA-21560: argument 2 is null, invalid, or out of range
i don't know whats the prob?
thanx
Comments
The name of the variable v_varchar is misleading.
It contains the RAW data, therefore you need to use utl.raw.length to retrieve the correct length value
The error in your calling code was not reproducible, because you didn't post the code of the TESTLOB procedure.
I created the function clob_to_blob as you suggested,
then I tried to:
select clob_to_blob(dbms_xmlgen.getXML('select 1 from dual')) from dual
and got the following error:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 826
ORA-06512: at "VP_SYSTEM.CLOB_TO_BLOB", line 19
It is quite unusual to expect an answer to a 3 year old post
But if you are on 10g I would propose to revert to dbms_lob.convertToBlob.
dbms_lob.writeappend expects a real lob locator, which you can only get by selecting from a lob column.
create table blobtab (b blob);
insert into blobtab values (empty_blob());
CREATE OR REPLACE FUNCTION clob_to_blob (clob_in IN CLOB)
RETURN BLOB
AS
v_blob blob;
v_varchar RAW(32767);
v_start BINARY_INTEGER := 1;
v_buffer BINARY_INTEGER := 32767;
BEGIN
select b into v_blob from blobtab;
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(clob_in) / v_buffer)
LOOP
v_varchar := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(clob_in, v_buffer, v_start)) ;
DBMS_OUTPUT.PUT_LINE(' DATA :' || DBMS_LOB.SUBSTR(clob_in, v_buffer, v_start));
DBMS_OUTPUT.PUT_LINE(' V_VARCHAR :' || v_VARCHAR);
DBMS_OUTPUT.PUT_LINE(' V_VARCHAR LENGTH :' || utl_raw.LENGTH(v_VARCHAR));
DBMS_LOB.WRITEAPPEND(v_blob, utl_raw.LENGTH(v_VARCHAR), v_varchar);
DBMS_OUTPUT.PUT_LINE('after append');
v_start := v_start + v_buffer;
END LOOP;
declare
blobvar blob;
begin
blobvar := clob_to_blob(dbms_xmlgen.getXML('select 1 from dual'));
end;
/
Для работы с данными большого объема СУБД 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 32 -1 байт и с BFILE файлами размером до 2 64 -1 байт.
Для работы с LOB'ами cоздам таблицу со столбцами соответствующих типов:
Вместе с таблицей были созданы сегменты для хранения больших объектов:
Для столбца типа BFILE отдельный сегмент не создан - ведь данные этого типа хранятся во внешних файлах.
Значение типа LOB может быть
- NULL - неинициализировано, не содержит указателя на LOB,
- пустым (empty) - указатель на LOB указывает в никуда,
- непустым - указатель на LOB указывает на данные LOB'а.
Пустые LOB значения создаются функциями EMPTY_CLOB и EMPTY_BLOB :
Последний запрос демонстрирует два способа проверить, является ли LOB пустым. Запрос использует пакет DBMS_LOB , содержащий процедуры и функции для работы с LOB'ами.
Начиная с версии Oracle 9i в SQL и PL/SQL поддерживается неявная конвертация между (N)CLOB и VARCHAR2, что позволяет манипулировать значениями в (N)CLOB столбцах и переменных так, как будто это значения типа VARCHAR2:
Как видим, функции и операторы, работающие с VARCHAR2, перегружены для типа (N)CLOB! При этом преодолеваются ограничения в 4000 символов, свойственные SQL типу VARCHAR2:
А вот операторы сравнения для (N)CLOB работают только в PL/SQL и не работают в SQL:
Выше я воспользовался функциями 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 ограничены. Например:
Зато в PL/SQL работают явная и неявная конвертации между BLOB и RAW:
Рассмотренные возможности по работе со значениями LOB как с VARCHAR2 получили название SQL семантика для LOB'ов (SQL semаntics for LOBs). С их использованием связаны некоторые ограничения, как мы увидим ниже.
С точки зрения PL/SQL большие объекты делятся на:
- временные (temporary), время жизни которых не превышает сеанса работы с СУБД,
- постоянные (persistent), которые хранятся в базе данных или во внешнем файле.
- создаются либо с помощью 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 семантика для LOB'ов всегда порождает временные LOB'ы - это и есть то ограничение, о котором я упоминал выше. Неявное приведение VARCHAR2 к LOB (строка 7) или функция, перегруженная для (N)CLOB (строка 14), дают нам временные LOB'ы. Как только переменной PL/SQL, указывающей на постоянный LOB, присваивается временный LOB, переменная начинает указывать на временный LOB. А связь переменной с постоянным LOB'ом утрачивается.
Значение временного LOB'а можно сохранить в БД - и тем самым сделать постоянным - либо с помощью SQL либо, как уже упоминалось, с помощью DBMS_LOB.COPY . Продемонстрирую обе возможности:
Обратите внимание, что процедура DBMS_LOB.COPY заменила в постоянном NCLOB c3 только фрагмент, равный по размеру значению исходного NCLOB'а c2 . Как вариант, можно было перед копированием очистить LOB назначения с помощью DBMS_LOB.ERASE .
Изменения внутренних постоянных LOB'ов (в отличие от внешних или временных) в СУБД Oracle подчиняются транзакциям. Убедимся в этом, отменив только что сделанные изменения:
Типичный алгоритм для чтения или изменения постоянного LOB'а с помощью PL/SQL таков:
- Извлечь указатель на LOB из столбца таблицы с помощью SELECT .
- Открыть большой объект с помощью DBMS_LOB.OPEN .
- Получить оптимальный размер фрагмента для чтения (записи) LOB с помощью DBMS_LOB.GETCHUNKSIZE
- Получить размер LOB'а в байтах (для BLOB и BFILE) или символах (для CLOB и NCLOB) с помощью DBMS_LOB.GETLENGTH .
- Многократно вызывать DBMS_LOB.READ для чтения последовательных фрагментов LOB'а, пока не будут извлечены все данные
ИЛИ
многократно вызывать DBMS_LOB.WRITE , со смещением, или DBMS_LOB.WRITEAPPEND или иные процедуры DBMS_LOB для записи фрагментов данных. - Закрыть LOB с помощью DBMS_LOB.CLOSE .
В предыдущем примере с DBMS_LOB.COPY я не открывал и не закрывал постоянный LOB при помощи DBMS_LOB.OPEN и DBMS_LOB.CLOSE , однако, это стоит делать для улучшения производительности при изменениях больших объектов.
Приведу пример выгрузки данных из постоянного CLOB'а во внешний файл. Для доступа к внешним файлам потребуется создать директорию, например:
Следующий код выгружает содержимое столбца lobs_tab.clob_col в файл clob_col1.txt , используя пакет DBMS_LOB для чтения CLOB и пакет UTL_FILE для записи во внешний файл:
Альтернативно, можно выгрузить CLOB во внешний файл, пользуясь SQL семантикой для LOB и не прибегая к DBMS_LOB :
Для обратной операции - загрузки содержимого файла в 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:
В примере 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):
Значения 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 и скопирую единственную строку таблицы во вторую строку:
Команда INSERT привела к тому, что значения bfile_col в обеих строках связаны с одним и тем же внешним файлом, и его изменение отразится на обоих значениях.
А вот значения столбцов clob_col , nclob_col и blob_col для строк 1 и 2 стали независимы - не только указатели на LOB, но и данные внутренних LOB'ов в LOB-сегментах были скопированы. Продемонстрирую их независимость, изменив значения clob_col и nclob_col для строки 2:
Аналогично, при присваивании BLOB и (N)CLOB переменных в PL/SQL мы получаем независимые копии LOB'ов:
Итак, мы на примерах рассмотрели работу с большими объектами в SQL и PL/SQL. Работа с большими объектами имеет и другой аспект - это технология SecureFiles, позволяющая, в частности, сжимать хранимые в LOB-сегментах данные, свести к минимуму их дублирование, шифровать эти данные. Но эта тема выходит за рамки данного очерка.
Step 1: Create plsql function blob_to_clob. It should return the CLOB .
CREATE OR REPLACE
FUNCTION blob_to_clob(
blob_in IN BLOB)
RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start pls_integer := 1;
v_buffer pls_integer := 32767;
BEGIN
dbms_lob.createtemporary(v_clob, TRUE);
FOR i IN 1..ceil(dbms_lob.getlength(blob_in) / v_buffer)
loop
v_varchar := utl_raw.cast_to_varchar2(dbms_lob.substr(blob_in, v_buffer, v_start));
dbms_lob.writeappend(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END loop;
RETURN v_clob;
END blob_to_clob;
Step 2: Call the function and test it .
Many ways to check this funtionality,
- Generate XML data from relational content
- Convert CLOB (XML data) to BLOB
- Convert BLOB to CLOB
- Print the CLOB
DECLARE
l_xml CLOB;
l_clob_to_blob BLOB;
l_blob_to_clob CLOB;
BEGIN
-- Generate XML
SELECT dbms_xmlgen.getxml('select * from emp') xml
INTO l_xml
FROM dual;
-- Convert CLOB to BLOB
l_clob_to_blob := clob_to_blob (l_xml);
-- Convert BLOB to CLOB
l_blob_to_clob := blob_to_clob(l_clob_to_blob);
-- Print the CLOB
print_clob_to_output (l_blob_to_clob);
END;
- Store BLOB in database
- Convert BLOB to CLOB
- Print the CLOB
CREATE TABLE store_blob_clob_files
(
ID NUMBER,
blob_content BLOB,
clob_content CLOB
);
DECLARE
l_blob BLOB;
l_clob_to_blob BLOB;
l_blob_to_clob CLOB;
BEGIN
-- Get BLOB file
SELECT blob_content
INTO l_blob
FROM store_blob_clob_files
WHERE >
-- Convert BLOB to CLOB
l_blob_to_clob := blob_to_clob(l_blob);
UPDATE store_blob_clob_files
SET clob_content = l_blob_to_clob
WHERE >
COMMIT;
-- Print the CLOB
print_clob_to_output (l_blob_to_clob);
END;
Читайте также: