Problem with Oracle NCLOB fields

[skoro]

Hi!
I encounter problem to fetch binary data from Oracle fields with NCLOB datatype. For example, I have following table:

Code: Select all

CREATE TABLE testlob (
id INTEGER,
name VARCHAR(50),
bindata NCLOB
)

I need to fetch id and bindata fields and I use TZQuery:

Code: Select all

  qry := TZQuery.Create(nil);
  qry.Connection := ZConnection1;
  with qry do begin
    qry.SQL.Text := 'select id, bindata from testlob';
    qry.Open;
  end;

In such code snippet qry.Open raises exception ORA-24806: Lob form mismatch. I note, that if field bindata is empty when all works ok, but in case of bindata full exception is raises. If this SQL query execute in Oracle SQL Console, data fetch fine.
I use Delphi 2007, Zeos 6.6.5-stable, Oracle XE 10.2.0.1.0.
Any suggestions?

[mdaems]

Hi,

Does it work for normal clob data? This may depend on your session Character Set settings according to the oracle documentation of the ORA-message:

ORA-24806:
LOB form mismatch
Cause: When reading from or writing into LOBs, the character set form of the user buffer should be same as that of the LOB.
Action: Make sure that the buffer you are using to read or write has the same form as that of the LOB.

I'm afraid changing the oracle OCI calls used in zeoslib to fix this is way above my oracle competences.
If changing some session/database/table charset settings doesn't work you'll have to dive into the zeoslib dbc/plain layers code and get a firm read on the oracle OCI documentation.

Mark

[skoro]

Hi!
Well, I alter NCLOB datatype to CLOB and now no more exception ORA-24806 raised, but following problem occuried: when I try to walk records with next code

Code: Select all

with qry do begin
    SQL.Text := 'select id, bindata from testlob';
    Open;
    if RecordCount > 0 then begin
      for n := 0 to RecordCount - 1 do begin
        s := FieldByName('id').AsString;
        ti := FieldByName('bindata').AsString;
        Next;
      end;
    end;
  end; 

Variable ti always contain value of last record. For example, three records exists: id=1, bindata=0x0001, id=2, bindata=0x0002, id=3, bindata=0x0003. In above code, ti all time is 0x0003, on first record, on secord record, etc...
Any suggestion?

skoro

[mdaems]

Seems like you've found the same problem as in this post : http://zeos.firmos.at/viewtopic.php?t=2462
There's a workaround mentioned which may help you in some cases, however.

Unfortunately my answer still remains the same: I'm not really able to help you myself.
Even if I had some time, I'd still need a 'ready to run' example program covering the problems you encounter. Since a few weeks I have a working XE-installation and I use it with the zeoslib test suite. So if you could make a minimal project including a database build script (including data) and load it into the bug tracker, I can try to see what's wrong .
I guess some debugging of the Oracle dbc layer from your side might prove more efficient.

Mark

[skoro]

Indeed, if I put qry.Refresh after qry.RecordCount I can fetch all CLOB field fine. On free time I try to investigate why this happen.
Thanks to all for advices!