If I have a query that returns more than 100 rows of CLOB data, such as
Code: Select all
select level,
to_clob(level) LEVEL_AS_CLOB
from dual
connect by level <= 200;
ODAC version 10.1.4
Delphi 10.1 Berlin
WIndows 7
Oracle client 12.1 or 12.2
Oracle server 12.2 (the problem does NOT happen with prior oracle server versions)
Server and client are different machines. Problem does not happen if they are on the same machine.
Does not matter if app is 32 or 64 bit.
files to build my demo project are below.
Unit.pas:
Code: Select all
unit Unit1;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.ExtCtrls, Vcl.StdCtrls, Vcl.Grids, Vcl.DBGrids, Data.DB, DBAccess, Ora,
MemDS, OraSmart, OraCall, Vcl.ComCtrls, OraClasses, Vcl.DBCtrls;
type
TForm1 = class(TForm)
sess: TOraSession;
qry: TSmartQuery;
ds: TOraDataSource;
Memo1: TMemo;
Panel2: TPanel;
btnRunQuery: TButton;
edUser: TLabeledEdit;
edPassword: TLabeledEdit;
edDatabase: TLabeledEdit;
btnConnect: TButton;
btnDisconnect: TButton;
StatusBar1: TStatusBar;
Panel1: TPanel;
DBMemo1: TDBMemo;
DBMemo2: TDBMemo;
Label2: TLabel;
Label3: TLabel;
Label1: TLabel;
DBGrid1: TDBGrid;
procedure btnConnectClick(Sender: TObject);
procedure btnDisconnectClick(Sender: TObject);
procedure btnRunQueryClick(Sender: TObject);
procedure qryAfterScroll(DataSet: TDataSet);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.btnConnectClick(Sender: TObject);
begin
if (edUser.Text = '') or
(edPassword.Text = '') or
(edDatabase.Text = '') then
MessageDlg('Enter username, password, and database.', mtInformation, [mbOK], 0)
else
begin
if true then // the problem happens on 12.2 databases. Does not matter if UseUnicode is true or false.
begin
OraCall.OCIUnicode := True;
Sess.Options.UseUnicode := True;
Sess.Options.UnicodeEnvironment := True;
end
else
begin
OraCall.OCIUnicode := False;
Sess.Options.UseUnicode := False;
Sess.Options.UnicodeEnvironment := False;
end;
Sess.Username := edUser.Text;
Sess.Password := edPassword.Text;
Sess.Server := edDatabase.Text;
Sess.Connected := True;
btnConnect.Enabled := False;
btnDisConnect.Enabled := True;
btnRunQuery.Enabled := True;
StatusBar1.Panels[0].Text := 'Connected';
end;
end;
procedure TForm1.btnDisconnectClick(Sender: TObject);
begin
Sess.Connected := False;
btnConnect.Enabled := True;
btnDisConnect.Enabled := False;
btnRunQuery.Enabled := False;
StatusBar1.Panels[0].Text := 'Disconnected';
end;
procedure TForm1.btnRunQueryClick(Sender: TObject);
var
sql: String;
dtStart,
dtStop: Real;
begin
sql := Trim(memo1.Lines.Text);
if SQL = '' then
MessageDlg('Enter a query first', mtInformation, [mbOK], 0)
else
begin
if qry.Active then qry.Close;
if SQL[Length(SQL)] = ';' then
SQL := Copy(SQL, 1, Length(SQL) - 1);
qry.SQL.Text := sql;
dtStart := now;
screen.cursor := crHourglass;
try
qry.Execute;
dbMemo1.DataField := 'LEVEL';
dbMemo2.DataField := 'LEVEL_AS_CLOB';
finally
screen.cursor := crDefault;
dtStop := now;
Statusbar1.Panels[1].Text := 'Exec time: ' + FloatToStr(Round(8640000 * (dtStop - dtStart)) / 100) + ' seconds';
end;
end;
end;
procedure TForm1.qryAfterScroll(DataSet: TDataSet);
begin
Statusbar1.Panels[2].Text := 'Row ' + IntToStr(qry.RecNo) + ' of ' + IntToStr(qry.RecordCount);
end;
end.
Code: Select all
object Form1: TForm1
Left = 326
Top = 94
Caption = 'Form1'
ClientHeight = 314
ClientWidth = 639
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Memo1: TMemo
Left = 0
Top = 99
Width = 639
Height = 86
Align = alClient
Lines.Strings = (
'select level, '
' to_clob(level) LEVEL_AS_CLOB'
'from dual '
'connect by level <= 200;')
TabOrder = 1
end
object Panel2: TPanel
Left = 0
Top = 0
Width = 639
Height = 99
Align = alTop
TabOrder = 0
object btnRunQuery: TButton
Left = 186
Top = 61
Width = 75
Height = 25
Caption = 'Run Query'
Enabled = False
TabOrder = 0
OnClick = btnRunQueryClick
end
object edUser: TLabeledEdit
Left = 7
Top = 20
Width = 121
Height = 21
EditLabel.Width = 22
EditLabel.Height = 13
EditLabel.Caption = 'User'
TabOrder = 1
Text = 'jdorlon'
end
object edPassword: TLabeledEdit
Left = 146
Top = 20
Width = 121
Height = 21
EditLabel.Width = 46
EditLabel.Height = 13
EditLabel.Caption = 'Password'
PasswordChar = '*'
TabOrder = 2
Text = 'jdorlon'
end
object edDatabase: TLabeledEdit
Left = 284
Top = 20
Width = 121
Height = 21
EditLabel.Width = 46
EditLabel.Height = 13
EditLabel.Caption = 'Database'
TabOrder = 3
Text = 'azure_12cr2_plug'
end
object btnConnect: TButton
Left = 7
Top = 61
Width = 75
Height = 25
Caption = 'Connect'
TabOrder = 4
OnClick = btnConnectClick
end
object btnDisconnect: TButton
Left = 99
Top = 61
Width = 75
Height = 25
Caption = 'Disconnect'
Enabled = False
TabOrder = 5
OnClick = btnDisconnectClick
end
end
object StatusBar1: TStatusBar
Left = 0
Top = 295
Width = 639
Height = 19
Panels = <
item
Text = 'Disconnected'
Width = 150
end
item
Width = 150
end
item
Width = 200
end>
end
object Panel1: TPanel
Left = 0
Top = 185
Width = 639
Height = 110
Align = alBottom
BevelOuter = bvNone
TabOrder = 3
object Label2: TLabel
Left = 259
Top = 7
Width = 25
Height = 13
Caption = 'Level'
end
object Label3: TLabel
Left = 371
Top = 9
Width = 68
Height = 13
Caption = 'Level as CLOB'
end
object Label1: TLabel
Left = 490
Top = 25
Width = 128
Height = 65
Caption =
'Problem: On Oracle 12.2, the CLOB data disappears after row # ' +
'100. Scroll down in grid to observe this.'
WordWrap = True
end
object DBMemo1: TDBMemo
Left = 259
Top = 25
Width = 105
Height = 68
DataSource = ds
TabOrder = 0
end
object DBMemo2: TDBMemo
Left = 370
Top = 26
Width = 105
Height = 68
DataSource = ds
TabOrder = 1
end
object DBGrid1: TDBGrid
Left = 0
Top = 0
Width = 241
Height = 110
Align = alLeft
DataSource = ds
TabOrder = 2
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
end
object sess: TOraSession
LoginPrompt = False
Left = 317
Top = 125
end
object qry: TSmartQuery
Session = sess
FetchRows = 200
Options.RawAsString = True
Options.SetFieldsReadOnly = False
Options.ExtendedFieldsInfo = False
Constraints = <>
AfterScroll = qryAfterScroll
Left = 282
Top = 127
end
object ds: TOraDataSource
DataSet = qry
Left = 248
Top = 129
end
end
Code: Select all
program Project1;
uses
Vcl.Forms,
Unit1 in 'Unit1.pas' {Form1};
{$R *.res}
begin
Application.Initialize;
Application.MainFormOnTaskbar := True;
Application.CreateForm(TForm1, Form1);
Application.Run;
end.