ORA-22835 on saving BLOB to LONG RAW column

[upscene]

As I said, I do not always know the exact datatype of the target column (LONG RAW or BLOB).

Is this an Oracle specific thing or does this issue only happen in ODAC?

I'm a bit stuck here

--
Martijn Tonies
Upscene Productions

[decimator]

Hello,

I have a similar (I think) question.

I'm trying to insert LONG RAW field as a parameter
using dbExpress'es TSQLQuery. My paramater is
ptInput and ftBlob. When I try to execute the SQL
I get the following:

ORA-01465: invalid hex number

Any suggestions?

I read somewhere that rawtohex() function in Oracle
might help. It actually helped me to insert a text
file in the field but when I tried inserting a jpeg - it
inserted only 4 bytes or something like that (the
field after inserting certainly didn't contain picture).

Thank you in advance.

Momchil Ivanov

[Plash]

As I said, I do not always know the exact datatype of the target column (LONG RAW or BLOB).

Is this an Oracle specific thing or does this issue only happen in ODAC?

I'm a bit stuck here

It is Oracle specific. In some cases it cannot convert LONG RAW value to BLOB.

[Plash]

Hello,

I have a similar (I think) question.

I'm trying to insert LONG RAW field as a parameter
using dbExpress'es TSQLQuery. My paramater is
ptInput and ftBlob. When I try to execute the SQL
I get the following:

ORA-01465: invalid hex number

Any suggestions?

This error occurs when parameter data type is ftString or ftUnknown. In this case Oracle consider that value is in hex format.

The data type can be changed to ftString if you set value using AsString property. You should use AsBlob property to set value of LONG RAW parameter.

[upscene]

As I said, I do not always know the exact datatype of the target column (LONG RAW or BLOB).

Is this an Oracle specific thing or does this issue only happen in ODAC?

I'm a bit stuck here

It is Oracle specific. In some cases it cannot convert LONG RAW value to BLOB.

This issue is still bugging me: I need a routine that can write binary data to both BLOB and LONG RAW.

I cannot change the datatype, either ftBlob or ftOraBlob.

Is that possible?

[upscene]

As I said, I do not always know the exact datatype of the target column (LONG RAW or BLOB).

Is this an Oracle specific thing or does this issue only happen in ODAC?

I'm a bit stuck here

It is Oracle specific. In some cases it cannot convert LONG RAW value to BLOB.

This issue is still bugging me: I need a routine that can write binary data to both BLOB and LONG RAW.

I cannot change the datatype, either ftBlob or ftOraBlob.

Is that possible?

Any tips? Is there possible code that works for both datatypes?

[AlexP]

You can write binary data to the BLOB and LONG RAW fields like this:

SimpleDataSet1.DataSet.ParamByName('TST_LONG_ROW').LoadFromFile('e:\1.bmp',ftBlob);
SimpleDataSet1.DataSet.ParamByName('TST_BLOB').LoadFromFile('e:\1.bmp',ftBlob);

where text query is:

INSERT INTO TST_TABLE
(TST_BLOB, TST_LONG_ROW)
VALUES
(:TST_BLOB, :TST_LONG_ROW)

and the types of param are:

TST_BLOB - ftOraBlob;
TST_LONG_ROW - ftOraBlob

but you can't insert both fields simultaneously if the size of data is more than 4000 bytes, because Oracle can't bind more than 4000 bytes with LOB and LONG columns in one command.

[upscene]

You can write binary data to the BLOB and LONG RAW fields like this:

SimpleDataSet1.DataSet.ParamByName('TST_LONG_ROW').LoadFromFile('e:\1.bmp',ftBlob);
SimpleDataSet1.DataSet.ParamByName('TST_BLOB').LoadFromFile('e:\1.bmp',ftBlob);

where text query is:

INSERT INTO TST_TABLE
(TST_BLOB, TST_LONG_ROW)
VALUES
(:TST_BLOB, :TST_LONG_ROW)

and the types of param are:

TST_BLOB - ftOraBlob;
TST_LONG_ROW - ftOraBlob

but you can't insert both fields simultaneously if the size of data is more than 4000 bytes, because Oracle can't bind more than 4000 bytes with LOB and LONG columns in one command.

How can I push more than 4000 bytes then? A LONG RAW should support gigs of data?

[AlexP]

Hello,

You can't insert data in the LOB and LONG fields simultaneously.
But you can insert data in turns by inserting one field during one execution.

[upscene]

Hello,

You can't insert data in the LOB and LONG fields simultaneously.
But you can insert data in turns by inserting one field during one execution.

I'm not saying I'm inserting both at the same time, I'm saying I cannot use the same code for both BLOB and LONG RAW fields

[AlexP]

hello,

Please execute the following code to insert the LOB and LONG data:

procedure TForm1.Button1Click(Sender: TObject);
var
SqlConnection: TSQLConnection;
SimpleDataSet: TSimpleDataSet;
begin
SqlConnection:= TSQLConnection.Create(nil);
SimpleDataSet:= TSimpleDataSet.Create(nil);
SqlConnection.DriverName:= 'DevartOracle';
SqlConnection.VendorLib := 'oci.dll';
SqlConnection.libraryName := 'dbexpoda.dll';
SqlConnection.GetDriverFunc := 'getSQLDriverORA';
SqlConnection.Params.Clear();
SqlConnection.Params.Add('Database=ORA1110');
SqlConnection.Params.Add('User_Name=SCOTT');
SqlConnection.Params.Add('Password=tiger');
SqlConnection.Connected := true;
SimpleDataSet.Connection := SqlConnection;
SimpleDataSet.DataSet.CommandText := 'INSERT INTO ALEXP_BLOB(ID, TST_BLOB) VALUES(:ID, :TST_BLOB)';
SimpleDataSet.DataSet.ParamByName('ID').AsInteger := 1;
SimpleDataSet.DataSet.ParamByName('TST_BLOB').LoadFromFile('e:\1.bmp',ftBlob);
SimpleDataSet.Execute;
SimpleDataSet.DataSet.CommandText := 'UPDATE alexp_blob SET TST_LONG_ROW = :TST_LONG_ROW WHERE ID = :ID';
SimpleDataSet.DataSet.ParamByName('ID').AsInteger := 1;
SimpleDataSet.DataSet.ParamByName('TST_LONG_ROW').LoadFromFile('e:\1.bmp',ftBlob);
SimpleDataSet.Execute;
SimpleDataSet.Close;
end;

Oracle doesn't allow to insert both fields simultaneously if the total size of data is more than 4000 bytes.

[upscene]

hello,

Please execute the following code to insert the LOB and LONG data:

procedure TForm1.Button1Click(Sender: TObject);
var
SqlConnection: TSQLConnection;
SimpleDataSet: TSimpleDataSet;

...

Oracle doesn't allow to insert both fields simultaneously if the total size of data is more than 4000 bytes.

As you can see in the rest of the topic, I'm using ODAC.

[AlexP]

Hello,

This piece of code demonstrates how to use ODAC when inserting data:

OraQuery1.SQL.Clear;
OraQuery1.SQL.Text := 'SELECT * FROM ALEXP_BLOB';
OraQuery1.Open;
OraQuery1.Append;
OraQuery1.FieldByName('id').AsInteger :=1;
(OraQuery1.FieldByName('TST_BLOB') as TBlobField).LoadFromFile('e:\1.bmp');
(OraQuery1.FieldByName('TST_LONG_ROW') as TBlobField).LoadFromFile('e:\1.bmp');
OraQuery1.post;

[upscene]

Hello,

This piece of code demonstrates how to use ODAC when inserting data:

OraQuery1.SQL.Clear;
OraQuery1.SQL.Text := 'SELECT * FROM ALEXP_BLOB';
OraQuery1.Open;
OraQuery1.Append;
OraQuery1.FieldByName('id').AsInteger :=1;
(OraQuery1.FieldByName('TST_BLOB') as TBlobField).LoadFromFile('e:\1.bmp');
(OraQuery1.FieldByName('TST_LONG_ROW') as TBlobField).LoadFromFile('e:\1.bmp');
OraQuery1.post;

As you can see in the rest of the post, I'm using a parameterized INSERT statement (no files involved).

For some reason, a LONG RAW always fails on insert data longer than 2000 or 4000 bytes.

BLOB works fine though.

When I change the code, LONG RAW appears to work fine, but BLOB fails.

I would like to have code that works with both.

[AlexP]

Hello,

You should set the OraQuery1.Options.TemporaryLobUpdate property to true, the TOraQuery parameters for BLOB should be DataType = OraBlob and ParamType = ptInput, and for LONG RAW they should be DataType = Blob and ParamType = ptInput.

The sql statment:

INSERT INTO TEST_BLOB
(ID, TST_LONG_ROW, TST_BLOB)
VALUES
(:ID, :TST_LONG_ROW, :TST_BLOB)

To insert data use the following code:

OraQuery1.ParamByName('ID').AsInteger:= 1;
OraQuery1.ParamByName('TST_LONG_ROW').LoadFromStream(stream,ftBlob);
OraQuery1.ParamByName('TST_BLOB').LoadFromStream(stream, ftOraBlob);
OraQuery1.Execute;

It this case all data (BLOB and LONG RAW) will be inserted correctly.