Oracle преобразовать в clob

Обновлено: 04.07.2024

Здесь я хочу привести свой код, для работы с БД Oracle (Не вставил в предыдущий топик т.к. полилось много текста и НЛО послало процесс сохранения лесом).

Вообще CLOB(LOB\BLOB) довольно интересен. Работа с ним отличается от работы с другими объектами. Детальное описание вы так же можете посмотреть в MSDN.
Скажу лишь то что сделать что-то вроде:

OracleParameter someClob = new OracleParameter()
ParameterName = "some_clob" ,
Direction = ParameterDirection.Input,
OracleType = OracleType.Clob,
Value = clobData
>;

не выйдет, подход должен быть тоньше.

На сладкое

У программистов и дизайнеров, да и наверное у всех людей, есть общая черта, я называю её «кнопка MakeItPizdato». Т.е. получить при наименьших затратах, чего угодно, максимальную эффектиность\продуктивность\подставить по своему желанию.
Поэтому я написал свой код (критике буду только рад! но разумной критике ;) ), которым решил поделиться:

Работа с БД разбита на несколько частей:
Фабрика — отвечает за полную работу с БД

using System;
using System.Collections. Generic ;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using Core.Extensions;

public OracleFactory()
InitializeConnection();
>

public OracleFactory( String connectionString)
_connectionString = connectionString;
InitializeConnection();
>

private void InitializeConnection()
Commands = new Dictionary< String , OracleCommand>();
RollbackCommandNames = new List < string >();

try
_connection = new OracleConnection(_connectionString);
_connection.Open();
>
catch (OracleException oraEx)
Console .WriteLine( "Connection failed with message: " + oraEx.ToString());
>
>

OracleFactory()
Dispose( false );
>

public void Dispose()
Dispose( true );
GC.SuppressFinalize( this );
>

if (disposeManagedResources)
foreach ( String commandName in Commands.Keys)
Commands[commandName].Dispose();
>
//Dispose connection information
_connection.Close();
_connection.Dispose();
>

public OracleConnection Connection
get
return (_connection);
>
>

public Dictionary< String , OracleCommand> Commands
get ;
private set ;
>

public List < String > RollbackCommandNames
get ;
private set ;
>

/// <summary>
/// Last used command name
/// </summary>
/// <exception cref="System.ArgumentException"/>
public String LastCommandName
get
if (_lastCommandName == String .Empty)
throw new ArgumentException( "LastCommandName is not specified" );

return (_lastCommandName);
>
set
_lastCommandName = value ;
>
>

/// <summary>
/// Last used command
/// </summary>
public OracleCommand LastCommand
get
return (Commands[LastCommandName]);
>
>

public Object this [ String commandName, String parameterName]
get
if (!Commands.Keys.Contains(commandName))
throw new OracleFactoryException();

return (Commands[commandName].Parameters[parameterName].Value);
>
set
Commands[commandName].Parameters[parameterName].Value = value ;
>
>

public Object this [ String parameterName]
get
return ( this [LastCommandName, parameterName]);
>
set
this [LastCommandName, parameterName] = value ;
>
>

/// <summary>
/// Creates a new Oracle`s command (default for stored procedures)
/// </summary>
/// <exception cref="Core.DL.OracleManagerException">
/// </exception>
/// <param name="commandName">Oracle`s command name</param>
public void CreateCommand( String commandName)
CreateCommand(commandName, CommandType.StoredProcedure);
>

/// <summary>
/// Creates a custom Oracle`s command
/// </summary>
/// <exception cref="Core.DL.OracleManagerException">
/// </exception>
/// <param name="commandName">Oracle command name</param>
/// <param name="commandType">Oracle command type</param>
public void CreateCommand( String commandName, CommandType commandType)
if (Commands.Keys.Contains(commandName))
throw new OracleFactoryException();

public void ClearParameters()
LastCommand.Parameters.Clear();
>

public void AddParameter( String parameterName, OracleType parameterType, Object parameterValue)
AddParameter(LastCommandName, parameterName, parameterType, parameterValue, ParameterDirection.Input);
>

public void AddParameter( String parameterName, OracleType parameterType, Object parameterValue, ParameterDirection parameterDirection)
AddParameter(LastCommandName, parameterName, parameterType, parameterValue, parameterDirection);
>

public void AddParameter( String commandName, String parameterName, OracleType parameterType,
Object parameterValue, ParameterDirection parameterDirection)
if (!Commands.Keys.Contains(commandName))
throw new OracleFactoryException();

if (parameterType == OracleType.Clob)
parameterValue = OracleFactory.PrepareCLOB(Commands[commandName], parameterValue.ToString());

Commands[commandName].Parameters.Add( new OracleParameter(parameterName, parameterType)
Direction = parameterDirection,
Value = parameterValue ?? DBNull.Value
>);
>

public void ExecuteNonQuery()
ExecuteNonQuery(LastCommandName);
>

public OracleDataReader ExecuteReader()
return (ExecuteReader(LastCommandName));
>

public OracleDataReader ExecuteReader( String commandName)
OracleDataReader dataReader = Commands[commandName].ExecuteReader();

public void Rollback( String commandName)
if (!RollbackCommandNames.Contains(commandName))
RollbackCommandNames.Add(commandName);
>
>

public void Rollback()
Rollback(LastCommandName);
>

public static OracleLob PrepareCLOB(OracleCommand command, String data)
OracleLob clob = CreateLob(command, OracleType.Clob);
byte [] byteData = System.Text. Encoding .Unicode.GetBytes(data);

clob.BeginBatch(OracleLobOpenMode.ReadWrite);
clob.Write(byteData, 0, byteData.Length);
clob.EndBatch();

public static OracleLob CreateLob(OracleCommand command, OracleType lobType)
if (command.Transaction == null )
throw new OracleFactoryException( "Parameter command should contains transaction value" );

if (lobType != OracleType.Clob && lobType != OracleType.Blob)
throw new OracleFactoryException( "Thhis method is used only for creation LOB objects" );

//Prepare for creating LOB object
command.Parameters.Clear();

command.CommandType = CommandType.Text;
command.CommandText = String .Format( "declare xx ; begin dbms_lob.createtemporary(xx, false, 0); :tempLob := xx; end;" ,
lobType == OracleType.Clob ? "clob" : "blob" );
command.Parameters.Add( new OracleParameter( "tempLob" , lobType) < Direction = ParameterDirection.Output >);
command.ExecuteNonQuery();

OracleLob retLob = command.Parameters[ "tempLob" ].Value as OracleLob;

Я подсел на использование расширяющих методов (Extensions). А в рамках того что мне нужно было подготовить коллекцию параметров для использования с типами LOB (сохранить, а затем восстановить), я придумал такое расширение:

namespace Core.Extensions
public static class OracleParameterCollectionExtensions
public static void MakeClone( this OracleParameterCollection sourceCollection, OracleParameterCollection outCollection)
foreach (OracleParameter parameter in sourceCollection)
OracleParameter newParam = new OracleParameter()
Direction = parameter.Direction,
IsNullable = parameter.IsNullable,
OracleType = parameter.OracleType,
ParameterName = parameter.ParameterName,
Value = parameter.Value
>;

* This source code was highlighted with Source Code Highlighter .

Начав использовать данный код сегодня, вы бесплатно получаете + Exception!:

namespace TestApp.Core
public class OracleFactoryException : Exception
public OracleFactoryException()
>

public OracleFactoryException( String exMessage)
: base (exMessage)

Для меня телодвижений стало меньше, теперь добавление выглядит так:

public void AddConscript( string firstName, string lastName, int ages, float growth, string biograpby)
using (OracleFactory factory = new OracleFactory())
factory.CreateCommand( "ADD_CONscript" );

factory.AddParameter( "FirstNameIn" , OracleType.VarChar, firstName);
factory.AddParameter( "LastNameIn" , OracleType.VarChar, lastName);
factory.AddParameter( "AgesIn" , OracleType.Number, ages);
factory.AddParameter( "GrowthIn" , OracleType.Float, growth);
factory.AddParameter( "BiographyIn" , OracleType.Clob, biograpby);
>
>

Код упрощён, можно использовать тот же CLOB прозрачно относительно других типов.
Если вам необходимо получить значение определённого параметра следующим образом:

Те кто поковыряются в коде, найдут ещё много чего интересного ;).

Вы спросите почему именно CLOB? Я отвечу — мне другие LOB пока были не нужны, а код я точил под себя ;).

Как преобразовать переменную в другой тип в PL/SQL

В ходе выполнения программы PL/SQL часто возникает необходимость преобразования данных из одного типа в другой. Преобразование может выполняться двумя способами:

  • Неявно — поиск «оптимального варианта» поручается исполнительному ядру PL/SQL;
  • Явно — преобразование выполняется вызовом функции или соответствующим оператором PL/SQL.

В этом разделе мы сначала разберемся, как в PL/SQL выполняются неявные преобразования, а затем перейдем к изучению функций и операторов явного преобразования.

Неявное преобразование типов

Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.

Неявные преобразования типов, выполняемые PL/SQL

Рис. 1. Неявные преобразования типов, выполняемые PL/SQL

Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:

Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:

Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT . Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT работа программы будет нарушена.

Ограничения неявного преобразования

Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:

В PL/SQL нельзя преобразовать строку «abc» в число, поэтому при выполнении приведенного кода инициируется исключение VALUE_ERROR . Вы сами должны позаботиться о том, чтобы значение, для которого PL/SQL выполняет преобразование типов, могло быть конвертировано без ошибок.

Недостатки неявного преобразования

Неявное преобразование типов имеет ряд недостатков.

  • PL/SQL относится к языкам со статической типизацией. Неявные преобразования означают потерю некоторых преимуществ статической типизации — таких, как ясность и надежность кода.
  • Каждое неявное преобразование означает частичную потерю контроля над программой. Программист не выполняет его самостоятельно и никак им не управляет, а лишь предполагает, что оно будет выполнено и даст желаемый эффект. В этом есть элемент неопределенности — если компания Oracle изменит способ или условие выполнения преобразований, это может отразиться на программе.
  • Неявное преобразование типов в PL/SQL зависит от контекста. Оно может работать в одной программе и не работать в другой, хотя на первый взгляд код кажется одинаковым. Кроме того, результат преобразования типов не всегда соответствует ожиданиями программиста.
  • Программу легче читать и понять, если данные в ней преобразуются явно, поскольку при этом фиксируются различия между типами данных в разных таблицах или в таблице и коде. Исключая из программы скрытые действия, вы устраняете и потенциальную возможность ошибок.

Таким образом, в SQL и PL/SQL рекомендуется избегать неявного преобразования типов. Лучше пользоваться функциями, которые выполняют явное преобразование — это гарантирует, что результат преобразования будет точно соответствовать вашим ожиданиям.

Явное преобразование типов

Oracle предоставляет обширный набор функций и операторов, с помощью которых можно выполнить преобразование типов данных в SQL и PL/SQL. Их полный список приведен в табл. 1. Большая часть функций описывается в других главах книги (для них в последнем столбце указан номер главы).

Таблица 1. Функции преобразования типов в PL/SQL

Функция Выполняемое преобразование
ASCIISTR Строку из любого набора символов в строку ASCII из набора символов базы данных
CAST Одно значение встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию. Этот способ может использоваться вместо традиционных функций (таких, как TO_DATE)
CHARTOROWID Строку в значение типа ROWID
CONVERT Строку из одного набора символов в другой
FROM_TZ В значение типа TIMESTAMP добавляет информацию о часовом поясе, преобразуя его тем самым в значение типа TIMESTAMP WITH TIME ZONE
HEXTORAW Значение из шестнадцатеричной системы в значение типа RAW
MULTISET Таблицу базы данных в коллекцию
NUMTODSINTERVAL Число (или числовое выражение) в литерал INTERVAL DAY TO SECOND
NUMTOYMINTERVAL Число (или числовое выражение) в литерал INTERVAL YEAR TO MONTH
RAWTOHEX, RAWTONHEX Значение типа RAW в шестнадцатеричный формат
REFTOHEX Значение типа REF в символьную строку, содержащую его шестнадцатеричное представление
ROWIDTOCHAR, ROWIDTONCHAR Двоичное значение типа ROWID в символьную строку
TABLE Коллекцию в таблицу базы данных; по своему действию обратна функции MULTISET
THE Значение столбца в строку виртуальной таблицы базы данных
TO_BINARY_FLOAT Число или строку в BINARY_FLOAT
TO_BINARY_DOUBLE Число или строку в BINARY_DOUBLE
TO_CHAR, TO_NCHAR (числовая версия) Число в строку (VARCHAR2 или NVARCHAR2 соответственно)
TO_CHAR, TO_NCHAR (версия для дат) Дату в строку
TO_CHAR, TO_NCHAR (символьная версия) Данные из набора символов базы данных в набор символов национального языка
TO_BLOB Значение типа RAW в BLOB
TO_CLOB, TO_NCLOB Значение типа VARCHAR2, NVARCHAR2 или NCLOB в CLOB (либо NCLOB)
TO_DATE Строку в дату
TO_DSINTERVAL Символьную строку типа CHAR, VARCHAR2, NCHAR или NVARCHAR2 в тип INTERVAL DAY TO SECOND
TO_LOB Значение типа LONG в LOB
TO_MULTI_BYTE Однобайтовые символы исходной строки в их многобайтовые эквиваленты (если это возможно)
TO_NUMBER Строку или число (например, BINARY_FLOAT) в NUMBER
TO_RAW Значение типа BLOB в RAW
TO_SINGLE_BYTE Многобайтовые символы исходной строки в соответствующие однобайтовые символы
TO_TIMESTAMP Символьную строку в значение типа TIMESTAMP
TO_TIMESTAMP_TZ Символьную строку в значение типа TO_TIMESTAMP_TZ
TO_YMINTERVAL Символьную строку типа CHAR, VARCHAR2, NCHAR или NVARCHAR2 в значение типа INTERVAL YEAR TO MONTH
TRANSLATE . USING Текст в набор символов, заданный для преобразования набора символов базы данных в национальный набор символов
UNISTR Строку произвольного набора символов в Юникод

Конвертация в другой тип данных в PL/SQL

Функция CHARTOROWID

Преобразует строку типа CHAR или VARCHAR2 в значение типа ROWID . Синтаксис функции:

Для успешного преобразования функцией CHARTOROWID строка должна состоять из 18 символов в формате:

где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке. Все четыре компонента задаются в формате Base64. Если исходная строка не соответствует этому формату, инициируется исключение VALUE_ERROR .

Функция CAST

Функция CAST является очень удобным и гибким механизмом преобразования данных. Она преобразует значение любого (или почти любого) встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию, и скорее всего, будет знакома всем программистам с опытом работы на объектно-ориентированных языках.

С помощью функции CAST можно преобразовать неименованное выражение (число, дату, NULL и даже результат подзапроса) или именованную коллекцию (например, вложенную таблицу) в тип данных или именованную коллекцию совместимого типа. Допустимые преобразования между встроенными типами данных показаны на рис. 2. Необходимо соблюдать следующие правила:

  • не допускается преобразование типов данных LONG , LONG RAW , любых типов данных LOB и типов, специфических для Oracle;
  • обозначению « DATE » на рисунке соответствуют типы данных DATE , TIMESTAMP , TIMESTAMP WITH TIMEZONE , INTERVAL DAY TO SECOND и INTERVAL YEAR TO MONTH ;
  • для преобразования именованной коллекции определенного типа в именованную коллекцию другого типа нужно, чтобы элементы обеих коллекций имели одинаковый тип;

Преобразование встроенных типов данных PL/SQL

Рис. 2. Преобразование встроенных типов данных PL/SQL

  • тип UROWID не может быть преобразован в ROWID , если UROWID содержит значение ROWID индекс-таблицы.

Ниже приведен пример использования функции CAST для преобразования скалярных типов данных. Ее вызов может быть включен в SQL-команду:

Также возможен вызов в синтаксисе PL/SQL:

Намного более интересное применение CAST встречается при работе с коллекциями PL/SQL (вложенными таблицами и VARRAY), поскольку эта функция позволяет преобразовывать коллекцию из одного типа в другой. Кроме того, CAST может использоваться для работы (из инструкций SQL) с коллекцией, объявленной как переменная PL/SQL.

Обе темы подробно рассматриваются в главе 12, а следующий пример дает некоторое представление о синтаксисе и возможностях преобразования. Сначала мы создаем два типа вложенных таблиц и одну реляционную таблицу:

Далее пишется программа, которая связывает данные из таблицы favorite_authors с содержимым вложенной таблицы, объявленной и заполненной в другой программе. Рассмотрим следующий блок:

В строках 2 и 3 объявляется локальная вложенная таблица, заполняемая именами нескольких популярных авторов. В строках 7–11 с помощью оператора UNION объединяются строки таблиц favorite_authors и scifi_favorites. Для этого вложенная таблица scifi_favorites (локальная и не видимая для ядра SQL) преобразуется с использованием функции CAST в коллекцию типа names_t. Такое преобразование возможно благодаря совместимости их типов данных. После преобразования вызов команды TABLE сообщает ядру SQL, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:

Функция CONVERT

Преобразует строку из одного набора символов в другой. Синтаксис функции:

Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.

Функция CONVERT не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.

Функция HEXTORAW

Преобразует шестнадцатеричную строку типа CHAR или VARCHAR2 в значение типа RAW . Синтаксис функции HEXTORAW :

Функция RAWTOHEX

Преобразует значение типа RAW в шестнадцатеричную строку типа VARCHAR2 . Синтаксис функции RAWTOHEX :

Функция RAWTOHEX всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW поддерживает оба типа строк.

Функция ROWIDTOCHAR

Преобразует двоичное значение типа ROWID в строку типа VARCHAR2 . Синтаксис функции ROWIDTOCHAR :

Возвращаемая функцией строка имеет следующий формат:

где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке PL/SQL. Все четыре компонента задаются в формате Base64. Пример:

Программное обеспечение базы данных Oracle предлагает несколько различных типов данных для хранения данных символьных строк: char, varchar2 и clob. Поля типа данных char имеют фиксированную длину, поля varchar2 имеют переменную длину, а поля clob позволяют хранить до 128 ТБ больших блоков текста. В то время как поля varchar2 имеют ограничение в 4000 символов, вы можете преобразовать до 4000 символов из поля clob в поле формата varchar2 с помощью функции Oracle dbms_lob.substr. Функция извлекает подстроку из поля clob, эффективно превращая ее в поле varchar2.


Функция substr преобразует данные Oracle clob в короткий текст strings.credit: Stockbyte / Stockbyte / Getty Images

Шаг 1

Запустите программу Oracle SQL * Plus.

Шаг 2

Введите следующий оператор в SQL * Plus:

SELECT dbms_lob.substr (clob_field, 4000, 1) ОТ my_table;

ВЫБЕРИТЕ dbms_lob.substr (budget_memo, 4000, 1) ОТ бюджетов;

Шаг 3

Нажмите Ввод." SQL * Plus выполняет оператор.

Преимущества баз данных Oracle

Преимущества баз данных Oracle

Oracle имеет много функций, которые делают его популярным в деловом мире. Новые версии баз данных Oracle выпускаются с новыми и улучшенными функциями, предназначенными для бизнеса и .

Как подключиться к Oracle с помощью SQL Management Studio

Как подключиться к Oracle с помощью SQL Management Studio

Microsoft предоставляет SQL Server Management Studio (SSMS) в качестве интерфейса к своим базам данных SQL Server. Приложение SSMS позволяет пользователям управлять базами данных на сервере .

Преимущества и недостатки Oracle SQL

Преимущества и недостатки Oracle SQL

Язык структурированных запросов помогает вам управлять данными в реляционной базе данных. Реляционные базы данных используют систему таблиц для хранения отдельных частей информации. Они известны как .


Рассматривается использование следующего поколения LOB-объектов: SecureFiles (сохраняемые файлы), которые объединяют лучшее качества внешних файлов (external files) и LOB-объектов базы данных для хранения неструктурированных данных, которые допускают также шифрование, сжатие, однократное хранение нескольких одинаковых объектов (deduplication – дедупликация) и т.д.

Внутренние двоичные объекты (BLOB) базы данных и файлы операционной системы

Что находится в базе данных Oracle? В большинстве случаев это - данные, хранимые в виде, пригодном для простого преобразования в некий определенный шаблон заданного типа данных: имена пользователей, остатки на счетах, коды состояний и т.п. Но со столь же большой долей вероятности может понадобиться хранение данных в неструктурированном или полуструктурированном виде. Например, изображения, текстовые документы, таблицы, XML-файлы и так далее. Как хранятся эти типы данных?

Обычно используется два подхода: такие данные хранятся в базе данных как LOB-объектов (BLOB для двоичных и CLOB для символьных данных) или в виде файлов операционной системы, а в базе данных хранятся на них ссылки.

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

Существуют также средства, которые позволяют распознавать такие структуры (patterns) в файлах и удалять повторяющиеся элементы для более эффективного хранения. Но они не являются частью базы данных и не имеют ее свойств. Резервное копирование таких файлов не выполняется, не доступны тонкости политики безопасности, эти файлы не являются частью транзакций, то есть целостность данных - первичная концепция базы данных Oracle - на них не распространяется.

Как же воспользоваться преимуществами каждого подхода? В Oracle Database 11g имеется ответ – SecureFiles (сохраняемые файлы), совершенно новая инфраструктура в базе данных, которая объединяет лучшие особенности LOB-объектов базы данных и файлов операционной системы. Давайте рассмотрим, как они работают. (Кстати, традиционные LOB-объекты до сих пор доступны в виде так называемых BasicFiles).

Практический пример

Представляется, что лучше всего представить концепцию SecureFiles с помощью простого примера. Предположим, вы разрабатываете систему документооборота, в которой хотите поместить в таблицу копии договоров. Обычно отсканированные документы представляют собой не текстовые, а PDF-файлы, а некоторые - документы MS Word или даже отсканированные рисунки. Это отличный пример для использования BLOB-объектов, так как такой столбец таблицы должен содержать двоичные данные.

Традиционное до Oracle Database 11g определение таблицы было бы следующим:

Реальные файлы хранятся в двоичном формате в столбце ORIG_FILE. Другие параметры показывают, что LOB-объект не должен кешироваться и журналироваться в операциях, что он хранится в строке таблицы, имеет размер порции (chunk) 4 КБ, и lob-сегмент находится в табличном пространстве USERS. Поскольку это явно не определено, LOB-объекты хранятся в Oracle Database 11g в общепринятом формате (BasicFiles).

Если надо хранить LOB-объект в виде SecureFile, то все, что необходимо сделать, это при создании таблицы дописать фразу store as securefile, как показано ниже:

Для того чтобы создать LOB-объект в виде SecureFile, необходимо выполнить два условия, причем оба выполняются по умолчанию (так что можно быть спокойным).

Параметр инициализации db_securefile должен быть установлен в permitted (значение по умолчанию). Я объясню, что это за параметр позднее.

Созданное табличное пространство, где размещаются сохраняемые (securefile) файлы, должно быть Automatic Segment Space Management (ASSM). ASSM - режим по умолчанию при создании табличного пространства в Oracle Database 11g, поэтому он уже установлен для табличного пространства. Если это всё же не так, тогда необходимо размещать SecureFiles в другом табличном ASSM-пространстве.

После того, как таблица создана, можно загружать данные тем же способом, как и обычные (до 11g) LOB-объекты (BasicFile). Не надо изменять приложения и не надо запоминать какой-то специальный синтаксис.

Вот пример небольшой программы, которая загружает данные в таблицу:

Эта программа 100 раз загружает файл contract.pdf в 100 строк таблицы. Заранее надо иметь определенный объект типа directory, названный SECFILE, определяющий директорию операционной системы, где расположен файл contract.pdf. Ниже приводится пример, в котором файл contract.pdf расположен в директории /opt/oracle.

Один раз сохранив LOB-объект как SecureFile, вы получаете множество возможностей для выполнения оптимальных действий. Приведем несколько примеров этих весьма полезных возможностей.

Однократное хранение нескольких одинаковых объектов (deduplication)

Дедупликация, вероятно, самая яркая возможность SecureFiles, поскольку из всех преимуществ файлов операционной системы перед внутренними BLOB-объектами она наиболее востребована. Допустим, в таблице хранится пять записей, каждая с BLOB-объектом. Три из них одинаковы. Если была бы возможность однократного хранения BLOB-объекта и размещения ссылки на него в других двух записях, это существенно бы уменьшило занимаемое место. Это возможно для файлов операционной системы, но не для LOB-объектов в Oracle Database 10g. Для SecureFile это легко реализуется с помощью свойства дедупликации. Его можно определить при создании таблицы или позднее:

В процессе дедупликации СУБД хеширует значения столбцов в каждой строке и сравнивает хеш-значения друг с другом. Если хеш-значения совпадают, то сохраняются они, а не исходный BLOB-объект. Когда добавляется новая запись, то вычисляется хеш, и если он совпадает со значением в другой строке, то сохраняется хеш-значение, в противном случае сохраняется реальное значение.

Теперь давайте определим объем пространства, сохраненного в результате дедупликации.

Определение занимаемого пространства LOB-сегментом можно выполнить с помощью пакета DBMS_SPACE. Ниже демонстрируется пример программы, показывающей занимаемое место:

Этот скрипт показывает объем занимаемого LOB-объектами пространства. Вот результат до процесса дедупликации:

и после дедупликации:

В приведенных данных достаточно сравнить только одну метрику used_bytes, которая показывает точное количество байтов, занимаемых LOB-столбцом. До дедупликации он занимал 4,923,392 байтов, или около 5 Мбайт, а уменьшился до 57,344 байт, что составляет около 57 Кбайт, то есть всего лишь около одного процента от исходного размера. Так получилось потому, что процесс дедупликации 100 раз нашел строки с одним и тем же значением (помните, мы в LOB-столбец всех строк поместили одно и тоже значение) и сохранил его только в одной строке, а в остальных – только указатели.

Можно обратить результат дедупликации:

Посмотрим после этого снова на занимаемое место:

Мы видим, что значение USED_BYTES выросло до исходной величины около 5 Мбайт.

Сжатие (Compression)

Другой возможностью SecureFiles является сжатие. Можно сжимать содержимое LOB-объектов, используя следующие SQL-предложение:

Сейчас, если выполнить PL/SQL-блок, вычисляющий объем:

то увидим, что значение used_bytes сейчас равно 1,646,592, или около 1,5 MB, что существенно меньше 5 MB.

Сжатие отличается от дедупликации. Сжатие происходит внутри LOB-столбца, в строке – каждый LOB-объект сжимается независимо от других. При дедупликации проверяются все строки, повторные значения удаляются и заменяются указателями. Если есть две существенно отличающиеся строки, дедупликация не уменьшит занимаемый размер, а сжатие может оптимизировать место, занимаемое LOB-объектами. Можно как сжимать, так и дедуплицировать данные.

Сжатие требует работы CPU, поэтому в зависимости от количества сжимаемых данных, сжатие может потерять смысл. Например, имеется много изображений в формате JPEG, которые уже сжаты, поэтому дальнейшее сжатие не уменьшит занимаемое место. С другой стороны, если CLOB-объектом является XML-документ, то сжатие может получиться существенным. Процесс SecureFiles-сжатия автоматически определяет, сжимаются ли данные или только расходуется процессорное время.

Индексы Oracle Text могут быть созданы как LOB-ы сжатых SecureFiles (compressed SecureFiles LOBs). Это главное преимущество хранения неструктурированных данных в базе данных Oracle по сравнению сжатыми файлами файловой системы.

Так же отметим, что сжатие LOB-объектов не зависит от сжатия таблиц. Если сжимается таблица CONTRACTS_SEC, то её LOB-объекты не сжимаются. Сжатие LOB-объектов будет происходить только при использовании приведенного выше SQL-предложения.

В Oracle Database 11g R2 есть третья возможность сжатия в дополнение к HIGH и MEDIUM: LOW. Как следует из её имени, в этом случае коэффициент сжатия минимальный, но намного меньше потребление ресурсов CPU, а также более высокая скорость процесса. В этом случае используется блок-базируемое сжатие без потерь (block-based lossless compression), подобное быстрому алгоритму LempelZivOberhumer (LZO) .

Рассмотрим пример LOW-сжатия таблицы, содержащей SecureFiles:

Если опустить фразу LOW, то по умолчанию сжатие будет выполнено в варианте MEDIUM. LOW-сжатие можно указать не только при создании таблицы; также можно применить LOW-сжатие, чтобы изменить существующий столбец.

Рассмотрим пример с этой же таблицей и столбцом. Для начала мы изменим столбец как несжатый:

Далее установим для столбца low-сжатие:

Шифрование (Encryption)

Перед проведением шифрования необходимо установить крипто-блокнот (encryption wallet). (Полное описание encryption wallet можно найти в Oracle Magazine в моей статье «Transparent Data Encryption»

Кратко перечислим основные действия:

  1. Установить, если еще не задан, в файле sqlnet.ora параметр, определяющий расположение крипто-блокнота: Директория /opt/oracle/orawall уже должна существовать, в противном случае ее необходимо создать.
  2. Создать крипто-блокнот: Это предложение создает крипто-блокнот с паролем mypass и открывает его.
  3. Предыдущие два шага выполняются только один раз. После того, как крипто-блокнот создан и открыт, он остается открытым, пока работает база данных (до тех пор, пока она полностью не завершит работу). Если база данных перезапускается, необходимо открыть wallet предложением:

Если LOB-столбец SecureFile показан как зашифрованный, то шифруются все значения этого столбца во всех строках этой таблицы. После шифрования нельзя использовать обычный (Conventional Export or Import) экспорт и импорт этой таблицы, а необходимо использовать утилиту Data Pump.

Кеширование (Caching)

Возможность кеширования – одно из преимуществ хранения неструктурированных данных в файлах операционной системы, а не во внутренних объектах базы данных. Файлы могут кешироваться в файловых буферах операционной системы. Внутренние объекты базы данных могут кешироваться базой данных. Тем не менее, в некоторых случаях кеширование может снизить производительность. LOB-объекты обычно очень велики (отсюда и название Large OBjects) и, если они попадают в кеш, то большинство количество других блоков будут оттуда вытеснены, чтобы дать место поступившему LOB-объекту. LOB-объект, возможно, и не будет потом использоваться, но при его записи в кеш используемые блоки оттуда будут удалены. Поэтому в большинстве случаев всё-таки следует отключить кеширование LOB-объектов.

В приведенном примере для CONTRACTS_SEC использовалась фраза nocache для отключения кеширования. Для включения кеширования следует изменить таблицу:

Это действие включает кеширование LOB-объектов. Отметим, что это кеширование относится только к LOB-объектам. Оставшаяся часть таблицы кешируется по тем же правилам, что и любая другая таблица, вне зависимости от того, установлено ли кеширование LOB-объектов таблицы, или нет.

Преимущества от кеширования сильно зависят от приложений. В приложении, работающем с мелкими (thumbnail ) рисунками, производительность, возможно, при кешировании увеличиться. Тем не менее, для больших документов или рисунков кеширование лучше отключить. Securefiles позволяют это контролировать.

Журналирование (Logging)

Журналирование определяет, как изменения данных в LOB-объектах записываются в поток redo-журнала. По умолчанию для них установлено полное журналирование, как для всех остальных данных. Но, поскольку данные в LOB-объектах обычно велики, вероятно, захочется в некоторых случаях отключить журналирование. Фраза NOLOGING, использованная в предыдущем примере, делает именно это.

Для SecureFiles имеется еще одно значение для этой фразы — filesystem_like_logging— как показано ниже:

Отметим, что выделенная жирным шрифтом строка, включает ведение redo-журнала для метаданных LOB-объектов, но не для самих LOB-объектов. Это похоже на файловую систему, когда метаданные файлов записываются в журналы файловой системы. Эта возможность облегчает восстановление после сбоев.

Читайте также: