Oracle blob преобразовать в текст

Обновлено: 07.07.2024

The DBMS_LOB package provides subprograms to operate on BLOBs , CLOBs , NCLOBs , BFILEs , and temporary LOBs. You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs.

This chapter contains the following topics:

Rules and Limits

Using DBMS_LOB

Overview

DBMS_LOB can read and modify BLOBs , CLOBs , and NCLOBs ; it provides read-only operations for BFILEs . The bulk of the LOB operations are provided by this package.

Security Model

This package must be created under SYS . Operations provided by this package are performed under the current calling user, not under the package owner SYS .

Any DBMS_LOB subprogram called from an anonymous PL/SQL block is executed using the privileges of the current user. Any DBMS_LOB subprogram called from a stored procedure is executed using the privileges of the owner of the stored procedure.

When creating the procedure, users can set the AUTHID to indicate whether they want definer's rights or invoker's rights. For example:

For more information on AUTHID and privileges, see Oracle Database PL/SQL User's Guide and Reference

You can provide secure access to BFILEs using the DIRECTORY feature discussed in BFILENAME function in the Oracle Database Application Developer's Guide - Large Objects and the Oracle Database SQL Reference .

For information about the security model pertaining to temporary LOB s, see Operational Notes.

Constants

DBMS_LOB defines the following constants:

Datatypes

The DBMS_LOB package uses the datatypes shown in Table 52-1.

Table 52-1 Datatypes Used by DBMS_LOB

Source or destination binary LOB .

Source or destination RAW buffer (used with BLOB ).

Source or destination character LOB (including NCLOB ).

Source or destination character buffer (used with CLOB and NCLOB ).

Specifies the size of a buffer or LOB , the offset into a LOB , or the amount to access.

Large, binary object stored outside the database.

The DBMS_LOB package defines no special types.

An NCLOB is a CLOB for holding fixed-width and varying-width, multibyte national character sets.

The clause ANY_CS in the specification of DBMS_LOB subprograms for CLOB s enables the CLOB type to accept a CLOB or NCLOB locator variable as input.

Rules and Limits

General Rules and Limits

The following rules apply in the specification of subprograms in this package:

length , offset , and amount parameters for subprograms operating on BLOBs and BFILEs must be specified in terms of bytes .

length , offset , and amount parameters for subprograms operating on CLOBs must be specified in terms of characters .

A subprogram raises an INVALID_ARGVAL exception if the following restrictions are not followed in specifying values for parameters (unless otherwise specified):

Only positive, absolute offsets from the beginning of LOB data are permitted: Negative offsets from the tail of the LOB are not permitted.

Only positive, nonzero values are permitted for the parameters that represent size and positional quantities, such as amount , offset , newlen , nth , and so on. Negative offsets and ranges observed in SQL string functions and operators are not permitted.

The value of offset , amount , newlen , nth must not exceed the value lobmaxsize (4GB-1) in any DBMS_LOB subprogram.

For CLOB s consisting of fixed-width multibyte characters, the maximum value for these parameters must not exceed ( lobmaxsize / character_width_in_bytes ) characters .

For example, if the CLOB consists of 2-byte characters, such as:

Then, the maximum amount value should not exceed:

PL/SQL language specifications stipulate an upper limit of 32767 bytes (not characters) for RAW and VARCHAR2 parameters used in DBMS_LOB subprograms. For example, if you declare a variable to be:

Then, charbuf can hold 3000 single byte characters or 1500 2-byte fixed width characters. This has an important consequence for DBMS_LOB subprograms for CLOBs and NCLOBs .

The %CHARSET clause indicates that the form of the parameter with %CHARSET must match the form of the ANY_CS parameter to which it refers.

For example, in DBMS_LOB subprograms that take a VARCHAR2 buffer parameter, the form of the VARCHAR2 buffer must match the form of the CLOB parameter. If the input LOB parameter is of type NCLOB , then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB , then the buffer must contain CHAR data.

For DBMS_LOB subprograms that take two CLOB parameters, both CLOB parameters must have the same form; that is, they must both be NCLOBs , or they must both be CLOBs .

If the value of amount plus the offset exceeds the maximum LOB size allowed by the database, then access exceptions are raised.

Under these input conditions, read subprograms, such as READ , COMPARE , INSTR , and SUBSTR , read until End of Lob/File is reached. For example, for a READ operation on a BLOB or BFILE , if the user specifies offset value of 3 GB and an amount value of 2 GB, then READ reads only (( 4GB-1)-3GB ) bytes.

Functions with NULL or invalid input values for parameters return a NULL . Procedures with NULL values for destination LOB parameters raise exceptions.

Operations involving patterns as parameters, such as COMPARE , INSTR , and SUBSTR do not support regular expressions or special matching characters (such as % in the LIKE operator in SQL) in the pattern parameter or substrings.

The End Of LOB condition is indicated by the READ procedure using a NO_DATA_FOUND exception. This exception is raised only upon an attempt by the user to read beyond the end of the LOB . The READ buffer for the last read contains 0 bytes.

For consistent LOB updates, you must lock the row containing the destination LOB before making a call to any of the procedures (mutators) that modify LOB data.

Unless otherwise stated, the default value for an offset parameter is 1, which indicates the first byte in the BLOB or BFILE data, and the first character in the CLOB or NCLOB value. No default values are specified for the amount parameter — you must input the values explicitly.

You must lock the row containing the destination internal LOB before calling any subprograms that modify the LOB , such as APPEND , COPY , ERASE , TRIM , or WRITE . These subprograms do not implicitly lock the row containing the LOB .

Rules and Limits Specific to External Files (BFILEs)

The subprograms COMPARE , INSTR , READ , SUBSTR , FILECLOSE , FILECLOSEALL and LOADFROMFILE operate only on an opened BFILE locator; that is, a successful FILEOPEN call must precede a call to any of these subprograms.

For the functions FILEEXISTS , FILEGETNAME and GETLENGTH , a file's open/close status is unimportant; however, the file must exist physically, and you must have adequate privileges on the DIRECTORY object and the file.

DBMS_LOB does not support any concurrency control mechanism for BFILE operations.

In the event of several open files in the session whose closure has not been handled properly, you can use the FILECLOSEALL subprogram to close all files opened in the session and resume file operations from the beginning.

If you are the creator of a DIRECTORY , or if you have system privileges, then use the CREATE OR REPLACE , DROP , and REVOKE statements in SQL with extreme caution.

If you, or other grantees of a particular directory object, have several open files in a session, then any of the preceding commands can adversely affect file operations. In the event of such abnormal termination, your only choice is to invoke a program or anonymous block that calls FILECLOSEALL , reopen your files, and restart your file operations.

All files opened during a user session are implicitly closed at the end of the session. However, Oracle strongly recommends that you close the files after both normal and abnormal termination of operations on the BFILE.

In the event of normal program termination, proper file closure ensures that the number of files that are open simultaneously in the session remains less than SESSION_MAX_OPEN_FILES .

In the event of abnormal program termination from a PL/SQL program, it is imperative that you provide an exception handler that ensures closure of all files opened in that PL/SQL program. This is necessary because after an exception occurs, only the exception handler has access to the BFILE variable in its most current state.

After the exception transfers program control outside the PL/SQL program block, all references to the open BFILEs are lost. The result is a larger open file count which may or may not exceed the SESSION_MAX_OPEN_FILES value.

For example, consider a READ operation past the end of the BFILE value, which generates a NO_DATA_FOUND exception:

After the exception has occurred, the BFILE locator variable file goes out of scope, and no further operations on the file can be done using that variable. Therefore, the solution is to use an exception handler:

In general, you should ensure that files opened in a PL/SQL block using DBMS_LOB are closed before normal or abnormal termination of the block.

Maximum LOB Size

The maximum size of a LOB supported by the database is equal to the value of the db_block_size initialization parameter times the value 4294967295. This allows for a maximum LOB size ranging from 8 terabytes to 128 terabytes.

Maximum Buffer Size

The maximum buffer size, 32767 bytes, is represented by maxbufsize .

Operational Notes

All DBMS_LOB subprograms work based on LOB locators. For the successful completion of DBMS_LOB subprograms, you must provide an input locator that represents a LOB that already exists in the database tablespaces or external file system. See also Chapter 1 of Oracle Database Application Developer's Guide - Large Objects.

To use LOBs in your database, you must first use SQL data definition language (DDL) to define the tables that contain LOB columns.

Internal LOBs

To populate your table with internal LOBs after LOB columns are defined in a table, you use the SQL data manipulation language (DML) to initialize or populate the locators in the LOB columns.

External LOBs

For an external LOB (BFILE) to be represented by a LOB locator, you must:

Ensure that a DIRECTORY object representing a valid, existing physical directory has been defined, and that physical files (the LOBs you plan to add) exist with read permission for the database. If your operating system uses case-sensitive path names, then be sure you specify the directory in the correct format.

Pass the DIRECTORY object and the filename of the external LOB you are adding to the BFILENAME function to create a LOB locator for your external LOB .

Once you have completed these tasks, you can insert or update a row containing a LOB column using the given LOB locator.

After the LOBs are defined and created, you can then SELECT from a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for access to the LOB value.

For details on the different ways to do this, you must refer to the section of the Oracle Database Application Developer's Guide - Large Objects that describes "Accessing External LOBs (BFILEs)."

Temporary LOBs

The database supports the definition, creation, deletion, access, and update of temporary LOBs. Your temporary tablespace stores the temporary LOB data. Temporary LOBs are not permanently stored in the database. Their purpose is mainly to perform transformations on LOB data.

For temporary LOBs, you must use the OCI, PL/SQL, or another programmatic interface to create or manipulate them. Temporary LOBs can be either BLOBs , CLOBs , or NCLOBs .

A temporary LOB is empty when it is created. By default, all temporary LOBs are deleted at the end of the session in which they were created. If a process dies unexpectedly or if the database crashes, then temporary LOBs are deleted, and the space for temporary LOBs is freed.

There is also an interface to let you group temporary LOBs together into a logical bucket. The duration represents this logical store for temporary LOBs. Each temporary LOB can have separate storage characteristics, such as CACHE / NOCACHE . There is a default store for every session into which temporary LOBs are placed if you don't specify a specific duration. Additionally, you are able to perform a free operation on durations, which causes all contents in a duration to be freed.

There is no support for consistent read (CR), undo, backup, parallel processing, or transaction management for temporary LOBs. Because CR and rollbacks are not supported for temporary LOBs, you must free the temporary LOB and start over again if you encounter an error.

Because CR, undo, and versions are not generated for temporary LOBs, there is potentially a performance impact if you assign multiple locators to the same temporary LOB . Semantically, each locator should have its own copy of the temporary LOB .

A copy of a temporary LOB is created if the user modifies the temporary LOB while another locator is also pointing to it. The locator on which a modification was performed now points to a new copy of the temporary LOB . Other locators no longer see the same data as the locator through which the modification was made. A deep copy was not incurred by permanent LOBs in these types of situations, because CR snapshots and version pages enable users to see their own versions of the LOB cheaply.

You can gain pseudo- REF semantics by using pointers to locators in OCI and by having multiple pointers to locators point to the same temporary LOB locator, if necessary. In PL/SQL, you must avoid using more than one locator for each temporary LOB . The temporary LOB locator can be passed by reference to other procedures.

Because temporary LOBs are not associated with any table schema, there are no meanings to the terms in-row and out-of-row temporary LOBs. Creation of a temporary LOB instance by a user causes the engine to create and return a locator to the LOB data. The PL/SQL DBMS_LOB package, PRO*C, OCI, and other programmatic interfaces operate on temporary LOBs through these locators just as they do for permanent LOBs.

There is no support for client side temporary LOBs. All temporary LOBs reside in the server.

Temporary LOBs do not support the EMPTY_BLOB or EMPTY_CLOB functions that are supported for permanent LOBs. The EMPTY_BLOB function specifies the fact that the LOB is initialized, but not populated with any data.

A temporary LOB instance can only be destroyed by using OCI or the DBMS_LOB package by using the appropriate FREETEMPORARY or OCIDurationEnd statement.

A temporary LOB instance can be accessed and modified using appropriate OCI and DBMS_LOB statements, just as for regular permanent internal LOBs. To make a temporary LOB permanent, you must explicitly use the OCI or DBMS_LOB COPY command, and copy the temporary LOB into a permanent one.

Security is provided through the LOB locator. Only the user who created the temporary LOB is able to see it. Locators are not expected to be able to pass from one user's session to another. Even if someone did pass a locator from one session to another, they would not access the temporary LOBs from the original session. Temporary LOB lookup is localized to each user's own session. Someone using a locator from somewhere else is only able to access LOBs within his own session that have the same LOB ID. Users should not try to do this, but if they do, they are not able to affect anyone else's data.

The database keeps track of temporary LOBs for each session in a v$ view called V$TEMPORARY_LOBS , which contains information about how many temporary LOBs exist for each session. V$ views are for DBA use. From the session, the database can determine which user owns the temporary LOBs. By using V$TEMPORARY_LOBS in conjunction with DBA_SEGMENTS , a DBA can see how much space is being used by a session for temporary LOBs. These tables can be used by DBAs to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.

The following notes are specific to temporary LOBs:

All functions in DBMS_LOB return NULL if any of the input parameters are NULL . All procedures in DBMS_LOB raise an exception if the LOB locator is input as NULL .

Operations based on CLOBs do not verify if the character set IDs of the parameters ( CLOB parameters, VARCHAR2 buffers and patterns, and so on) match. It is the user's responsibility to ensure this.

Data storage resources are controlled by the DBA by creating different temporary tablespaces. DBAs can define separate temporary tablespaces for different users, if necessary.

Temporary LOBs still adhere to value semantics in order to be consistent with permanent LOBs and to try to conform to the ANSI standard for LOBs. As a result, each time a user does an OCILobLocatatorAssign , or the equivalent assignment in PL/SQL, the database makes a copy of the temporary LOB .

Each locator points to its own LOB value. If one locator is used to create a temporary LOB , and then is assigned to another LOB locator using OCILobLOcatorAssign in OCI or through an assignment operation in PL/SQL, then the database copies the original temporary LOB and causes the second locator to point to the copy.

In order for users to modify the same LOB , they must go through the same locator. In OCI, this can be accomplished fairly easily by using pointers to locators and assigning the pointers to point to the same locator. In PL/SQL, the same LOB variable must be used to update the LOB to get this effect.

The following example shows a place where a user incurs a copy, or at least an extra round-trip to the server.

The PL/SQL compiler makes temporary copies of actual arguments bound to OUT or IN OUT parameters. If the actual parameter is a temporary LOB , then the temporary copy is a deep (value) copy.

The following PL/SQL block illustrates the case where the user incurs a deep copy by passing a temporary LOB as an IN OUT parameter.

To minimize deep copies on PL/SQL parameter passing, use the NOCOPY compiler hint where possible.

The duration parameter passed to dbms_lob.createtemporary() is a hint. The duration of the new temp LOB is the same as the duration of the locator variable in PL/SQL. For example, in the preceding program block, the program variable a has the duration of the residing frame. Therefore at the end of the block, memory of a will be freed at the end of the function.

If a PL/SQL package variable is used to create a temp LOB , it will have the duration of the package variable, which has a duration of SESSION .

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

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

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

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

Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются 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	Строку произвольного набора символов в Юникод

Функция 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 ;
для преобразования именованной коллекции определенного типа в именованную коллекцию другого типа нужно, чтобы элементы обеих коллекций имели одинаковый тип;

Рис. 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 и 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 может приводить к разным результатам — в отличие от внутренних больших объектов, соответствующих модели логической целостности чтения.

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 .

Я пытаюсь увидеть из консоли SQL, что находится внутри Oracle BLOB.

Я знаю, что он содержит довольно большой объем текста, и я хочу просто увидеть текст, но следующий запрос указывает только на то, что в этом поле есть BLOB:

Результат, который я получаю, не совсем то, что я ожидал:

Итак, какие магические заклинания я могу сделать, чтобы превратить BLOB в его текстовое представление?

PS: Я просто пытаюсь просмотреть содержимое BLOB-объекта из консоли SQL (Eclipse Data Tools), а не использовать его в коде.

Прежде всего, вы можете захотеть хранить текст в столбцах CLOB / NCLOB вместо BLOB, который предназначен для двоичных данных (кстати, ваш запрос будет работать с CLOB).

Следующий запрос позволит вам увидеть первые 32767 символов (максимум) текста внутри большого двоичного объекта при условии, что все наборы символов совместимы (исходная CS текста, хранящегося в BLOB, CS базы данных, используемой для VARCHAR2):

SQL Developer также предоставляет эту функциональность:

Дважды щелкните ячейку сетки результатов и щелкните изменить:

Затем в верхней правой части всплывающего окна «Просмотреть как текст» (вы даже можете видеть изображения ..)

Вы можете использовать приведенный ниже SQL для чтения полей BLOB из таблицы.

Если вы хотите искать внутри текста, а не просматривать его, это работает:

Ответ Barn сработал для меня с модификацией, потому что мой столбец не сжат. Быстрое и грязное решение:

Некоторое время я боролся с этим и реализовал решение PL / SQL, но позже понял, что в Toad вы можете просто дважды щелкнуть ячейку сетки результатов, и появится редактор с текстовым содержимым. (я использую Toad v11)

Используйте этот SQL, чтобы получить первые 2000 символов большого двоичного объекта.

Примечание. Это связано с тем, что Oracle не сможет обработать преобразование BLOB, длина которого превышает 2000.

Вы можете попробовать это:

Однако он будет ограничен 4000 байтами

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

Затем запустите select, чтобы получить текст

Надеюсь, это кому-то поможет.

выберите lcase ((insert (insert (insert (insert (hex (BLOB_FIELD), 9,0, '-'), 14,0, '-'), 19,0, '-'), 24,0, '- '))) как FIELD_ID из TABLE_WITH_BLOB, где ;

Используйте функцию TO_CHAR .

Преобразует данные NCHAR , NVARCHAR2 , CLOB или NCLOB в набор символов базы данных. Возвращаемое значение всегда VARCHAR2 .

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