Что такое clob oracle

/привет/мир/etc

Непериодические заметки о программировании

понедельник, 25 мая 2015 г.

О работе с большими объектами в СУБД Oracle

Указанные типы данных можно использовать в СУБД Oracle для определения столбцов таблиц, атрибутов объектных типов и переменных PL/SQL.

Вот краткая характеристика этих типов:

Итак, по месту хранения LOB’ы делятся на

а по содержанию на

Для работы с LOB’ами cоздам таблицу со столбцами соответствующих типов:

Вместе с таблицей были созданы сегменты для хранения больших объектов:

Значение типа LOB может быть

Пустые LOB значения создаются функциями EMPTY_CLOB и EMPTY_BLOB :

Начиная с версии 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_RAW недоступна в SQL и, отсюда, возможности конвертации между BLOB и RAW в SQL ограничены. Например:

Зато в PL/SQL работают явная и неявная конвертации между BLOB и RAW:

Рассмотренные возможности по работе со значениями LOB как с VARCHAR2 получили название SQL семантика для LOB’ов (SQL semаntics for LOBs). С их использованием связаны некоторые ограничения, как мы увидим ниже.

С точки зрения PL/SQL большие объекты делятся на:

В вышеприведенных примерах с PL/SQL мы имели дело с временными LOB’ами.

Изменения внутренних постоянных LOB’ов (в отличие от внешних или временных) в СУБД Oracle подчиняются транзакциям. Убедимся в этом, отменив только что сделанные изменения:

Типичный алгоритм для чтения или изменения постоянного LOB’а с помощью PL/SQL таков:

Приведу пример выгрузки данных из постоянного CLOB’а во внешний файл. Для доступа к внешним файлам потребуется создать директорию, например:

Альтернативно, можно выгрузить CLOB во внешний файл, пользуясь SQL семантикой для LOB и не прибегая к DBMS_LOB :

Тип данных BFILE содержит указатель на внешний файл, который

Пример чтения BFILE и записи во временный BLOB:

Вместо чтения BFILE по частям пакет DBMS_LOB позволяет

Пример загрузки текстового файла во временный CLOB (аналогично можно загрузить и в постоянный CLOB):

Приведу неполный список процедур и функций DBMS_LOB для чтения, анализа и изменения значений BLOB, CLOB и NCLOB:

Следующий эксперимент покажет разницу между внутренними и внешними постоянными LOB’ами. Помещу в поле bfile_col таблицы lobs_tab объект BFILE и скопирую единственную строку таблицы во вторую строку:

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

Аналогично, при присваивании BLOB и (N)CLOB переменных в PL/SQL мы получаем независимые копии LOB’ов:

Источник

10 PL/SQL Semantics for LOBs

This chapter contains these topics:

PL/SQL Statements and Variables

In PL/SQL, a number of semantic changes have been made as described in the previous paragraphs.

The following discussions, concerning CLOBs and VARCHAR2s, also apply to BLOBs and RAWs, 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

Explicit Conversion Functions

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.

VARCHAR2 and CLOB in PL/SQL Built-In Functions

PL/SQL VARCHAR2 functions and operators can take CLOB s as arguments or operands.

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

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

PL/SQL CLOB Comparison Rules

CLOBs Follow the VARCHAR2 Collating Sequence

It makes no sense to compare CLOB s with non-character data, or with BLOB s. An error is returned in these cases.

Источник

16 Working with LOBs and BFILEs

This chapter describes how to use Java Database Connectivity (JDBC) and the oracle.sql.* classes to access and manipulate large object (LOB) and binary file (BFILE) locators and data. This chapter contains the following sections:

Oracle Extensions for LOBs and BFILEs

LOBs are stored in a way that optimizes space and provides efficient access. The JDBC drivers provide support for two types of LOB: binary large object (BLOB), which is used for unstructured binary data, and character large object (CLOB), which is used for character data. BLOB and CLOB data is accessed and referenced by using a locator that is stored in the database table and points to the BLOB or CLOB data, which is outside the table.

BFILEs are large binary data objects stored in operating system files outside of database tablespaces. These files use reference semantics. They can also be located on tertiary storage devices, such as hard disks, CD-ROMs, PhotoCDs, and DVDs. As with BLOBs and CLOBs, a BFILE is accessed and referenced by a locator which is stored in the database table and points to the BFILE data.

To work with LOB data, you must first obtain a LOB locator. Then you can read or write LOB data and perform data manipulation.

The JDBC drivers support the following oracle.sql.* classes for BLOBs, CLOBs, and BFILEs:

The oracle.sql.BLOB and oracle.sql.CLOB classes implement the java.sql.Blob and java.sql.Clob interfaces, respectively. In contrast, BFILE is an Oracle extension, without a corresponding java.sql interface.

Instances of these classes contain only the locators for these data types, not the data. After accessing the locators, you must perform some additional steps to access the data.

Working with BLOBs and CLOBs

This section describes how to read and write data to and from BLOBs and CLOBs in Oracle Database, using LOB locators. This section covers the following topics:

Getting and Passing BLOB and CLOB Locators

Standard as well as Oracle-specific getter and setter methods are available for retrieving or passing LOB locators from or to the database. This section covers the following topics:

Retrieving BLOB and CLOB Locators

Given a standard JDBC result set or callable statement that includes BLOB or CLOB locators, you can access the locators by using standard getter methods. You can use the standard getBlob and getClob methods, which return java.sql.Blob and Clob objects, respectively.

All the standard and Oracle-specific getter methods discussed here take either an int column index or a String column name as input.

You can also use the getOracleObject method, which returns an oracle.sql.Datum object, and cast the output appropriately.

Example: Getting BLOB and CLOB Locators from a Result Set

First, select the LOB locators into a standard result set, then get the LOB data into appropriate Java classes:

Example: Getting a CLOB Locator from a Callable Statement

The callable statement methods for retrieving LOBs are identical to the result set methods.

This example registers OracleTypes.CLOB as the type code of the output parameter.

Passing BLOB and CLOB Locators

Given a standard JDBC prepared statement or callable statement, you can use standard setter methods to pass L OB locators. These methods are defined as follows:

Use the set OracleObject method, which simply specifies an oracle.sql.Datum input.

Example: Passing a BLOB Locator to a Prepared Statement

Example: Passing a CLOB Locator to a Callable Statement

Reading and Writing BLOB and CLOB Data

Once you have a LOB locator, you can use JDBC methods to read and write the LOB data. LOB data is materialized as a Java array or stream. However, unlike most Java streams, a locator representing the LOB data is stored in the table. Thus, you can access the LOB data at any time during the life of the connection.

To read and write the LOB data, use the methods in the oracle.sql.BLOB or oracle.sql.CLOB class, as appropriate. These classes provide functionality such as reading from the LOB into an input stream, writing from an output stream into a LOB, determining the length of a LOB, and closing a LOB.

To read and write LOB data, you can use these methods:

To read from a BLOB, use the getBinaryStream method of an oracle.sql.BLOB object to retrieve the entire BLOB as an input stream. This returns a java.io.InputStream object.

As with any InputStream object, use one of the overloaded read methods to read the LOB data and use the close method when you finish.

To write to a BLOB, use the setBinaryStream method of an oracle.sql.BLOB object to retrieve the BLOB as an output stream. This returns a java.io.OutputStream object to be written back to the BLOB.

As with any OutputStream object, use one of the overloaded write methods to update the LOB data and use the close method when you finish.

To read from a CLOB, use the getAsciiStream or getCharacterStream method of an oracle.sql.CLOB object to retrieve the entire CLOB as an input stream. The getAsciiStream method returns an ASCII input stream in a java.io.InputStream object. The getCharacterStream method returns a Unicode input stream in a java.io.Reader object.

As with any InputStream or Reader object, use one of the overloaded read methods to read the LOB data and use the close method when you finish.

To write to a CLOB, use the setAsciiStream or setCharacterStream method of an oracle.sql.CLOB object to retrieve the CLOB as an output stream to be written back to the CLOB. The setAsciiStream method returns an ASCII output stream in a java.io.OutputStream object. The setCharacterStream method returns a Unicode output stream in a java.io.Writer object.

As with any Stream or Writer object, use one of the overloaded write methods to update the LOB data and use the flush and close methods when you finish.

The stream write methods described in this section write directly to the database when you write to the output stream. You do not need to run an UPDATE to write the data. However, you need to call close or flush to ensure all changes are written. CLOBs and BLOBs are transaction controlled. After writing to either, you must commit the transaction for the changes to be permanent. BFILEs are not transaction controlled. Once you write to them the changes are permanent, even if the transaction is rolled back, unless the external file system does something else.

When writing to or reading from a CLOB, the JDBC drivers perform all character set conversions for you.

Example: Reading BLOB Data

Use the getBinaryStream method of the oracle.sql.BLOB class to read BLOB data. The getBinaryStream method provides access to the BLOB data through a binary stream.

The following example uses the getBinaryStream method to read BLOB data through a byte stream and then reads the byte stream into a byte array, returning the number of bytes read, as well.

Example: Reading CLOB Data

The following example uses the getCharacterStream method to read CLOB data into a Unicode character stream. It then reads the character stream into a character array, returning the number of characters read, as well.

The next example uses the getAsciiStream method of the oracle.sql.CLOB class to read CLOB data through an ASCII character stream. It then reads the ASCII stream into a byte array, returning the number of bytes read, as well.

Example: Writing BLOB Data

Use the setBinaryOutputStream method of an oracle.sql.BLOB object to write BLOB data.

The following example reads a vector of data into a byte array, then uses the setBinaryOutputStream method to write an array of character data to a BLOB.

Example: Writing CLOB Data

Use the setCharacterStream method or the setAsciiStream method to write data to a CLOB. The setCharacterStream method returns a Unicode output stream. The setAsciiStream method returns an ASCII output stream.

The following example reads a vector of data into a character array, then uses the setCharacterStream method to write the array of character data to a CLOB.

The next example reads a vector of data into a byte array, then uses the setAsciiStream method to write the array of ASCII data to a CLOB.

Creating and Populating a BLOB or CLOB Column

Create and populate a BLOB or CLOB column in a table by using SQL statements.

You cannot construct a new BLOB or CLOB locator in your application with a Java new statement. You must create the locator through a SQL operation, and then select it into your application or with the createTemporary or empty_lob methods.

Create a BLOB or CLOB column in a table with the SQL CREATE TABLE statement, then populate the LOB. This includes creating the LOB entry in the table, obtaining the LOB locator, and then copying the data into the LOB.

Creating a BLOB or CLOB Column in a New Table

To create a BLOB or CLOB column in a new table, run the SQL CREATE TABLE statement. The following example code creates a BLOB column in a new table. This example assumes that you have already created your Connection object conn and Statement object stmt :

In this example, the VARCHAR2 column designates a row number, such as 1 or 2, and the BLOB column stores the locator of the BLOB data.

Populating a BLOB or CLOB Column in a New Table

The following example writes the john.gif file to a BLOB:

Begin by using SQL statements to create the BLOB entry in the table. Use the empty_blob function to create the BLOB locator.

Get the BLOB locator from the table.

You must disable auto-commit mode.

Declare a file handler for the john.gif file, then print the length of the file. This value will be used later to ensure that the entire file is read into the BLOB. Next, create a FileInputStream object to read the contents of the file, and an OutputStream object to retrieve the BLOB as a stream.

Call getBufferSize to retrieve the ideal buffer size to use in writing to the BLOB, then create the buffer byte array.

Once your data is in the BLOB or CLOB, you can manipulate the data.

Accessing and Manipulating BLOB and CLOB Data

Once you have your BLOB or CLOB locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you first must select their locators from a result set or from a callable statement.

This example is a continuation of the example in the previous section. It uses the SQL SELECT statement to select the BLOB locator from the table my_blob_table into a result set. The result of the data manipulation is to print the length of the BLOB in bytes.

Additional BLOB and CLOB Features

In addition to what has already been discussed in this chapter, the oracle.sql.BLOB and oracle.sql.CLOB classes have a number of methods for further functionality.

The oracle.sql.CLOB class supports all the character sets that the Oracle data server supports for CLOB types.

Additional BLOB Methods

The oracle.sql.BLOB class includes the following methods:

Closes the BLOB associated with the locator.

Frees the storage used by a temporary BLOB.

Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing BLOB data. This value is a multiple of the chunk size and is close to 32K.

Reads from the BLOB data, starting at a specified point, into a supplied buffer. The method returns a byte array which holds the contents of the BLOB.

This method can give errors for BLOB data that exceed available memory or the size imposed by the Java language.

Returns the Oracle chunking size, which can be specified by the database administrator when the LOB column is first created. This value, in Oracle blocks, determines the size of the chunks of data read or written by the LOB data layer in accessing or modifying the BLOB value. Part of each chunk stores system-related information, and the rest stores LOB data. Performance is enhanced if read and write requests use some multiple of the chunk size.

Returns true if the BLOB is a temporary BLOB.

Returns the length of the BLOB in bytes.

Opens the BLOB associated with the locator.

Opens the BLOB associated with the locator in the mode specified by the argument.

Determines the byte position in the BLOB where a given pattern begins.

Returns the BLOB data for this Blob instance as a stream of bytes beginning at the position in the BLOB specified in the argument.

Writes BLOB data, starting at a specified point, from a supplied buffer.

Trims the value of the BLOB to the length specified by the argument.

Additional CLOB Methods

The oracle.sql.CLOB class includes the following methods:

Closes the CLOB associated with the locator.

Frees the storage used by a temporary CLOB.

Returns the CLOB value designated by the Clob object as a stream of ASCII bytes.

Returns the CLOB value designated by the CLOB object as a stream of ASCII bytes, beginning at the position in the CLOB specified by the argument.

Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing CLOB data. This value is a multiple of the chunk size and is close to 32K.

Returns the CLOB data as a stream of Unicode characters.

Returns the CLOB data as a stream of Unicode characters beginning at the position in the CLOB specified by the argument.

Retrieves characters from a specified point in the CLOB data into a character array.

Returns the Oracle chunking size, which can be specified by the database administrator when the LOB column is first created. This value, in Oracle blocks, determines the size of the chunks of data read or written by the LOB data layer in accessing or modifying the CLOB value. Part of each chunk stores system-related information and the rest stores LOB data. Performance is enhanced if you make read and write requests using some multiple of the chunk size.

Retrieves a substring from a specified point in the CLOB data.

Returns true if and only if the CLOB is a temporary CLOB.

Returns the length of the CLOB in characters.

Opens the CLOB associated with the locator.

Opens the CLOB associated with the locator in the mode specified by the argument.

Determines the character position in the CLOB at which a given substring begins.

Writes characters from a character array to a specified point in the CLOB data.

Returns a java.io.OutputStream object to write data to the CLOB as a stream. The data is written beginning at the position in the CLOB specified by the argument.

Returns a java.io.Writer object to write data to the CLOB as a stream. The data is written beginning at the position in the CLOB specified by the argument.

setString(long pos, String str)

Writes a string to a specified point in the CLOB data.

Trims the value of the CLOB to the length specified by the argument.

Creating Empty LOBs

public static BLOB getEmptyBLOB() throws SQLException

public static CLOB getEmptyCLOB() throws SQLException

A JDBC driver creates an empty LOB instance without making database round trips. You can use empty LOBs in the following:

set XXX methods of the OraclePreparedStatement class

update XXX methods of updatable result sets

attributes of STRUCT objects

elements of ARRAY objects

Data Interface for LOBs

For the JDBC Oracle Call Interface (OCI) and Thin drivers there is no limitation on the size of the byte array or String and no limit on the length specified for the stream functions except the limits imposed by the Java language, which is that array sizes are limited to positive Java int or 2147483648 elements.

For the server side internal driver there is currently a limitation of 4000 bytes for operations on SQL statements, such as an INSERT statement. The limitation does not apply for PL/SQL statements. There is a simple workaround for an INSERT statement, which is to wrap it in a PL/SQL block, as follows:

You must bear in mind the following automatic switching of the input mode for large data:

For SQL statements:

setBytes switches to setBinaryStream for data larger than 2000 bytes

setString switches to setCharacterStream for data larger than 32766 characters

setBytes switches to setBinaryStream for data larger than 2000 bytes and to setBytesForBlob for data larger that 32512 bytes

setString switches to setStringForClob for string data larger than 32512 bytes in the database character set or national character set depending on whether setFormOfUse has been used for NCLOB parameters

This will have impact on some programs, which formerly got ORA-17157 errors for attempts to use setString for String values larger than 32766 characters. Now, depending on the type of the target parameter an error may occur while the application is executed or the operation may succeed.

Another impact is that the automatic switching may result in additional server side parsing to adapt to the change in the parameter type. This would result in a performance effect if the data sizes vary above and below the limit for repeated executions of the statement. Switching to the stream modes will effect batching as well.

Oracle Database 10 g release 1 (10.1) has the SetBigStringTryClob connection property. Setting this property causes the standard setString method to switch to setStringForClob method for large data. This property is no longer used or needed. The setBytesForBlob and setStringForClob methods create temporary LOBs, which are automatically freed when the statement is executed or closed before execution.

CallableSatement and IN OUT Parameter

It is a PL/SQL requirement that the Java types used as input and output for an IN OUT parameter must be the same. The automatic switching of types done by the extensions described in this chapter may cause problems with this.

Consider that you have an IN OUT CLOB parameter of a stored procedure and you wish to use setString for setting the value for this parameter. For any IN and OUT parameter, the binds must be of the same type. The automatic switching of the input mode will cause problems unless you are sure of the data sizes. For example, if it is known that neither the input nor output data will ever be larger than 32512 bytes, then you could use setString for the input parameter and register the OUT parameter as Types.VARCHAR and use getString for the output parameter.

A better solution is to change the stored procedure to have separate IN and OUT parameters. That is, if you have:

Another workaround is to use a wrapper block to make the call. The clob_proc procedure can be wrapped with a Java string to use for the prepareCall statement, as follows:

In either case you may use setString on the first parameter and registerOutParameter with Types.CLOB on the second.

Working With Temporary LOBs

You can use temporary LOBs to store transient data. The data is stored in temporary table space rather than regular table space. You should free temporary LOBs after you no longer need them. If you do not, then the space the LOB consumes in temporary table space will not be reclaimed.

You can insert temporary LOBs into a table. When you do this, a permanent copy of the LOB is created and stored. Inserting a temporary LOB may be preferable for some situations. For example, if the LOB data is relatively small so that the overhead of copying the data is less than the cost of a database round trip to retrieve the empty locator. Remember that the data is initially stored in the temporary table space on the server and then moved into permanent storage.

You can free a temporary LOB by calling the freeTemporary method. Free any temporary LOBs before ending the session or call. Otherwise, the storage used by the temporary LOB will not be reclaimed.

Failure to free a temporary LOB will result in the storage used by that LOB in the database being unavailable. Frequent failure to free temporary LOBs will result in filling up temporary table space with unavailable LOB storage.

Creating Temporary NCLOBs

You create temporary national character large objects (NCLOBs) using a variant of the createTemporary method.

Using Open and Close With LOBs

You do not have to open and close your LOBs. You may choose to open and close them for performance reasons.

If you do not wrap LOB operations inside an Open/Close call operation, then each modification to the LOB will implicitly open and close the LOB, thereby firing any triggers on a domain index. Note that in this case, any domain indexes on the LOB will become updated as soon as LOB modifications are made. Therefore, domain LOB indexes are always valid and may be used at any time.

If you wrap your LOB operations inside the Open/Close call operation, then triggers will not be fired for each LOB modification. Instead, the trigger on domain indexes will be fired at the Close call. For example, you might design your application so that domain indexes are not be updated until you call the close method. However, this means that any domain indexes on the LOB will not be valid in-between the Open/Close calls.

An error occurs if you commit the transaction before closing all LOBs that were opened by the transaction. The openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed, but the triggers for domain indexing are not fixed.

Working with BFILEs

This section describes how to read and write data to and from BFILEs, using file locators. This section covers the following topics:

Источник