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
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
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.