Bug: Inserting via array DML, large VARCHAR2 columns, some data not inserted.

jdorlon · Post by **jdorlon** » Mon 14 Jan 2019 14:50

Hello,

When I attempt to use Array DML to insert data into a table with VARCHAR2 columns of size > 4000 (possible only when oracle parameter max_string_size=extended), some of the inserted values are null when they should have values.

A sample project to reproduce it is below.

Versions:
Oracle 12.2 with max_string_size=extended
ODAC 10.3.8
Rad Studio 10.1 Berlin, compiling in 32 bit
Windows 7

Project1.dpr

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 259
  ClientWidth = 567
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 166
    Top = 129
    Width = 104
    Height = 13
    Caption = 'To reproduce error:   '
  end
  object Label2: TLabel
    Left = 180
    Top = 148
    Width = 359
    Height = 13
    Caption = 
      '1) Connect to a 12cR2 or newer database with max_string_size=ext' +
      'ended'
  end
  object Label3: TLabel
    Left = 180
    Top = 167
    Width = 299
    Height = 13
    Caption = '2) Click drop_objects, create_objects, and copy_data, above.'
  end
  object Label4: TLabel
    Left = 180
    Top = 186
    Width = 305
    Height = 13
    Caption = '3) data should now be identical in tables VC32K_1 and VC32K_2'
  end
  object Label5: TLabel
    Left = 192
    Top = 205
    Width = 260
    Height = 13
    Caption = 'but some of values in the VARCHAR(32000) column in '
  end
  object Label6: TLabel
    Left = 192
    Top = 224
    Width = 85
    Height = 13
    Caption = 'VC32K_2 are null.'
  end
  object GroupBox1: TGroupBox
    Left = 8
    Top = 8
    Width = 142
    Height = 243
    Caption = 'Source'
    TabOrder = 0
    object lbSource: TLabel
      Left = 6
      Top = 198
      Width = 72
      Height = 13
      Caption = 'Not Connected'
    end
    object eSourceUser: TLabeledEdit
      Left = 6
      Top = 36
      Width = 123
      Height = 21
      EditLabel.Width = 22
      EditLabel.Height = 13
      EditLabel.Caption = 'User'
      TabOrder = 0
      Text = 'jdorlon'
    end
    object eSourcePassword: TLabeledEdit
      Left = 6
      Top = 78
      Width = 121
      Height = 21
      EditLabel.Width = 46
      EditLabel.Height = 13
      EditLabel.Caption = 'Password'
      TabOrder = 1
      Text = 'jdorlon'
    end
    object eSourceDB: TLabeledEdit
      Left = 6
      Top = 121
      Width = 121
      Height = 21
      EditLabel.Width = 46
      EditLabel.Height = 13
      EditLabel.Caption = 'Database'
      TabOrder = 2
      Text = 'ORCL12CR2_Normal'
    end
    object Button1: TButton
      Left = 6
      Top = 158
      Width = 75
      Height = 25
      Caption = 'Connect'
      TabOrder = 3
      OnClick = Button1Click
    end
  end
  object btnCreate: TButton
    Left = 156
    Top = 42
    Width = 137
    Height = 25
    Caption = 'Create Objects'
    Enabled = False
    TabOrder = 1
    OnClick = btnCreateClick
  end
  object btnCopy: TButton
    Left = 156
    Top = 73
    Width = 137
    Height = 25
    Caption = 'Copy Data'
    Enabled = False
    TabOrder = 2
    OnClick = btnCopyClick
  end
  object btnDrop: TButton
    Left = 156
    Top = 11
    Width = 137
    Height = 25
    Caption = 'Drop Objects'
    Enabled = False
    TabOrder = 3
    OnClick = btnDropClick
  end
  object srcSession: TOraSession
    Options.UseUnicode = True
    Options.EnableNumbers = True
    Options.UnicodeEnvironment = True
    HomeName = 'OraClient12Home2_32bit'
    Left = 360
    Top = 16
  end
  object scrCreateObjects: TOraScript
    SQL.Strings = (
      'CREATE TABLE VC32K_1'
      '('
      '  CATEGORY     VARCHAR2(30 CHAR),'
      '  DESCRIPTION  VARCHAR2(200 CHAR),'
      '  SCRIPT       VARCHAR2(32000 CHAR)'
      ');'
      ''
      'CREATE TABLE VC32K_2'
      '('
      '  CATEGORY     VARCHAR2(30 CHAR),'
      '  DESCRIPTION  VARCHAR2(200 CHAR),'
      '  SCRIPT       VARCHAR2(32000 CHAR)'
      ');'
      ''
      'insert into VC32K_1(category, description, script)'
      'select owner, table_name, '
      
        '       owner || '#39' '#39' || table_name || '#39' '#39' || CLUSTER_NAME || '#39' '#39' ' +
        '|| IOT_NAME || '#39' '#39' || CLUSTER_OWNER || '#39' '#39' || TABLESPACE_NAME ||' +
        ' '#39' '#39' || DEGREE || '#39' '#39' || INSTANCES'
      'from dba_tables'
      'where rownum <= 100;'
      ''
      'update VC32K_1'
      
        'set script = to_clob(script || script || script || script || scr' +
        'ipt || script);'
      'update VC32K_1'
      
        'set script = to_clob(script || script || script || script || scr' +
        'ipt || script);'
      'update VC32K_1'
      
        'set script = to_clob(script || script || script || script || scr' +
        'ipt || script);'
      ''
      'commit;'
      '')
    Session = srcSession
    Left = 313
    Top = 43
  end
  object scrDropObjects: TOraScript
    SQL.Strings = (
      'drop table VC32K_1 purge;'
      'drop table VC32K_2 purge;')
    OnError = scrDropObjectsError
    Session = srcSession
    Left = 312
    Top = 9
  end
  object srcQry: TOraQuery
    Session = srcSession
    Left = 395
    Top = 17
  end
  object TargetQry: TOraSQL
    Left = 427
    Top = 13
  end
  object SmartQuery1: TSmartQuery
    Session = srcSession
    Left = 460
    Top = 15
  end
end

Unit1.dfm

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 259
  ClientWidth = 567
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 166
    Top = 129
    Width = 104
    Height = 13
    Caption = 'To reproduce error:   '
  end
  object Label2: TLabel
    Left = 180
    Top = 148
    Width = 359
    Height = 13
    Caption = 
      '1) Connect to a 12cR2 or newer database with max_string_size=ext' +
      'ended'
  end
  object Label3: TLabel
    Left = 180
    Top = 167
    Width = 299
    Height = 13
    Caption = '2) Click drop_objects, create_objects, and copy_data, above.'
  end
  object Label4: TLabel
    Left = 180
    Top = 186
    Width = 305
    Height = 13
    Caption = '3) data should now be identical in tables VC32K_1 and VC32K_2'
  end
  object Label5: TLabel
    Left = 192
    Top = 205
    Width = 260
    Height = 13
    Caption = 'but some of values in the VARCHAR(32000) column in '
  end
  object Label6: TLabel
    Left = 192
    Top = 224
    Width = 85
    Height = 13
    Caption = 'VC32K_2 are null.'
  end
  object GroupBox1: TGroupBox
    Left = 8
    Top = 8
    Width = 142
    Height = 243
    Caption = 'Source'
    TabOrder = 0
    object lbSource: TLabel
      Left = 6
      Top = 198
      Width = 72
      Height = 13
      Caption = 'Not Connected'
    end
    object eSourceUser: TLabeledEdit
      Left = 6
      Top = 36
      Width = 123
      Height = 21
      EditLabel.Width = 22
      EditLabel.Height = 13
      EditLabel.Caption = 'User'
      TabOrder = 0
      Text = 'jdorlon'
    end
    object eSourcePassword: TLabeledEdit
      Left = 6
      Top = 78
      Width = 121
      Height = 21
      EditLabel.Width = 46
      EditLabel.Height = 13
      EditLabel.Caption = 'Password'
      TabOrder = 1
      Text = 'jdorlon'
    end
    object eSourceDB: TLabeledEdit
      Left = 6
      Top = 121
      Width = 121
      Height = 21
      EditLabel.Width = 46
      EditLabel.Height = 13
      EditLabel.Caption = 'Database'
      TabOrder = 2
      Text = 'ORCL12CR2_Normal'
    end
    object Button1: TButton
      Left = 6
      Top = 158
      Width = 75
      Height = 25
      Caption = 'Connect'
      TabOrder = 3
      OnClick = Button1Click
    end
  end
  object btnCreate: TButton
    Left = 156
    Top = 42
    Width = 137
    Height = 25
    Caption = 'Create Objects'
    Enabled = False
    TabOrder = 1
    OnClick = btnCreateClick
  end
  object btnCopy: TButton
    Left = 156
    Top = 73
    Width = 137
    Height = 25
    Caption = 'Copy Data'
    Enabled = False
    TabOrder = 2
    OnClick = btnCopyClick
  end
  object btnDrop: TButton
    Left = 156
    Top = 11
    Width = 137
    Height = 25
    Caption = 'Drop Objects'
    Enabled = False
    TabOrder = 3
    OnClick = btnDropClick
  end
  object srcSession: TOraSession
    Options.UseUnicode = True
    Options.EnableNumbers = True
    Options.UnicodeEnvironment = True
    HomeName = 'OraClient12Home2_32bit'
    Left = 360
    Top = 16
  end
  object scrCreateObjects: TOraScript
    SQL.Strings = (
      'CREATE TABLE VC32K_1'
      '('
      '  CATEGORY     VARCHAR2(30 CHAR),'
      '  DESCRIPTION  VARCHAR2(200 CHAR),'
      '  SCRIPT       VARCHAR2(32000 CHAR)'
      ');'
      ''
      'CREATE TABLE VC32K_2'
      '('
      '  CATEGORY     VARCHAR2(30 CHAR),'
      '  DESCRIPTION  VARCHAR2(200 CHAR),'
      '  SCRIPT       VARCHAR2(32000 CHAR)'
      ');'
      ''
      'insert into VC32K_1(category, description, script)'
      'select owner, table_name, '
      
        '       owner || '#39' '#39' || table_name || '#39' '#39' || CLUSTER_NAME || '#39' '#39' ' +
        '|| IOT_NAME || '#39' '#39' || CLUSTER_OWNER || '#39' '#39' || TABLESPACE_NAME ||' +
        ' '#39' '#39' || DEGREE || '#39' '#39' || INSTANCES'
      'from dba_tables'
      'where rownum <= 100;'
      ''
      'update VC32K_1'
      
        'set script = to_clob(script || script || script || script || scr' +
        'ipt || script);'
      'update VC32K_1'
      
        'set script = to_clob(script || script || script || script || scr' +
        'ipt || script);'
      'update VC32K_1'
      
        'set script = to_clob(script || script || script || script || scr' +
        'ipt || script);'
      ''
      'commit;'
      '')
    Session = srcSession
    Left = 313
    Top = 43
  end
  object scrDropObjects: TOraScript
    SQL.Strings = (
      'drop table VC32K_1 purge;'
      'drop table VC32K_2 purge;')
    OnError = scrDropObjectsError
    Session = srcSession
    Left = 312
    Top = 9
  end
  object srcQry: TOraQuery
    Session = srcSession
    Left = 395
    Top = 17
  end
  object TargetQry: TOraSQL
    Left = 427
    Top = 13
  end
  object SmartQuery1: TSmartQuery
    Session = srcSession
    Left = 460
    Top = 15
  end
end

Unit1.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, Data.DB, MemDS, DBAccess, Ora, OraSmart, OraCall, Vcl.StdCtrls, Vcl.ExtCtrls,
  DAScript, OraScript;

type
  TForm1 = class(TForm)
    GroupBox1: TGroupBox;
    eSourceUser: TLabeledEdit;
    eSourcePassword: TLabeledEdit;
    eSourceDB: TLabeledEdit;
    Button1: TButton;
    btnCreate: TButton;
    btnCopy: TButton;
    srcSession: TOraSession;
    scrCreateObjects: TOraScript;
    lbSource: TLabel;
    btnDrop: TButton;
    scrDropObjects: TOraScript;
    srcQry: TOraQuery;
    TargetQry: TOraSQL;
    SmartQuery1: TSmartQuery;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure btnCreateClick(Sender: TObject);
    procedure btnCopyClick(Sender: TObject);
    procedure btnDropClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure scrDropObjectsError(Sender: TObject; E: Exception; SQL: string; var Action: TErrorAction);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  srcSession.Username := eSourceUser.Text;
  srcSession.Password := eSourcePassword.Text;
  srcSession.Server := eSourceDB.Text;
  srcSession.Connect;
  lbSource.Caption := 'Connected';
  btnDrop.Enabled := (lbSource.Caption = 'Connected') ;
  btnCreate.Enabled := btnDrop.Enabled;
  btnCopy.Enabled := btnDrop.Enabled;
end;

procedure TForm1.btnCreateClick(Sender: TObject);
begin
  screen.cursor := crHourglass;
  try
    scrCreateObjects.Session := srcSession;
    scrCreateObjects.Execute;
  finally
    screen.cursor := crDefault;
  end;
end;

procedure TForm1.btnCopyClick(Sender: TObject);
var
  i: Integer;
Const
  ArraySize = 10; // note: If array size = 1, all data is copied correctly.
begin
  screen.cursor := crHourglass;
  try
    SrcQry.ObjectView := true;
    SrcQry.Options.ExtendedFieldsInfo := false;
    SrcQry.Options.SetFieldsReadOnly := false;
    SrcQry.OptionsDS.RawAsString := True;

    SrcQry.Session := srcSession;
    srcQry.sql.Text := 'Select * from VC32K_1';
    srcQry.Execute;
    TargetQry.Session := srcSession;

    TargetQry.ArrayLength := ArraySize;

    TargetQry.sql.Text := 'insert into VC32K_2(CATEGORY, DESCRIPTION, SCRIPT) values (:v0, :v1, :v2)';

    TargetQry.ParamByName('v0').DataType := srcQry.FieldByName('CATEGORY').DataType;
    TargetQry.ParamByName('v1').DataType := srcQry.FieldByName('DESCRIPTION').DataType;
    TargetQry.ParamByName('v2').DataType := srcQry.FieldByName('SCRIPT').DataType;

    TargetQry.ParamByName('v0').ParamType := ptInput;
    TargetQry.ParamByName('v1').ParamType := ptInput;
    TargetQry.ParamByName('v2').ParamType := ptInput;

    i := 1;
    while not SrcQry.eof do
    begin
      TargetQry.ParamByName('v0').ItemAsString[i] := SrcQry.FieldByName('CATEGORY').asString;
      TargetQry.ParamByName('v1').ItemAsString[i] := SrcQry.FieldByName('DESCRIPTION').asString;
      TargetQry.ParamByName('v2').ItemAsString[i] := SrcQry.FieldByName('SCRIPT').asString;
      if i = ArraySize then
      begin
        TargetQry.Execute(ArraySize);
        i := 1;
      end
      else
        i := i + 1;
      srcQry.Next;
    end;

    if i > 1 then
      TargetQry.Execute(i-1);

    TargetQry.Session.Commit;

    // verify
    SmartQuery1.SQL.Clear;
    SmartQuery1.SQL.Add('select category, description, script');
    SmartQuery1.SQL.Add('from vc32k_1');
    SmartQuery1.SQL.Add('minus');
    SmartQuery1.SQL.Add('select category, description, script');
    SmartQuery1.SQL.Add('from vc32k_2');
    SmartQuery1.Execute;
    if not smartquery1.eof then
      ShowMessage('Rows do not match.');
    SmartQuery1.Close;


  finally
    screen.cursor := crDefault;
  end;
end;

procedure TForm1.btnDropClick(Sender: TObject);
begin
  screen.cursor := crHourglass;
  try
    scrDropObjects.Session := srcSession;
    scrDropObjects.Execute;
  finally
    screen.cursor := crDefault;
  end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  OraCall.OCIUnicode := True;
end;

procedure TForm1.scrDropObjectsError(Sender: TObject; E: Exception; SQL: string; var Action: TErrorAction);
begin
  Action :=  eaContinue;
end;

end.

MaximG · Post by **MaximG** » Fri 18 Jan 2019 15:40

Currently, to work with parameters for DML operation which values exceed 4000, you need to explicitly specify the size:

Code: Select all

 TargetQry.ParamByName('v2').Size := 32000;

We will consider the possibility of implementing the behavior of our components without this explicit specification.

jdorlon · Post by **jdorlon** » Fri 18 Jan 2019 15:42

That's good enough for me, thank you.

MaximG · Post by **MaximG** » Fri 18 Jan 2019 17:20

Please don't hesitate to contact us with questions concerning our product usage.

Devart Forums

Bug: Inserting via array DML, large VARCHAR2 columns, some data not inserted.

Bug: Inserting via array DML, large VARCHAR2 columns, some data not inserted.

Re: Bug: Inserting via array DML, large VARCHAR2 columns, some data not inserted.

Re: Bug: Inserting via array DML, large VARCHAR2 columns, some data not inserted.

Re: Bug: Inserting via array DML, large VARCHAR2 columns, some data not inserted.