Devart Forums

Hi!

We try to insert data into a table with two CLOB columns.
If the blob text is shorter than 4000 characters all works fine.

If we try to insert more than 4000 characters into a CLOB field, we
receive different errors depending on the code we use to setup the TParam.

Here are the possible error messages:

ORA-01461 can bind a LONG value only for insert into a LONG column
ORA-12571 // no text!!!
ORA-01460 unimplemented or unreasonable conversion requested
LOB not initialized


We have tried:

p: TParam; // the param from the TSQLDataset
s: string; // a string with 11000 bytes

a)
p.AsBlob:=s;
// error on execsql

b)
p.Datatype:=ftOraCLob;
p.Value:=s;
// error on execsql

c)
p.AsMemo:=s;
// error on execsql

d)
p.Datatype:=ftOraCLOB;
p.SetBlobData(@s[1], Length(s));
// error on execsql

e)
p.Datatype:=ftBlob;
p.SetBlobData(@s[1], Length(s));
// error on execsql

f)
p.Datatype:=ftString;
p.SetBlobData(@s[1], Length(s));
// error on execsql


and so on. None of them works with more than 4000 bytes of data.

What is a working method of setting up this parameter with
more than 400 Bytes ??

Are there any limits on CLOB params ??


Thanks
Silvio

We cannot reproduce your problem with Oracle server 9.2.0.1, Oracle server 10.2.0.1, database charset AL32UTF8, DbxOda 100. We tried to insert 15000 of English characters.
Please specify your version of Oracle server, database charset, client charset (in TSQLConnection parameters)

I've got the same problem and tried also .AsString:= but without an results.

did you find a solution to this problem?

thanx
alex

When you write data using TParam.AsString you change parameter data type to ftString (the maximum length is 4000). You have to use TParam.LoadFromStream(BlobStream, ftOraClob) for CLOB parameters.

I'm having a similar problem, however instead of using a TSQLQuery
object for doing parametered UPDATE/INSERT statements I'm using
a TSQLConnection object as described below.

I'm having trouble creating a BlobStream from the TSQLDataSet that I'm getting from TSQLConnection->DataSets[0].

Do you happen to have any other sample code for creating Blob streams?

Thanks in advance...

jim


sqlString = "INSERT INTO EMP (STR,BIG_CLOB) VALUES (?,?)";

// set up a transaction
// sqlConnection is a TSQLConnection object
if (!sqlConnection->InTransaction) {
TD.TransactionID = 1;
TD.IsolationLevel = xilREADCOMMITTED;
sqlConnection->StartTransaction(TD);
}

// bind some parameters
// paramsList = new TParams;
TParam *p = paramsList->CreateParam(ftString,":0",ptInput);
p->AsString = "hello"

p = paramsList->CreateParam(ftOraClob,":1",ptInput);
p->AsString = "really big clob string..."

// then execute the statment.
cnt = sqlConnection->Execute(sqlString,paramsList,NULL);

// now commit
sqlConnection->Commit(TD);

// and we're done.
sqlConnection->Connected = false;

The following line converts TParam.DataType to ftString. The maximum value length is 4000.
p->AsString = "really big clob string..."

TParam.SetBlobData converts TParam.DataType to ftBlob. See Db.pas for more details. This corresponds to Oracle LONG RAW type.

Please use TParam.LoadFromStream(Stream, ftOraClob) for loading data to CLOB parameter.

We also get this error: tried above mentioned methods but error occurs even on this dirty workaround: The only thing we didn't test was the returning clause as it is almost impossible to change all the app code for this..

Any suggestions to solve this issue?

Anyone could get a solution for any of these problems ?
I'm having the same problems. With Oracle Net 3.0.4 and Oracle 10g