Re: Problem with Oracle 9

From: <steve.morrell_at_white-carbon.com>
Date: 23 Sep 2005 03:03:27 -0700
Message-ID: <1127469807.813868.204600@f14g2000cwb.googlegroups.com>

Regarding using 9.2.0.1 (my typo on the 9.2.0.0), well those crazy clients who pay the bills will have their fun...

The problem appears to be that there is a limit of 64k on a clob read from our delphi software, which only appears in Oracle 9, not Oracle 8.

Try this

Create the user create user testora8 identified by test default tablespace USERS temporary tablespace TEMP profile DEFAULT;
Grant/Revoke role privileges grant connect to testora8;
Grant/Revoke system privileges grant create any table to testora8; grant unlimited tablespace to testora8;
Create table create table TESTORA8.TEST ( ID varchar2(10) DATA CLOB ) tablespace USERS pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 128K next 128K minextents 1 maxextents 4096 pctincrease 0 );

Then this Delphi code demonstrates that if you point it at an oracle 8 database you get all the data back, but if you point it at an Oracle 9 database, it is "truncated" on the read to 64k. I can cheerfully supply an exe if this is not sufficient.

procedure Tform1.btnInsertDataClick(Sender: TObject); var
  lSQL : TStrings;
  lConnection : TADOConnection;
  i : integer;
  lDataIn, lDataChunk, lOut : string;
begin

edtOugoingsize.Text := '';
edtIngoing.Text := '';

screen.Cursor := crHourGlass;

  lDataIn := '';
  lDataChunk := '0123456789';
  lSQL := TStringList.Create();
  lConnection := TADOConnection.Create( self );   lConnection.ConnectionString :=
'Provider=OraOLEDB.Oracle.1;Password=manager;Data Source=' + edtDataSource.Text + ';User Id=system;';

for i := 0 to 10000 do
begin

lDataIn := lDataIn + lDataChunk;
end;

  lInsertData2.Parameters.ParamByName('UNIQUEID').Value     :=  'ID';
  lInsertData2.Parameters.ParamByName('BLOB').Value     :=  lDataIn;

lInsertData2.Connection := lConnection;

lConnection.Open();
lInsertData2.ExecSQL();

edtIngoing.Text := IntToStr( Length( lDataIn ) );

  RetrieveData.Connection := lConnection;
  RetrieveData.Parameters.ParamByName('ID').Value     :=  'ID';
  RetrieveData.ExecSQL;
  RetrieveData.Open;
  RetrieveData.First;

lOut := RetrieveData.FieldByName( 'DATA' ).Value; edtOugoingsize.Text := IntToStr( Length( lOut ) );

lConnection.Close();

Screen.Cursor := crDefault

end; Received on Fri Sep 23 2005 - 05:03:27 CDT