Oracle преобразовать clob в varchar2
In PL/SQL, a number of semantic changes have been made as described in the previous paragraphs.
Неявное преобразование типов
Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.
Рис. 1. Неявные преобразования типов, выполняемые PL/SQL
Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:
Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:
Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT . Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT работа программы будет нарушена.
VARCHAR2 and CLOB in PL/SQL Built-In Functions
CLOB and VARCHAR2 are two distinct types.
However, depending on the usage, a CLOB can be passed to SQL and PL/SQL VARCHAR2 built-in functions, used exactly like a VARCHAR2 . Or the variable can be passed into DBMS_LOB APIs, acting like a LOB locator. See the following combined example, "CLOB Variables in PL/SQL" .
PL/SQL VARCHAR2 functions and operators can take CLOB s as arguments or operands.
When the size of a VARCHAR2 variable is not large enough to contain the result from a function that returns a CLOB , or a SELECT on a CLOB column, an error is raised and no operation is performed. This is consistent with VARCHAR2 semantics.
CLOB Variables in PL/SQL
Please note that in line 10 of "CLOB Variables in PL/SQL" , a temporary CLOB is implicitly created and is pointed to by the revisedStory CLOB locator. In the current interface the line can be expanded as:
In line 13, myGist is appended to the end of the temporary LOB, which has the same effect of:
In some occasions, implicitly created temporary LOBs in PL/SQL statements can change the representation of LOB locators previously defined. Consider the next example.
Change in Locator-Data Linkage
After line 7, myStory represents a persistent LOB in print_media .
The DBMS_LOB.WRITE call in line 8 directly writes the data to the table.
No UPDATE statement is necessary. Subsequently in line 11, a temporary LOB is created and assigned to myStory because myStory is now used like a local VARCHAR2 variable. The LOB locator myStory now points to the newly-created temporary LOB.
Therefore, modifications to myStory are no longer reflected in the database. To propagate the changes to the database table, an UPDATE statement becomes necessary now. Note again that for the previous persistent LOB, the UPDATE is not required.
If the SQL statement returns a LOB or a LOB is an OUT parameter for a PL/SQL function or procedure, you must test if it is a temporary LOB, and if it is, then free it after you are done with it.
Freeing Temporary LOBs Automatically and Manually
Temporary LOBs created in a program block as a result of a SELECT or an assignment are freed automatically at the end of the PL/SQL block or function or procedure. You must also free the temporary LOBs that were created with DBMS_LOB.CREATETEMPORARY to reclaim system resources and temporary tablespace. Do this by calling DBMS_LOB.FREETEMPORARY on the CLOB variable.
PL/SQL CLOB Comparison Rules
Like VARCHAR2 s, when a CLOB is compared with another CLOB or compared with a VARCHAR2 , a set of rules determines the comparison. The rules are usually called a "collating sequence". In Oracle, CHAR s and VARCHAR2 s have slightly different sequences due to the blank padding of CHAR s.
VARCHAR2 and CLOB in PL/SQL Built-In Functions
CLOB and VARCHAR2 are still two distinct types. But depending on the usage, a CLOB can be passed to SQL and PL/SQL VARCHAR2 built-in functions, used exactly like a VARCHAR2 . Or the variable can be passed into DBMS_LOB APIs, acting like a LOB locator. Please see the following combined example,"CLOB Variables in PL/SQL".
PL/SQL VARCHAR2 functions and operators can take CLOB s as arguments or operands.
When the size of a VARCHAR2 variable is not large enough to contain the result from a function that returns a CLOB , or a SELECT on a CLOB column, an error should be raised and no operation will be performed. This is consistent with VARCHAR2 semantics.
CLOB Variables in PL/SQL
Please note that in line 10 of "CLOB Variables in PL/SQL", a temporary CLOB is implicitly created and is pointed to by the revisedStory CLOB locator. In the current interface the line can be expanded as:
In line 13, myGist is appended to the end of the temporary LOB, which has the same effect of:
In some occasions, implicitly created temporary LOBs in PL/SQL statements can change the representation of LOB locators previously defined. Consider the next example.
Change in Locator-Data Linkage
After line 7, myStory represents a persistent LOB in print_media .
The DBMS_LOB.WRITE call in line 8 directly writes the data to the table.
No UPDATE statement is necessary. Subsequently in line 11, a temporary LOB is created and assigned to myStory because myStory is now used like a local VARCHAR2 variable. The LOB locator myStory now points to the newly-created temporary LOB.
Therefore, modifications to myStory will no longer be reflected in the database. To propagate the changes to the database table, an UPDATE statement becomes necessary now. Note again that for the previous persistent LOB, the UPDATE is not required.
Temporary LOBs created in a program block as a result of a SELECT or an assignment are freed automatically at the end of the PL/SQL block/function/procedure. You can choose to free the temporary LOBs to reclaim system resources and temporary tablespace by calling DBMS_LOB.FREETEMPORARY on the CLOB variable.
Freeing Temporary LOBs Automatically and Manually
6.1.2 Implicit Conversions Between CLOB and VARCHAR2
This section describes support for implicit conversions between CLOB and VARCHAR2 data types.
Implicit conversions from CLOB to VARCHAR2 and from VARCHAR2 to CLOB data types are supported in PL/SQL.
SQL Semantics for LOBs for details on LOB support in SQL statements.
While this section uses VARCHAR2 data type as an example for simplicity, other character types like CHAR and LONG can also participate in implicit conversions with CLOBs.
Assigning a CLOB to a VARCHAR2 in PL/SQL
- SELECT persistent or temporary CLOB data into a character buffer variable such as CHAR , LONG , or VARCHAR2 . In a single SELECT statement, you can have more than one of such defines.
- Assign a CLOB to a VARCHAR2 , CHAR , or LONG variable.
- Pass CLOB data types to built-in SQL and PL/SQL functions and operators that accept VARCHAR2 arguments, such as the INSTR function and the SUBSTR function.
- Pass CLOB data types to user-defined PL/SQL functions that accept VARCHAR2 or LONG data types.
The following example illustrates the way CLOB data is accessed when the CLOB s are treated as VARCHAR2 s:
Assigning a VARCHAR2 to a CLOB in PL/SQL
- INSERT or UPDATE character data stored in VARCHAR2 , CHAR , or LONG variables into a CLOB column. Multiple such binds are allowed in a single INSERT or UPDATE statement.
- Assign a VARCHAR2 , CHAR , or LONG variable to a CLOB variable.
- Pass VARCHAR2 or LONG data types to user-defined PL/SQL functions that accept LOB data types.
Функция HEXTORAW
Преобразует шестнадцатеричную строку типа CHAR или VARCHAR2 в значение типа RAW . Синтаксис функции HEXTORAW :
PL/SQL Functions for Remote LOBs and BFILEs
Built-in and user-defined PL/SQL functions that are executed on the remote site and operate on remote LOBs and BFILE s are allowed, as long as the final value returned by nested functions is not a LOB.
The example uses the print_media table described in "Table for LOB Examples: The PM Schema print_media Table"
6.1.5 Detailed Examples for Implicit Conversions with LOBs
The example in this section demonstrates using multiple VARCHAR and RAW binds in INSERT and UPDATE operations.
Example 6-1 Using Character and RAW Binds in INSERT and UPDATE Operations
The following example demonstrates using Character and RAW binds for LOB columns in INSERT and UPDATE operations
Example 6-2 Multiple Defines for LOBs in SELECT
The following example demonstrates performing a SELECT operation to retrieve multiple persistent or temporary CLOBs from a SQL query into a VARCHAR2 variable, or a BLOB to a RAW variable.
Example 6-3 Implicit Conversions between BLOB and RAW
Implicit assignment works for variables declared explicitly and for variables declared by referencing an existing column type using the %TYPE attribute as show in the following example. The example assumes that column long_col in table t has been migrated from a LONG to a CLOB column.
Example 6-4 Calling PL/SQL and C Procedures from PL/SQL
You can call a PL/SQL or C procedure from PL/SQL. You can pass a CLOB as an actual parameter, where a VARCHAR2 is the formal parameter, or you can pass a VARCHAR2 as an actual parameter, where a CLOB is the formal parameter. The same holds good for BLOB s and RAW s. One example of when these cases can arise is when either the formal or the actual parameter is an anchored type, that is, the variable is declared using the table_name.column_name%type syntax. PL/SQL procedures or functions can accept a CLOB or a VARCHAR2 as a formal parameter. This holds for both built-in and user-defined procedures and functions.
The following example demonstrates implicit conversion during procedure calls:
Example 6-5 Implicit Conversion with PL/SQL built-in functions
The following example illustrates the use of CLOB s in PL/SQL built-in functions.
Most discussions concerning PL/SQL semantics, and CLOB s and VARCHAR2 s, also apply to BLOB s and RAW s, unless otherwise noted. In the text, BLOB and RAW are not explicitly mentioned.
PL/SQL semantics support is described in the following sections:
Implicit Conversions Between CLOB and VARCHAR2
Implicit conversions from CLOB to VARCHAR2 and from VARCHAR2 to CLOB datatypes are allowed in PL/SQL. These conversions enable you to perform the following operations in your application:
CLOB columns can be selected into VARCHAR2 PL/SQL variables
VARCHAR2 columns can be selected into CLOB variables
Assignment and parameter passing between CLOB s and VARCHAR2 s
Accessing a CLOB as a VARCHAR2 in PL/SQL
The following example illustrates the way CLOB data is accessed when the CLOB s are treated as VARCHAR2 s:
Assigning a CLOB to a VARCHAR2 in PL/SQL
CLOBs Follow the VARCHAR2 Collating Sequence
As a rule, CLOB s follow the same collating sequence as VARCHAR2 s. That is, when a CLOB is compared, the result is consistent with if the CLOB data content is retrieved into a VARCHAR2 buffer and the VARCHAR2 is compared. The rule applies to all cases including comparisons between CLOB and CLOB , CLOB and VARCHAR2 , and CLOB and CHAR .
У меня есть таблица Oracle со столбцом типа clob. Я хочу сохранить порядок столбцов и изменить тип данных на varchar2. Столбец просто содержит текст.
ORA-22859: invalid modification of columns
Я попытался сделать столбец нулевым, а затем преобразовать в char или long, а затем в varchar2. Но вроде ничего не работает. Я бы предпочел не копировать таблицу, чтобы изменить один столбец.
Я не хочу просто читать содержимое. Я хочу изменить тип данных столбца с clob на varchar2.
Помощь будет принята с благодарностью. Я работаю над этим некоторое время. Дайте знать, если у вас появятся вопросы.
Вы рискуете, что преобразовав данные, вы обрежете все строки, длина которых превышает 4000 символов. Вы уверены, что длина всех ваших данных меньше 4000 символов?
Порядок столбцов на самом деле не имеет значения. Есть ли причина, по которой вам нужно его сохранить?
И, @alex poole, к сожалению, то, как этот проект был настроен исходным разработчиком, сделал порядок столбцов важным и жестко закодировал его в фоновом режиме. : /
Вы можете сделать следующие шаги:
Если вы не хотите хранить данные в исходном столбце:
Если вы действительно хотите сохранить данные, это всего лишь два дополнительных шага, показанных в версии rowid ниже.
Создайте таблицу переопределения со столбцами в нужном вам порядке:
И называем посылку:
В документации есть больше о проверке того, что таблица действительна для переопределения, обработки зависимых таблиц (внешних ключей) и т. Д.
Если в вашей таблице нет первичного ключа, вы можете использовать идентификаторы строк , передав дополнительный флаг параметра. Для этой демонстрации я тоже сохраню данные.
Как и раньше, на этом этапе новый столбец (на этот раз содержащий данные) есть, но в неправильном положении. Итак, переопределите, как раньше, но с DBMS_REDEFINITION.CONS_USE_ROWID флагом:
И данные тоже есть:
И, как упоминалось в связанной документации, вы можете удалить скрытый столбец, используемый для управления идентификаторами строк:
This section describes the implicit conversion process in PL/SQL from one LOB type to another LOB type or from a LOB type to a non-LOB type.
Most of the in the following sections use print_media table. Following is the structure of print_media table:
Figure 6-1 print_media table
Функция CHARTOROWID
Преобразует строку типа CHAR или VARCHAR2 в значение типа ROWID . Синтаксис функции:
Для успешного преобразования функцией CHARTOROWID строка должна состоять из 18 символов в формате:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке. Все четыре компонента задаются в формате Base64. Если исходная строка не соответствует этому формату, инициируется исключение VALUE_ERROR .
Remote Functions in PL/SQL, OCI, and JDBC
All the SQL statements listed above work the same if they are executed from inside PL/SQL, OCI, and JDBC. No additional functionality is provided.
В ходе выполнения программы PL/SQL часто возникает необходимость преобразования данных из одного типа в другой. Преобразование может выполняться двумя способами:
- Неявно — поиск «оптимального варианта» поручается исполнительному ядру PL/SQL;
- Явно — преобразование выполняется вызовом функции или соответствующим оператором PL/SQL.
В этом разделе мы сначала разберемся, как в PL/SQL выполняются неявные преобразования, а затем перейдем к изучению функций и операторов явного преобразования.
Implicit Conversions Between CLOB and VARCHAR2
Implicit conversions from CLOB to VARCHAR2 and from VARCHAR2 to CLOB data types are allowed in PL/SQL.
These conversions enable you to perform the following operations in your application:
CLOB columns can be selected into VARCHAR2 PL/SQL variables
VARCHAR2 columns can be selected into CLOB variables
Assignment and parameter passing between CLOB s and VARCHAR2 s
Accessing a CLOB as a VARCHAR2 in PL/SQL
The following example illustrates the way CLOB data is accessed when the CLOB s are treated as VARCHAR2 s:
Assigning a CLOB to a VARCHAR2 in PL/SQL
6.1.1 Implicit Conversion Between CLOB and NCLOB Data Types in SQL
This section describes support for implicit conversions between CLOB and NCLOB data types.
The database enables you to perform operations such as cross-type assignment and cross-type parameter passing between CLOB and NCLOB data types. The database performs implicit conversions between these types when necessary to preserve properties such as character set formatting.
Note that, when implicit conversions occur, each character in the source LOB is changed to the character set of the destination LOB, if needed. In this situation, some degradation of performance may occur if the data size is large. When the character set of the destination and the source are the same, there is no degradation of performance.
After an implicit conversion between CLOB and NCLOB types, the destination LOB is implicitly created as a temporary LOB. This new temporary LOB is independent from the source LOB. If the implicit conversion occurs as part of a define operation in a SELECT statement, then any modifications to the destination LOB do not affect the persistent LOB in the table that the LOB was selected from as shown in the following example:
Oracle Database SQL Language Reference for details on implicit conversions supported for all data types.
Ограничения неявного преобразования
Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:
В PL/SQL нельзя преобразовать строку «abc» в число, поэтому при выполнении приведенного кода инициируется исключение VALUE_ERROR . Вы сами должны позаботиться о том, чтобы значение, для которого PL/SQL выполняет преобразование типов, могло быть конвертировано без ошибок.
Функция ROWIDTOCHAR
Преобразует двоичное значение типа ROWID в строку типа VARCHAR2 . Синтаксис функции ROWIDTOCHAR :
Возвращаемая функцией строка имеет следующий формат:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке PL/SQL. Все четыре компонента задаются в формате Base64. Пример:
Restrictions on Remote User-Defined Functions
The restrictions that apply to SQL functions apply here also.
A function in one dblink cannot operate on LOB data in another dblink.For example, the following statement is not supported:
One query block cannot contain tables and functions at different dblink s. For example, the following statement is not supported:
There is no support for performing remote LOB operations (that is, DBMS_LOB ) from within PL/SQL, other than issuing SQL statements from PL/SQL.
Функция 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, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:
Явное преобразование типов
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 | Строку произвольного набора символов в Юникод |
Функция CONVERT
Преобразует строку из одного набора символов в другой. Синтаксис функции:
Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.
Функция CONVERT не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.
Функция RAWTOHEX
Преобразует значение типа RAW в шестнадцатеричную строку типа VARCHAR2 . Синтаксис функции RAWTOHEX :
Функция RAWTOHEX всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW поддерживает оба типа строк.
Explicit Conversion Functions
In SQL and PL/SQL, the certain explicit conversion functions convert other data types to and from CLOB , NCLOB , and BLOB as part of the LONG -to-LOB migration:
TO_CLOB() : Converting from VARCHAR2 , NVARCHAR2 , or NCLOB to a CLOB
TO_NCLOB() : Converting from VARCHAR2 , NVARCHAR2 , or CLOB to an NCLOB
TO_BLOB() : Converting from RAW to a BLOB
TO_CHAR() converts a CLOB to a CHAR type. When you use this function to convert a character LOB into the database character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit.
TO_NCHAR() converts an NCLOB to an NCHAR type. When you use this function to convert a character LOB into the national character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit.
CAST does not directly support any of the LOB data types. When you use CAST to convert a CLOB value into a character data type, an NCLOB value into a national character data type, or a BLOB value into a RAW data type, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target data type. If the resulting value is larger than the target type, then the database returns an error.
Other explicit conversion functions are not supported, such as, TO_NUMBER() , see Table 16-1. Conversion function details are explained in Migrating Columns from LONGs to LOBs.'
Note that LOBs do not support duplicate LONG binds.
6.1.4 Guidelines and Restrictions for Implicit Conversions with LOBs
This section describes the techniques that you use to access LOB columns or attributes using the Data Interface for LOBs.
Data from CLOB and BLOB columns or attributes can be referenced by regular SQL statements, such as INSERT , UPDATE , and SELECT .
There is no piecewise INSERT , UPDATE , or fetch routine in PL/SQL. Therefore, the amount of data that can be accessed from a LOB column or attribute is limited by the maximum character buffer size in PL/SQL, which is 32767 bytes. For this reason, only LOBs less than 32 kilo bytes in size can be accessed by PL/SQL applications using the data interface for persistent LOBs.
If you must access a LOB with a size more than 32 kilobytes -1 bytes, using the data interface, then you must make JDBC or OCI calls from the PL/SQL code to use the APIs for piecewise insert and fetch.
Use the following guidelines for using the Data Interface to access LOB columns or attributes:
LOB columns or attributes can be selected into character or binary buffers in PL/SQL. If the LOB column or attribute is longer than the buffer size, then an exception is raised without filling the buffer with any data. LOB columns or attributes can also be selected into LOB locators.
You can INSERT into tables containing LOB columns or attributes using regular INSERT statements in the VALUES clause. The field of the LOB column can be a literal, a character data type, a binary data type, or a LOB locator.
LOB columns or attributes can be updated as a whole by UPDATE . SET statements. In the SET clause, the new value can be a literal, a character data type, a binary data type, or a LOB locator.
There are restrictions for binds of more than 4000 bytes:
If a table has both LONG and LOB columns, then you can bind more than 4000 bytes of data to either the LONG or LOB columns, but not both in the same statement.
In an INSERT AS SELECT operation, binding of any length data to LOB columns is not allowed.
The database does not do implicit hexadecimal to RAW or RAW to hexadecimal conversions on data that is more than 4000 bytes in size. You cannot bind a buffer of character data to a binary data type column, and you cannot bind a buffer of binary data to a character data type column if the buffer is over 4000 bytes in size. Attempting to do so results in your column data being truncated at 4000 bytes.
For example, you cannot bind a VARCHAR2 buffer to a BLOB column if the buffer is more than 4000 bytes in size. Similarly, you cannot bind a RAW buffer to a CLOB column if the buffer is more than 4000 bytes in size.
Недостатки неявного преобразования
Неявное преобразование типов имеет ряд недостатков.
- PL/SQL относится к языкам со статической типизацией. Неявные преобразования означают потерю некоторых преимуществ статической типизации — таких, как ясность и надежность кода.
- Каждое неявное преобразование означает частичную потерю контроля над программой. Программист не выполняет его самостоятельно и никак им не управляет, а лишь предполагает, что оно будет выполнено и даст желаемый эффект. В этом есть элемент неопределенности — если компания Oracle изменит способ или условие выполнения преобразований, это может отразиться на программе.
- Неявное преобразование типов в PL/SQL зависит от контекста. Оно может работать в одной программе и не работать в другой, хотя на первый взгляд код кажется одинаковым. Кроме того, результат преобразования типов не всегда соответствует ожиданиями программиста.
- Программу легче читать и понять, если данные в ней преобразуются явно, поскольку при этом фиксируются различия между типами данных в разных таблицах или в таблице и коде. Исключая из программы скрытые действия, вы устраняете и потенциальную возможность ошибок.
Таким образом, в SQL и PL/SQL рекомендуется избегать неявного преобразования типов. Лучше пользоваться функциями, которые выполняют явное преобразование — это гарантирует, что результат преобразования будет точно соответствовать вашим ожиданиям.
Explicit Conversion Functions
In SQL and PL/SQL, the following explicit conversion functions convert other data types to and from CLOB , NCLOB , and BLOB as part of the LONG-to-LOB migration:
TO_CLOB() : Converting from VARCHAR2 , NVARCHAR2 , or NCLOB to a CLOB
TO_NCLOB() : Converting from VARCHAR2 , NVARCHAR2 , or CLOB to an NCLOB
TO_BLOB() : Converting from RAW to a BLOB
TO_CHAR() converts a CLOB to a CHAR type. When you use this function to convert a character LOB into the database character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit.
TO_NCHAR() converts an NCLOB to an NCHAR type. When you use this function to convert a character LOB into the national character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit.
CAST does not directly support any of the LOB datatypes. When you use CAST to convert a CLOB value into a character datatype, an NCLOB value into a national character datatype, or a BLOB value into a RAW datatype, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target datatype. If the resulting value is larger than the target type, then the database returns an error.
Other explicit conversion functions are not supported, such as, TO_NUMBER() , see Table 9-1, "SQL VARCHAR2 Functions and Operators on LOBs". Conversion function details are explained in Chapter 11, "Migrating Columns from LONGs to LOBs".
6.1.3 Implicit Conversions Between BLOB and RAW
This section describes support for implicit conversions between BLOB and RAW data types.
- INSERT or UPDATE binary data stored in RAW or LONG RAW variables into a BLOB column. Multiple such binds are allowed in a single INSERT or UPDATE statement.
- SELECT persistent or temporary BLOB data into a binary buffer variable such as RAW and LONG RAW . Multiple such defines are allowed in a single SELECT statement.
- Assign a BLOB to a RAW or LONG RAW variable, or assign a RAW or LONG RAW to a BLOB variable.
- Pass BLOB data types to built-in or user-defined PL/SQL functions defined to accept RAW or LONG RAW data types or pass RAW or LONG RAW data types to built-in or user-defined PL/SQL functions defined to accept BLOB data types.
Читайте также: