Oracle преобразовать в clob
The arcane LONG datatype has as you’re aware of many intrinsic limitations but unfortunately Oracle still uses quite a few LONG columns in the data dictionary. It’s beyond me why these haven’t been converted to CLOB a very long time ago (when Oracle deprecated usage of LONG). At the same time, Oracle only provides the following means of converting these LONG columns to eg CLOB :
- TO_LOB : A function that converts a LONG column to a CLOB . Can only be used in a SELECT list of a subquery in an INSERT statement. This means that the function is only useful if you’re converting the underlying table definition.
- ALTER TABLE MODIFY CLOB : This converts the column datatype and the data as well.
- Use DBMS_REDEFINITION to redefine the column datatype.
- Use Oracle Data Pump to convert the column datatype.
- Use CAST function to cast from LONG to CLOB : Unfortunately, CAST doesn’t support LONG .
- Write a PL/SQL function that performs a SELECT . INTO l FROM user_views , where l is a PL/SQL variable of type LONG . However, in PL/SQL, a LONG variable can only hold up to 32,760 characters (yes, not 32,767, ie different from VARCHAR2 ) so this will only solve the problem for small to medium sized views.
- Use dynamic SQL with DBMS_SQL.COLUMN_VALUE_LONG to access the LONG piecewise.
Object Type Collection
This is the implementation of the object type collection, using a nested table:
We need this object type collection for the pipelined table function as this pipes back a collection of object type instances back to the SQL engine — namely, one object type instance for each row in USER_VIEWS this function finds.
Ограничения неявного преобразования
Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:
В PL/SQL нельзя преобразовать строку «abc» в число, поэтому при выполнении приведенного кода инициируется исключение VALUE_ERROR . Вы сами должны позаботиться о том, чтобы значение, для которого PL/SQL выполняет преобразование типов, могло быть конвертировано без ошибок.
VARCHAR2 to CLOB
Let's see another case. Since the maximum length of VARCHAR2 is 4000 bytes for 11g and earlier releases, we'd like to convert a VARCHAR2 to CLOB (Character Large Object) so as to raise its length upper limit.
SQL> alter table hr.locations modify (city clob);
alter table hr.locations modify (city clob)
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype
Функция RAWTOHEX
Преобразует значение типа RAW в шестнадцатеричную строку типа VARCHAR2 . Синтаксис функции RAWTOHEX :
Функция RAWTOHEX всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW поддерживает оба типа строк.
Maintenance
Every time you need to convert a LONG column in a table/view to a CLOB you need to do the following:
- Create the object type with attributes corresponding to the columns of the table/view you need to obtain data for. The object type needs two constructors and the member function DEFINE_COLUMNS as for the USER_VIEWS_T object type.
- Create the object type collection for 1.
- Create the standalone pipelined table function.
- Optionally create a view on top of 3.
If you prefer to use packages over standalone functions, you could bundle them up in a package called LONG_TO_CLOB and rename the function LONG_TO_CLOB to TO_CLOB .
Функция CONVERT
Преобразует строку из одного набора символов в другой. Синтаксис функции:
Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.
Функция CONVERT не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.
3. Add Constraint (Optional)
If the original column has imposed constrains, we should add them on the new column too, like we add NOT NULL constraint on the column as below.
SQL> alter table hr.locations modify (city_1 not null);
Недостатки неявного преобразования
Неявное преобразование типов имеет ряд недостатков.
- PL/SQL относится к языкам со статической типизацией. Неявные преобразования означают потерю некоторых преимуществ статической типизации — таких, как ясность и надежность кода.
- Каждое неявное преобразование означает частичную потерю контроля над программой. Программист не выполняет его самостоятельно и никак им не управляет, а лишь предполагает, что оно будет выполнено и даст желаемый эффект. В этом есть элемент неопределенности — если компания Oracle изменит способ или условие выполнения преобразований, это может отразиться на программе.
- Неявное преобразование типов в PL/SQL зависит от контекста. Оно может работать в одной программе и не работать в другой, хотя на первый взгляд код кажется одинаковым. Кроме того, результат преобразования типов не всегда соответствует ожиданиями программиста.
- Программу легче читать и понять, если данные в ней преобразуются явно, поскольку при этом фиксируются различия между типами данных в разных таблицах или в таблице и коде. Исключая из программы скрытые действия, вы устраняете и потенциальную возможность ошибок.
Таким образом, в SQL и PL/SQL рекомендуется избегать неявного преобразования типов. Лучше пользоваться функциями, которые выполняют явное преобразование — это гарантирует, что результат преобразования будет точно соответствовать вашим ожиданиям.
Problem
Imagine the following scenario:
- You need to access the source of a given view.
- USER_VIEWS.TEXT contains the view source but it is a column with datatype LONG so very limited in use.
- Using DBMS_METADATA to obtain the source is not an option for various reasons, eg performance, practicality, etc.
2. Migrate Data from VARCHAR2
SQL> update hr.locations set city_1 = city;
23 rows updated.
Check the new table definition.
SQL> desc hr.locations;
Name Null? Type
----------------------------------------- -------- ----------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(4000)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
CITY_1 CLOB
How to Convert VARCHAR2 to CLOB
No, we can't do it directly, but we can do it indirectly. Let's see steps.
Функция ROWIDTOCHAR
Преобразует двоичное значение типа ROWID в строку типа VARCHAR2 . Синтаксис функции ROWIDTOCHAR :
Возвращаемая функцией строка имеет следующий формат:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке PL/SQL. Все четыре компонента задаются в формате Base64. Пример:
Oracle и PL/SQL поддерживают несколько разновидностей типов данных, предназначенных специально для работы с большими объектами ( LOB, Large OBjects ). Такие объекты позволяют хранить огромные (от 8 до 128 терабайт) объемы двоичных (например, графических) или текстовых данных.
До выхода Oracle9i Release2 в объектах LOB можно было хранить до 4 Гбайт данных. Начиная с Oracle10g, ограничение было повышено до величины от 8 до 128 терабайт (конкретное значение зависит от размера блока вашей базы данных).
В PL/SQL можно объявлять большие объекты четырех типов:
- BFILE — двоичный файл. Переменная этого типа содержит локатор файла, указывающий на файл операционной системы вне базы данных. Oracle интерпретирует содержимое файла как двоичные данные.
- BLOB — большой двоичный объект. Переменная этого типа содержит локатор LOB , указывающий на большой двоичный объект, хранящийся в базе данных.
- CLOB — большой символьный объект. Переменная этого типа содержит локатор LOB , указывающий на хранящийся в базе данных большой блок текстовых данных в наборе символов базы данных.
- NCLOB — большой символьный объект с поддержкой символов национальных языков ( NLS ). Переменная этого типа содержит локатор LOB , указывающий на хранящийся в базе данных большой блок текстовых данных с национальным набором символов.
Большие объекты можно разделить на две категории: внутренние и внешние. Внутренние большие объекты (типы BLOB, CLOB и NCLOB ) хранятся в базе данных и могут участвовать в транзакциях на сервере базы данных. Внешние большие объекты (тип BFILE ) представляют двоичные данные, хранящиеся в файлах операционной системы вне таблиц базы данных. Они не могут участвовать в транзакциях, то есть вносимые в них изменения нельзя сохранить или отменить в зависимости от результата транзакции. Целостность данных обеспечивается только на уровне файловой системы. Кроме того, повторное чтение из BFILE может приводить к разным результатам — в отличие от внутренних больших объектов, соответствующих модели логической целостности чтения.
Share this:
“LONG-to-CLOB” Function
This is the function that converts a LONG column to a CLOB value through a DBMS_SQL cursor that has been parsed, prepared (given column “defined” with DBMS_SQL.DEFINE_COLUMN_LONG ) and executed:
4. Drop the Source Column
Our plan is to replace the original column with the new column. Therefore, we need to drop the original column.
SQL> alter table hr.locations drop column city;
Like this:
About ellebaek
Sun Certified Programmer for Java 5 (SCJP). Oracle Advanced PL/SQL Developer Certified Professional (OCP Advanced PL/SQL). ISTQB Certified Tester Foundation Level (ISTQB CTFL).
Solution
Seen that we need to access the data from the view, we cannot use TO_LOB or any of the other solutions that alter the underlying table column definition. However, the DBMS_SQL.COLUMN_VALUE_LONG function comes to the rescue as this allows us to fetch the LONG data piecewise and construct a CLOB with the same data. This obviously means that we have to use dynamic SQL for the query of the table/view we’re trying to convert the LONG column for.
We have two different methods of applying dynamic SQL to this problem:
- Dynamic SQL for just the LONG column. We need to write a function that receives the primary key value(s) of the underlying table/view (could be ROWID if a table) as input parameter(s), builds a SELECT statement for the underlying table/view for the LONG column using bind variable(s) for the primary key value(s), does the DBMS_SQL magic and uses the DBMS_SQL.COLUMN_VALUE_LONG function. For scalability, the solution should keep a collection of parsed statements and re-use those without re-parsing.
- Dynamic SQL for the whole underlying table/view generated and called in a pipelined table function that utilizes object types.
For this blog post I’ll use solution 2, for which I’ll demonstrate the following:
- Create a standalone function with “LONG-to-CLOB” functionality.
- Create an object type with attributes corresponding to USER_VIEWS . This object type will have member methods used with DBMS_SQL .
- Create an object type collection based on the object type from 2.
- Create a standalone function that takes an optional argument for a WHERE clause used against USER_VIEWS , using a combination of DBMS_SQL and 1., 2., and 3. above. This method is a pipelined table function.
- Optionally create a view on top of the standalone function from 4.
You can apply the structure of this solution in use cases where you need to access a LONG value in a table/view as a CLOB , without converting the underlying persistent column: The standalone function from 1. above is generic — items 2. through 5. are specific to the underlying table/view.
We’ll go through the different parts in the following sections.
Pipelined Table Function
This is the implementation of the standalone pipelined table function that takes an optional parameter to be matched against the VIEW_NAME column in a LIKE expression and uses DBMS_SQL to parse the query from USER_VIEWS , uses the USER_VIEWS_T and USER_VIEWS_C object types (which in turn calls LONG_TO_CLOB ) and pipes the rows back to the SQL engine (type PTF suffix refers to “Pipelined Table Function”):
It’s inconvenient that we can’t create this function as a member function on USER_VIEWS_T where it really belongs. However this is not possible because that would introduce a cyclical dependency between USER_VIEWS_T and USER_VIEWS_C , which is not allowed (even using forward object type declarations).
5. Rename the Target Column
Use RENAME clause to replace the original column.
SQL> alter table hr.locations rename column city_1 to city;
SQL> desc hr.locations;
Name Null? Type
----------------------------------------- -------- ----------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
CITY NOT NULL CLOB
Now, you have to make sure that all business logic are running correctly. The only drawback we have now is that the column order has been changed. If you do care about it, you can re-order column position of a table by different ways.
Для работы с данными большого объема СУБД 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-сегментах данные, свести к минимуму их дублирование, шифровать эти данные. Но эта тема выходит за рамки данного очерка.
SELECT
You can select from the pipelined table function with a TABLE expression, such as:
1. Add a Target Column with CLOB.
SQL> alter table hr.locations add (city_1 clob);
We appended _1 to the new column name to differentiate from the source column.
Installation
You need to install the solution objects in the following order:
- LONG_TO_CLOB function.
- USER_VIEWS_T object type specification.
- USER_VIEWS_T object type body.
- USER_VIEWS_C object type collection.
- USER_VIEWS_PTF function.
A small test case:
You can optionally create a view on top of the pipelined table function. In this case, you cannot push the LIKE expression into the argument to the table function so the optimizer has no alternative to perform a full table scan on USER_VIEWS and then a match on the returned rows on whatever predicate the view is used with.
Функция HEXTORAW
Преобразует шестнадцатеричную строку типа CHAR или VARCHAR2 в значение типа RAW . Синтаксис функции HEXTORAW :
Явное преобразование типов
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 | Строку произвольного набора символов в Юникод |
Object Type
In Oracle 11.2.0.1.0, USER_VIEWS has the following columns:
which means that we could create our object type like the following (notice how TEXT is represented by a CLOB instead of the original LONG ):
The object type has two constructors and one member function. The member function is used to define the columns for DBMS_SQL and in order to be able to use the attributes, this has to be a member function (working on an object type instance as opposed to a static function) and the first constructor is used to create such a dummy instance, with all the attributes set to NULL . The second constructor sets all attributes to corresponding column values in a given fetched DBMS_SQL cursor.
This is the implementation of the object type body:
Notice how the second constructor uses our LONG_TO_CLOB function.
The process of matching the original table/view columns into attributes and handling of these in one of the constructors and the DEFINE_COLUMNS method is tedious and error prone. If you need to do this often you should consider writing a generator that generates the object type specification and body based on the definition of a given table/view.
Функция CHARTOROWID
Преобразует строку типа CHAR или VARCHAR2 в значение типа ROWID . Синтаксис функции:
Для успешного преобразования функцией CHARTOROWID строка должна состоять из 18 символов в формате:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке. Все четыре компонента задаются в формате Base64. Если исходная строка не соответствует этому формату, инициируется исключение VALUE_ERROR .
9 Responses to Converting a LONG Column to a CLOB on the fly
Or you know…Oracle could stop being lazy and update all their deprecated data types. You would think after 5-10 years it would be on the client to update their code and not the db vendor.
Thanks – very helpful. I’ve tweaked it a bit to allow NULLs to propagate through, though, as I wanted to use it with a nullable LONG:
dbms_sql.column_value_long(dbms_sql_cursor, col_id, buf_len, cur_pos, long_val, long_len);
IF long_val IS NULL THEN
RETURN NULL;
END IF;
dbms_lob.CreateTemporary(result, false, dbms_lob.call);
WHILE long_len > 0 LOOP
dbms_lob.Append(result, long_val);
cur_pos := cur_pos + long_len;
dbms_sql.column_value_long(dbms_sql_cursor, col_id, buf_len, cur_pos, long_val, long_len);
END LOOP;
Excellent point, thank you very much Lee!
Damn you Oracle! Calling people not to use LONG but using yourself in crucial places? Look what kind of bizarre things have to be done to workaround it!
I have a very vexing problem with a LONG column in a vendor’s database table.
I am building a data warehouse, and I need to create a view to convert a LONG to a CLOB on the fly. I thought I found the solution here, but, unfortunately, I’ve run into a snag.
My table has three keys and a LONG value:
SQL> desc req_task_list
Name Null? Type
—————————- ——– ————-
REQ_NUMBER VARCHAR2(10)
PHASE NUMBER
WO_NUMBER VARCHAR2(10)
TASK_LIST LONG
The keys have the following three combinations
REQ_NUMBER, null, null
REQ_NUMBER, PHASE, null
null, null, WO_NUMBER
I wrote the Type to return all the columns, and the Pipeline Function to not take any arguments. My ptf query is straight-forward:
query :=
‘select tl.req_number, tl.phase, tl.wo_number, tl.task_list from req_task_list tl ‘ ||
‘where rownum < 101 ';
When I run a view query on the collection, for example
select * from table(req_task_list_t);
it works fine and returns all 100 rows of data collected back in the ptf.
But I have over 300,000 rows in my source table, not a mere 100, and the collection, which lives in memory, can't handle that many rows in a reasonable amount of time.
I am considering rewriting my view query to incorporate the source table like this
select r.req_number, r.phase, r.wo_number,
(select tl.* from table(req_task_list_t(r.req_number, r.phase, r.wo_number)) task_list
from req r;
But this doesn't seen as elegant as your solution for USER_VIEWS. Am I overthinking this?
I’m not sure I see the benefit from the rewrite of the query. Have you looked into whether your pipelined table function is called more than you expect (in your case more than once)? Unfortunately, this is often the case. Sometimes hinting can improve on this. It’s a shame that the function result cache can’t be used with pipelined table functions and object types.
The entire collection does not live in memory. That’s the point with pipelined table functions (the following taken from the Oracle docs):
“Pipelining enables rows to be returned iteratively, as they are produced. This also reduces the memory that a table function requires, as the object cache does not need to materialize the entire collection.”
Also, don’t you need to pass down some kind of WHERE clause against your underlying table? Do you need all 300,000 rows every time you select from the view? If not, how often does data in the underlying table change? If not very often, it would probably be a better idea to hold a persistent CLOB version of the LONG column somewhere.
dbms_sql.column_value_long returns a buffer amount to a varchar2, not a long, so your long_val variable isn’t correct it should be a varchar2.
Excellent point, well spotted! I guess I’ve been blinded by the fact that most DBMS_SQL.COLUMN_VALUE_ procedures return the value in the appropriate datatype.
However, the implicit datatype conversions in PL/SQL saves the solution. So when DBMS_SQL.COLUMN_VALUE_LONG returns it implicitly converts the VARCHAR2 value to a LONG value and saves it in the LONG_VAL local variable. And when DBMS_LOB.APPEND is called, the LONG_VAL local variable is implicitly converted to a CLOB before the call is carried out.
Here’s a test in Oracle 11.2.0.1.0 in the SYS schema (pardon the useless formatting that WordPress allows for comments):
select uvp.view_name,
uvp.text_length,
length(text) text_length2,
abs(uvp.text_length - length(text)) text_length_diff,
uvp.text
from table(user_views_ptf('DBA_%')) uvp
where length(text) > 4000
order by uvp.text_length desc;
VIEW_NAME TEXT_LENGTH TEXT_LENGTH2 TEXT_LENGTH_DIFF TEXT
---------------------------- ----------- ------------ ---------------- ------------------------------
DBA_STREAMS_COLUMNS 36309 36309 0 select distinct u.name, o.n.
DBA_OBJ_AUDIT_OPTS 19247 19247 0 select u.name, o.name, 'TAB.
DBA_MVIEWS 18577 18577 0 select s.sowner as OWNER, s.
DBA_IND_STATISTICS 14597 14597 0 SELECT.
DBA_TRIGGERS 12388 12388 0 select trigusr.name, trigob.
DBA_TAB_STATISTICS 9316 9316 0 SELECT /* TABLES */.
DBA_TRIGGER_COLS 8376 8376 0 select /*+ ORDERED NOCOST *.
DBA_LOB_PARTITIONS 7794 7794 0 select u.name.
DBA_AUTOTASK_CLIENT 7750 7750 0 SELECT "CNAME_KETCL","STATU.
DBA_TAB_PARTITIONS 7287 7287 0 select u.name, o.name, 'NO'.
DBA_SCHEDULER_JOB_DESTS 7092 7092 0 SELECT dd.OWNER, dd.JOB_NA.
DBA_TABLESPACE_THRESHOLDS 7064 7064 0 SELECT tablespace_name.
DBA_TABLES 6966 6966 0 select u.name, o.name.
DBA_SCHEDULER_JOBS 6954 6954 0 SELECT ju.name, jo.name, jo.
DBA_HIST_ACTIVE_SESS_HISTORY 6631 6631 0 select /* ASH/AWR meta attr.
DBA_OBJECT_TABLES 6248 6248 0 select u.name, o.name.
DBA_TAB_COLS 5984 5984 0 select u.name, o.name.
DBA_INDEXES 5652 5652 0 select u.name, o.name.
DBA_NESTED_TABLE_COLS 5622 5622 0 select u.name, o.name.
DBA_IND_PARTITIONS 5388 5388 0 select u.name, io.name, 'NO.
DBA_LOBS 5336 5336 0 select u.name, o.name.
DBA_MVIEW_DETAIL_RELATIONS 4950 4950 0 select u.name, o.name, du.n.
DBA_CUBE_ATTR_VISIBILITY 4914 4914 0 SELECT.
DBA_LOB_SUBPARTITIONS 4805 4805 0 select u.name.
DBA_PART_TABLES 4713 4713 0 select u.name, o.name.
DBA_VARRAYS 4581 4581 0 select u.name, op.name, ac.
DBA_CUBE_VIEW_COLUMNS 4456 4456 0 SELECT.
DBA_TAB_SUBPARTITIONS 4124 4124 0 select u.name, po.name, po.
В ходе выполнения программы PL/SQL часто возникает необходимость преобразования данных из одного типа в другой. Преобразование может выполняться двумя способами:
- Неявно — поиск «оптимального варианта» поручается исполнительному ядру PL/SQL;
- Явно — преобразование выполняется вызовом функции или соответствующим оператором PL/SQL.
В этом разделе мы сначала разберемся, как в PL/SQL выполняются неявные преобразования, а затем перейдем к изучению функций и операторов явного преобразования.
LONG И LONG RAW
Вероятно, спецы, знакомые с Oracle, заметили, что мы до сих пор не упоминали о типах данных LONG и LONG RAW . И это не случайно. Конечно, в столбцах типа LONG и LONG RAW базы данных можно хранить большие объемы (до 2 Гбайт) соответственно символьных и двоичных данных. Однако максимальная длина соответствующих им переменных PL/SQL значительно меньше: всего лишь 32 760 байт, что даже меньше длины переменных VARCHAR2 и RAW (32 767 байт). С учетом столь странного ограничения в программах PL/SQL лучше использовать переменные типа VARCHAR2 и RAW , а не типа LONG и LONG RAW.
Значения типов LONG и LONG RAW , извлекаемые из базы данных и содержащие более 32 767 байт данных, не могут присваиваться переменным типа VARCHAR2 и RAW . Это крайне неудобное ограничение, из-за которого типы LONG и LONG RAW лучше вообще не применять.
Эти типы официально считаются устаревшими и поддерживаются только для сохранения обратной совместимости кода. Компания Oracle не рекомендует ими пользоваться, и я с ней полностью согласен. В новых приложениях вместо них лучше использовать типы CLOB и BLOB . А для существующих приложений в документации Oracle SecureFiles and Large Objects Developer’s Guide приводятся рекомендации по преобразованию данных типа LONG в данные типа LOB .
In each major data types, minor data types can be changed easily from one to another using ALTER TABLE MODIFY statement, as long as they are in the same major data types.
For example, we'd like to change a column FIRST_NAME from VARCHAR2 into CHAR . The original table definition is as the followings:
We convert FIRST_NAME from VARCHAR2 to CHAR .
SQL> alter table hr.employees modify (first_name char(20));
It looks pretty easy to convert one character type to another.
Неявное преобразование типов
Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.
Рис. 1. Неявные преобразования типов, выполняемые PL/SQL
Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:
Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:
Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT . Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT работа программы будет нарушена.
Функция CAST
Функция CAST является очень удобным и гибким механизмом преобразования данных. Она преобразует значение любого (или почти любого) встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию, и скорее всего, будет знакома всем программистам с опытом работы на объектно-ориентированных языках.
С помощью функции CAST можно преобразовать неименованное выражение (число, дату, NULL и даже результат подзапроса) или именованную коллекцию (например, вложенную таблицу) в тип данных или именованную коллекцию совместимого типа. Допустимые преобразования между встроенными типами данных показаны на рис. 2. Необходимо соблюдать следующие правила:
- не допускается преобразование типов данных LONG , LONG RAW , любых типов данных LOB и типов, специфических для Oracle;
- обозначению « DATE » на рисунке соответствуют типы данных DATE , TIMESTAMP , TIMESTAMP WITH TIMEZONE , INTERVAL DAY TO SECOND и INTERVAL YEAR TO MONTH ;
- для преобразования именованной коллекции определенного типа в именованную коллекцию другого типа нужно, чтобы элементы обеих коллекций имели одинаковый тип;
Рис. 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, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:
Читайте также: