Page 1 of 3
ORA-22835 on saving BLOB to LONG RAW column
Posted: Wed 02 Jan 2008 11:38
by upscene
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
Posted: Thu 03 Jan 2008 11:04
by Plash
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;
Posted: Thu 03 Jan 2008 11:17
by upscene
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
Posted: Fri 04 Jan 2008 10:36
by Plash
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.
Posted: Fri 04 Jan 2008 10:47
by upscene
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
Posted: Fri 04 Jan 2008 11:45
by Plash
Yes, the same code will work for LONG RAW and BLOB, if you replace TOraSQL with TOraQuery component and set TemporaryLobUpdate option to True.
Posted: Fri 04 Jan 2008 11:51
by upscene
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
Posted: Tue 08 Jan 2008 09:34
by Plash
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.
Posted: Tue 08 Jan 2008 10:38
by upscene
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
Posted: Tue 08 Jan 2008 11:43
by Plash
ftOraBlob corresponds to Oracle BLOB data type.
ftBlob corresponds to Oracle LONG RAW data type.
Posted: Tue 08 Jan 2008 14:46
by upscene
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
Posted: Wed 09 Jan 2008 09:00
by Plash
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.
Posted: Wed 09 Jan 2008 09:05
by upscene
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
Posted: Wed 09 Jan 2008 11:52
by upscene
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
Posted: Thu 10 Jan 2008 08:54
by Plash
I don't have exact information about all cases when ftBlob does not work for BLOBs.
You should use ftOraBlob for Oracle BLOBs.