Работа с CLOB-полем в Oracle

← →
Andrew_ (2002-07-16 16:34) [0]

Господа! Подскажите, как записать в CLOB-поле таблицы файл, скажем, файл MS WORD или WAV-файл. Использую библиотеку ODAC.
Спасибо.

← →
Nonam (2002-07-16 16:47) [1]

Для начала, файл MS Word или .wav вы не запишите в CLOB-поле. Надо использовать BLOB-поле. Во вторых, не понятно, какой версией ODAC вы пользуетесь - Standard или Net.

← →
dimis (2002-07-16 18:25) [2]

в хелпе по Odac все очень хорошо описано.
советую почитать

← →
dimis (2002-07-16 18:27) [3]

Привожу нужный кусок

Working with BLOB and CLOB data types

ODAC supports Oracle 8 BLOB and CLOB datatypes. You can retrieve values of LOB fields by TOraQuery component as easy as LONG or LONG ROW fields. The difference in using LOB datatype appears when it is necessary to use these fields in SQL DML and PL/SQL statements.
For BLOB and CLOB columns only the LOB locator (the pointer to data) is stored in the table column; BLOB and CLOB data is stored in separate tablespaces. In the case of LONG or LONG RAW the entire value is stored in the table column. When you access LOB column, it is the locator which is returned. When you access LONG or LONG RAW, the entire value is returned.

This table helps us to illustrate further examples.

CREATE TABLE ClobTable (
Id NUMBER,
Name VARCHAR2(30),
Value CLOB
)

Oracle doesn’t allow to update this table by executing such statement if Value is non initialized LOB locator.

UPDATE ClobTable SET Name = :Name, Value = :Value WHERE Id = :Id

To initialize LOB locator you must use EMPTY_BLOB or EMPTY_CLOB Oracle function. To return initialized locator use RETURNING clause.
For example

UPDATE ClobTable
SET
Name = :Name,
Value = EMPTY_CLOB()
WHERE
Id = :Id
RETURNING
Value
INTO
:Value

In spite of the fact that Value is OUT parameter in this example ODAC writes LOB data to Oracle.
If you need use stored procedure to store LOB value it might be written in such a way

CREATE OR REPLACE
PROCEDURE ClobTableUpdate (p_Id NUMBER, p_Name VARCHAR2,
p_Value OUT CLOB)
is
begin
UPDATE ClobTable
SET
Name = p_Name,
Value = EMPTY_CLOB()
WHERE
Id = p_Id
RETURNING
Value
INTO
p_Value;
end;

Note Value parameter is declared as OUT but ParamType must have ptInput value to write lob data to Oracle.

OraStroredProc1.StoredProcName := "ClobTableUpdate";
OraStroredProc1.Prepare;
OraStroredProc1.ParamByName("p_Id").AsInteger := Id;
OraStroredProc1.ParamByName("p_Name").AsString := Name;
OraStroredProc1.ParamByName("Value").ParamType := ptInput;
OraStroredProc1.ParamByName("Value").AsCLOBLocator.
LoadFromFile(FileName);
OraStroredProc1.Execute;

It is important that ODAC uses ParamType property of parameters in LOB operations. If ParamType is ptInput ODAC writes data to server, if ParamType is ptOutput it reads data.
You can also use dtBlob and dtMemo datatypes with LOB parameters to write ordinary DML statements. In such a case Oracle automatically converts LONG and LONG ROW values to CLOB or BLOB data.

Работа с CLOB-полем в Oracle Найти похожие ветки