02июн
Большие объекты в СУРБД ORACLE. Преобразование типов. Dbms_lob. Utl_raw
Очень часто пользователям нужно преобразовывать данные типа blob в clob; long row, long в blob, clob и тому подобное. Предлагаю рассмотреть некоторые преобразования типов:
Blob -> varchar2
Продемонстрирую все на примере. Создаём таблицу, вставляем строку с полем blob, загружаем в blob файл операционной системы.
SQL> drop table demo;
Table dropped.
SQL> drop sequence blob_seq;
Sequence dropped.
SQL> create table demo(id int primary key, theBlob blob );
Table created.
SQL> create or replace directory my_files as 'C:\hs';
Directory created.
SQL> create sequence blob_seq;
Sequence created.
SQL> create or replace
2 procedure dbst_load_a_file( p_dir_name in varchar2,p_file_name in varchar2 )
3 as
4 l_blob blob;
5 l_bfile bfile;
6 begin
7 insert into demo values ( blob_seq.nextval, empty_blob() )returning theBlob into l_Blob;
8 l_bfile := bfilename( p_dir_name, p_file_name );
9 dbms_lob.fileopen( l_bfile );
10 dbms_lob.loadfromfile( l_blob, l_bfile,dbms_lob.getlength( l_bfile ) );
11 dbms_lob.fileclose( l_bfile );
12 end;
13 /
Procedure created.
SQL> exec dbst_load_a_file( 'MY_FILES', 'my_test.txt' );
PL/SQL procedure successfully completed.
Посмотрим, что есть в нашем поле blob :
SQL> select dbms_lob.substr(theblob,20,1) from demo;
DBMS_LOB.SUBSTR(THEBLOB,20,1)
-------------------------------------------------------------
68656C6C6F2C20776F726C64
А теперь попробуем преобразовать в varchar2:
SQL> select utl_raw.cast_to_varchar2(dbms_lob.substr(theblob,20,1)) from demo;
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(THEBLOB,20,1))
--------------------------------------------------------------------------------
hello, world
Как все чудненько вышло!
Но есть одно большое замечание: если в переменной blob встречаются недопустимые символы, то нужно их поменять на нечто удобоваримое, иначе sqlplus откажется сотрудничать.
varchar2->Blob
А теперь посмотрим на обратную процедуру:
SQL> update demo
2 set theBlob = 'Hello World!!!!!'
3 where id = 1
4 /
set theBlob = 'Hello World!!!!!'
*
ERROR at line 2:
ORA-01465: invalid hex number
Попытка не увенчалась успехом – нужно сделать преобразование в шестнадцатеричный вид. Потому предлагаю сделать следующее:
SQL> update demo
2 set theBlob = utl_raw.cast_to_raw('Hello World!!!!!')
3 where id = 1
4 /
1 row updated.
Посмотрим, что же у нас получилось
SQL> select dbms_lob.getlength(theBlob) len from demo where id =1;
LEN
----------
16
SQL> select utl_raw.cast_to_varchar2(dbms_lob.substr(theblob,20,1)) from demo where id =1;
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(THEBLOB,20,1))
--------------------------------------------------------------------------------
Hello World!!!!!
SQL> select dbms_lob.substr(theBlob,40,1) raw_data from demo where id =1;
RAW_DATA
--------------------------------------------------------------------------------
48656C6C6F20576F726C642121212121
Все как мы и ожидали!