bug with CLOB in Oracle 12.2

[jdorlon]

Hello,

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;

then put this in a TSmartQuery with a TDBMemo attached to the CLOB column, once I scroll past the 100th row, there is no more data populated in the CLOB. In this demo, the error is not terrible, as the only symptom is that the data does not appear. In our application, which uses Devex grids, it is more serious as a "Stream Read Error" exception.

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.

Unit1.dfm:

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

Project1.dpr:

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.

Thank you.

[MaximG]

Thank you for the information. We investigated the issue according to your description. First, we managed to reproduce the issue according to your description. However, we are not sure that it is related to our components behavior, because the issue is only reproduced when using OCI Mode (the correct behavior is observed in the Direct Mode). In addition, we conducted the following test. We created a table of the following structure:

Code: Select all

CREATE TABLE TABLE_Y ("LEVEL" NVARCHAR2 (50), LEVEL_AS_CLOB NVARCHAR2 (50))

and fill out its data with the query:

Code: Select all

insert INTO TABLE_Y select the level, to_clob (level) LEVEL_AS_CLOB from the dual level connection <= 200

After that, we successfully executed the query

Code: Select all

 select * from TABLE_Y

using your application.

After the tests completion, we restarted the Oracle server, after that the issue stopped reproducing even when using the application you sent. Try performing similar actions in your environment and inform us about the results.

[jdorlon]

I wouldn't expect the problem to happen with your TABLE_Y because you defined the table with NVARCHAR2(50), not CLOB. The problem is related to CLOBs. But, I do see that the problem does not happen if TABLE_Y is created with a CLOB.

This is the query that my customer found the problem with:

Code: Select all

select sql_fulltext from v$SQL;

What I have noticed, is that the problem happens with ANY query that uses TO_CLOB. And the problem does not happen in SQL*Plus. So it does not seem to be an OCI problem.

Direct Mode has too many limitations, so it is not an acceptable solution for me.

[MaximG]

Sorry for the inaccuracy. The correct behavior is observed when using the following table

Code: Select all

CREATE TABLE TABLE_Y ("LEVEL" NVARCHAR2 (50), LEVEL_AS_CLOB CLOB)

We will carefully investigate the work of ODAC when using queries with the TO_CLOB statement and let you know the results in the near future.

[jdorlon]

Hello,

Can something be done to resolve this? It has been almost 6 months since I reported it.

Thank you,

-John

[MaximG]

Thank you for the patience. We carefully investigated the described issue and found the necessary solution. Changes in our product regarding this issue will be included in the next build. Currently, as a workaround, we can send you a night build with the required changes. For this, please specify your license number and the exact version of Delphi you are using via the e-support form ( https://www.devart.com/company/contactform.html )

[jdorlon]

Thank you, Maxim.

[jdorlon]

That seems to fix it, thank you very much.

[MaximG]

We are glad that the problem has been resolved. In the near future we plan to release a new build of our products, which will include the fixes you are interested in. Follow the news at our website