ORA-22835 on saving BLOB to LONG RAW column

upscene · Post by **upscene** » Wed 02 Jan 2008 11:38

Hi,

I have a table with a LONG RAW column and I'm trying insert a long string value ( a binary image coming from a MS SQL database ) by doing the following:

Code: Select all

if FCommand.Params[Index].Datatype in [ftBLOB, ftOraBLOB]
  then begin
         FCommand.Params[Index].ParamType := ptInput;
         FCommand.Params[Index].AsOraBlob.OCISvcCtx := FCommand.Session.OCISvcCtx;
         FCommand.Params[Index].AsOraBlob.CreateTemporary(ltBlob);
         ss := TStringStream.Create(Value);
         try
           FCommand.Params[Index].AsOraBlob.LoadFromStream(ss);
           FCommand.Params[Index].AsOraBlob.WriteLob;
         finally
           ss.Free;
         end;
       end

As you can see, I'm creating a temporary blob and write the string value.

FCommand is a TOraSQL component, I set the datatype of the parameter to ftBLOB.

However, I'm getting:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 21626, maximum: 2000)

How should I write to a (LONG) RAW column?

--
Martijn Tonies

Plash · Post by **Plash** » Thu 03 Jan 2008 11:04

You should not use BLOB to insert a LONG RAW value. Use code like the following:

Code: Select all

if FCommand.Params[Index].Datatype = ftBLOB then
begin 
  FCommand.Params[Index].ParamType := ptInput; 
  FCommand.Params[Index].AsBlob := Value;
end;

upscene · Post by **upscene** » Thu 03 Jan 2008 11:17

Does this work with BLOB and CLOB columns as well?

edit: I've tried the same code on a BLOB column and I get this error:
ORA-01460: unimplemented or unreasonable conversion requested

I've also tried SetBlobData, but no luck. Is there a method that works with both BLOBs and LONG RAWs?

--
Martijn Tonies

Plash · Post by **Plash** » Fri 04 Jan 2008 10:36

To insert or update BLOB and CLOB values, you should use temporary lob or INSERT or UPDATE statement with RETURNING clause.

In ODAC 6 we have added TemporaryLob update option to TOraQuery. If you set this option to True, you can use the code I provided above. But you should use AsString property instead of AsBlob because AsBlob property changes DataType of parameter to ftBlob.

upscene · Post by **upscene** » Fri 04 Jan 2008 10:47

I'm using TOraSQL here, it doesn't seem to have such a property. Perhaps in protected?

Either way, if I use the TemporaryLob property, will the same code work for LONG RAW and BLOB? Cause that's what's important to me.

--
Martijn Tonies
www.upscene.com

Plash · Post by **Plash** » Fri 04 Jan 2008 11:45

Yes, the same code will work for LONG RAW and BLOB, if you replace TOraSQL with TOraQuery component and set TemporaryLobUpdate option to True.

upscene · Post by **upscene** » Fri 04 Jan 2008 11:51

I'm using AsString now and have set the TemporaryLobUpdate to True.

I still get this error on a LONG RAW:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 21626, maximum: 2000)

--
Martijn Tonies
Upscene Productions

Plash · Post by **Plash** » Tue 08 Jan 2008 09:34

We could not reproduce the problem. Please send to odac*crlab*com a complete small sample that demonstrates the problem, including script to create database objects.

We'll add TemporaryLobUpdate property to TOraSQL in the next ODAC build.

upscene · Post by **upscene** » Tue 08 Jan 2008 10:38

Done.

When trying to reproduce, I noticed a difference between using ftOraBLOB as the field type for the parameter and ftBLOB.

Can you clarify the difference? When should they be used?

--
Martijn Tonies
Upscene Productions

Plash · Post by **Plash** » Tue 08 Jan 2008 11:43

ftOraBlob corresponds to Oracle BLOB data type.
ftBlob corresponds to Oracle LONG RAW data type.

upscene · Post by **upscene** » Tue 08 Jan 2008 14:46

It seems that when I use ftBLOB for both the BLOB and LONG RAW, it works fine.

Can you confirm this?

Is this supposed to work?

--
Martijn Tonies
Upscene Productions

Plash · Post by **Plash** » Wed 09 Jan 2008 09:00

When you use ftBlob for BLOB parameters, a parameter value is passed to the server as LONG RAW. Then the server converts this value to BLOB. This works, but I recommend to use ftOraBlob for BLOB parameters because using ftBlob for Oracle BLOBs can cause some problem, especially when connecting in Direct mode.

upscene · Post by **upscene** » Wed 09 Jan 2008 09:05

I'm having trouble in my application to distinguish between ftBLOB and ftOraBLOB, basically, it's all "blob" to me.

What kind of problems could arise that you're aware of?

--
Martijn Tonies
Upscene Productions

upscene · Post by **upscene** » Wed 09 Jan 2008 11:52

btw, new attemps with BLOB as ftBLOB and it seems that it won't work on binary data, but only on text data.

Can you confirm that?

--
Martijn Tonies
Upscene Productions

Plash · Post by **Plash** » Thu 10 Jan 2008 08:54

I don't have exact information about all cases when ftBlob does not work for BLOBs.
You should use ftOraBlob for Oracle BLOBs.