Devart Forums

Hi,

In Delphi2010, I have created a test project in which i can read the read russian characters inserted into the database with toad.

Displaying isn't a problem.
Updating without parameters does not work.

This is the test table:
CREATE TABLE DBADM.TEST
(
ID NUMBER(22,10) NOT NULL,
DESCR VARCHAR2(100 CHAR),
NAME VARCHAR2(30 CHAR)
)
TABLESPACE DBATBL
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX DBADM.TEST_PK ON DBADM.TEST
(ID)
LOGGING
TABLESPACE DBATBL
NOPARALLEL;


ALTER TABLE DBADM.TEST ADD (
CONSTRAINT TEST_PK
PRIMARY KEY
(ID)
USING INDEX DBADM.TEST_PK);
Insert into DBADM.TEST
(ID,
DESCR, NAME)
Values
(1, 'уверены, что хо', 'a');
Insert into DBADM.TEST
(ID,
DESCR, NAME)
Values
(2, 'уверены, что хо', 'b');
Insert into DBADM.TEST
(ID,
DESCR, NAME)
Values
(3, 'закрыть , что хо', 'c');
Insert into DBADM.TEST
(ID,
DESCR, NAME)
Values
(12, NULL, '????');
Insert into DBADM.TEST
(ID,
DESCR, NAME)
Values
(20, '??????', '??????');
Insert into DBADM.TEST
(ID,
DESCR, NAME)
Values
(10, 'Вы грязная шлюха', 'd');
Insert into DBADM.TEST
(ID,
DESCR, NAME)
Values
(11, '??? ??', 'e');
COMMIT;

This is the unit1.pas of my code:
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ExtCtrls, DB, MemDS, DBAccess, Ora, DBCtrls, Grids, DBGrids,
WideStrings, DBXOracle, FMTBcd, SqlExpr;

type
TForm1 = class(TForm)
Memo1: TMemo;
Memo2: TMemo;
s1: TOraSession;
Q2: TOraQuery;
Panel1: TPanel;
Button1: TButton;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
sess: TSQLConnection;
Qa: TSQLQuery;
Button2: TButton;
Q1: TOraQuery;
Button3: TButton;
Q3: TOraQuery;
s2: TOraSession;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}


procedure TForm1.Button1Click(Sender: TObject);
var qry: TSQLQuery;
begin
qry := Qa;
sess.Params.Values['USER_NAME'] := 'dbadm';
sess.Params.Values['PASSWORD'] := 'ora920';
sess.Params.Values['DATABASE'] := 'M10R_UPG';
sess.ConnectionName := 'test';
sess.Connected := True;
qry.Close;

qry.SQL.Assign(Memo1.Lines);
Memo2.Lines.Add('Run script: ');
Memo2.Lines.AddStrings(Memo1.Lines);
Memo2.Lines.Add('------------------------------');
Memo2.Lines.Add('');
if SameText(Copy(Trim(qry.SQL.Text), 1, 6), 'SELECT') then
begin
try
Memo2.Lines.Add('Open query: ' + DateTimeToStr(Now));
qry.Open;
Memo2.Lines.Add('query opened: ' + DateTimeToStr(Now));
except
on E: Exception do
begin
Memo2.Lines.Add(E.Message);
end;
end;
end
else
begin
try
Memo2.Lines.Add('Execute query: ' + DateTimeToStr(Now));
qry.ExecSQL;
Memo2.Lines.Add('Query executed: ' + DateTimeToStr(Now));
except
on E: Exception do
begin
Memo2.Lines.Add(E.Message);
end;
end;
end;
end;

procedure TForm1.Button2Click(Sender: TObject);
var qry: TOraQuery;
begin
s1.Options.UseUnicode := True;
s1.Connect;
qry := q2;

qry.Close;
DataSource1.DataSet := q2;

qry.SQL.Text := Memo1.Lines.Text;

//qry.SQL.Text :=
Memo2.Lines.Add('Run script: ');
Memo2.Lines.AddStrings(Memo1.Lines);
Memo2.Lines.Add('------------------------------');
Memo2.Lines.Add('');
if SameText(Copy(Trim(qry.SQL.Text), 1, 6), 'SELECT') then
begin
try
Memo2.Lines.Add('Open query: ' + DateTimeToStr(Now));
qry.Open;
Memo2.Lines.Add('query opened: ' + DateTimeToStr(Now));
except
on E: Exception do
begin
Memo2.Lines.Add(E.Message);
end;
end;
end
else
begin
try
Memo2.Lines.Add('Execute query: ' + DateTimeToStr(Now));
qry.ExecSQL;
Memo2.Lines.Add('Query executed: ' + DateTimeToStr(Now));
except
on E: Exception do
begin
Memo2.Lines.Add(E.Message);
end;
end;
end;
end;

procedure TForm1.Button3Click(Sender: TObject);
var List: TStrings;
I: Integer;
begin
List := TStringList.Create;
try
s1.Connect;
s1.Options.UseUnicode := True;
q1.SQL.Text := 'SELECT Value FROM V$NLS_VALID_VALUES WHERE Parameter = :PARAM1 ORDER BY Value';
q1.ParamByName('PARAM1').AsString := 'CHARACTERSET';
q1.Open;
while not Q1.Eof do
begin
List.Add(Q1.FieldByName('VALUE').AsString);
Q1.Next;
end;
q1.Close;
//s1.Disconnect;
q2.Close;
s2.Connect;
s2.Options.UseUnicode := True;
DataSource1.DataSet := q3;
q2.SQL.Text :=
'update dbadm.test set descr = ''уверен'' where id = 20 ';
for I := 0 to List.Count - 1 do
begin
Memo2.Lines.Add(Format('Charset: %s', [List]));
s1.Options.CharSet := List;
try
q2.ExecSQL;
q3.Close;
q3.Open;
if not q3.IsEmpty then
Memo2.Lines.Add(Format('Descr: %s', [q3.FieldByName('DESCR').AsString]));
except
on E: Exception do
Memo2.Lines.Add(E.Message);
end;
end;
finally
List.Free;
end;
end;

end.

This is the unit1.dfm
object Form1: TForm1
Left = 194
Top = 189
Caption = 'Form1'
ClientHeight = 561
ClientWidth = 1071
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 = 65
Width = 1071
Height = 105
Align = alTop
Font.Charset = RUSSIAN_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
Lines.Strings = (
'update dbadm.test '
'set descr = '#39#1091#1074#1077#1088#1077#1085#39
'where id = 20')
ParentFont = False
TabOrder = 0
ExplicitLeft = 32
ExplicitTop = 88
ExplicitWidth = 865
end
object Memo2: TMemo
Left = 0
Top = 392
Width = 1071
Height = 169
Align = alBottom
Lines.Strings = (
'Memo2')
TabOrder = 1
ExplicitLeft = 32
ExplicitTop = 216
ExplicitWidth = 841
end
object Panel1: TPanel
Left = 0
Top = 0
Width = 1071
Height = 65
Align = alTop
Caption = 'Panel1'
TabOrder = 2
object Button1: TButton
Left = 128
Top = 40
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 0
OnClick = Button1Click
end
object Button2: TButton
Left = 256
Top = 24
Width = 75
Height = 25
Caption = 'Button2'
TabOrder = 1
OnClick = Button2Click
end
object Button3: TButton
Left = 520
Top = 21
Width = 75
Height = 25
Caption = 'Button2'
TabOrder = 2
OnClick = Button3Click
end
end
object DBGrid1: TDBGrid
Left = 0
Top = 170
Width = 1071
Height = 197
Align = alClient
DataSource = DataSource1
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
ParentFont = False
TabOrder = 3
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
object DBNavigator1: TDBNavigator
Left = 0
Top = 367
Width = 1071
Height = 25
DataSource = DataSource1
Align = alBottom
TabOrder = 4
end
object s1: TOraSession
Options.Charset = 'AL32UTF8'
Username = 'DBADM'
Password = '****'
Server = 'M10R_UPG'
Connected = True
LoginPrompt = False
Left = 784
Top = 16
end
object Q2: TOraQuery
Session = s1
Left = 688
Top = 72
end
object DataSource1: TDataSource
DataSet = Qa
Left = 400
Top = 16
end
object sess: TSQLConnection
ConnectionName = 'OracleConnection'
DriverName = 'Oracle'
GetDriverFunc = 'getSQLDriverORACLE'
LibraryName = 'dbxora.dll'
LoginPrompt = False
Params.Strings = (
'DriverName=Oracle'
'DataBase=Database Name'
'User_Name=user'
'Password=password'
'RowsetSize=20'
'BlobSize=-1'
'ErrorResourceFile='
'LocaleCode=0000'
'IsolationLevel=ReadCommitted'
'OS Authentication=False'
'Multiple Transaction=False'
'Trim Char=False'
'Decimal Separator=.')
VendorLib = 'oci.dll'
Left = 312
Top = 112
end
object Qa: TSQLQuery
MaxBlobSize = -1
Params =
SQLConnection = sess
Left = 432
Top = 104
end
object Q1: TOraQuery
Session = s1
Left = 688
Top = 16
end
object Q3: TOraQuery
Session = s2
SQL.Strings = (
'select * from dbadm.Test'
'order by id desc')
Left = 736
Top = 16
end
object s2: TOraSession
Options.UseUnicode = True
Username = 'DBADM'
Password = '****'
Server = 'M10R_UPG'
LoginPrompt = False
Left = 848
Top = 24
end
end

This is the resulting output after clicking button 3:
Memo2
Charset: AL16UTF16
Descr: ??????
Charset: AL24UTFFSS
Descr: ??????
Charset: AL32UTF8
Descr: ??????
Charset: AR8ADOS710
Descr: ??????
Charset: AR8ADOS710T
Descr: ??????
Charset: AR8ADOS720
Descr: ??????
Charset: AR8ADOS720T
Descr: ??????
Charset: AR8APTEC715
Descr: ??????
Charset: AR8APTEC715T
Descr: ??????
Charset: AR8ARABICMAC
Descr: ??????
Charset: AR8ARABICMACS
Descr: ??????
Charset: AR8ARABICMACT
Descr: ??????
Charset: AR8ASMO708PLUS
Descr: ??????
Charset: AR8ASMO8X
Descr: ??????
Charset: AR8EBCDIC420S
Descr: ??????
Charset: AR8EBCDICX
Descr: ??????
Charset: AR8HPARABIC8T
Descr: ??????
Charset: AR8ISO8859P6
Descr: ??????
Charset: AR8MSWIN1256
Descr: ??????
Charset: AR8MUSSAD768
Descr: ??????
Charset: AR8MUSSAD768T
Descr: ??????
Charset: AR8NAFITHA711
Descr: ??????
Charset: AR8NAFITHA711T
Descr: ??????
Charset: AR8NAFITHA721
Descr: ??????
Charset: AR8NAFITHA721T
Descr: ??????
Charset: AR8SAKHR706
Descr: ??????
Charset: AR8SAKHR707
Descr: ??????
Charset: AR8SAKHR707T
Descr: ??????
Charset: AR8XBASIC
Descr: ??????
Charset: AZ8ISO8859P9E
Descr: ??????
Charset: BG8MSWIN
Descr: ??????
Charset: BG8PC437S
Descr: ??????
Charset: BLT8CP921
Descr: ??????
Charset: BLT8EBCDIC1112
Descr: ??????
Charset: BLT8EBCDIC1112S
Descr: ??????
Charset: BLT8ISO8859P13
Descr: ??????
Charset: BLT8MSWIN1257
Descr: ??????
Charset: BLT8PC775
Descr: ??????
Charset: BN8BSCII
Descr: ??????
Charset: CDN8PC863
Descr: ??????
Charset: CE8BS2000
Descr: ??????
Charset: CEL8ISO8859P14
Descr: ??????
Charset: CH7DEC
Descr: ??????
Charset: CL8BS2000
Descr: ??????
Charset: CL8EBCDIC1025
Descr: ??????
Charset: CL8EBCDIC1025C
Descr: ??????
Charset: CL8EBCDIC1025R
Descr: ??????
Charset: CL8EBCDIC1025S
Descr: ??????
Charset: CL8EBCDIC1025X
Descr: ??????
Charset: CL8EBCDIC1158
Descr: ??????
Charset: CL8EBCDIC1158R
Descr: ??????
Charset: CL8ISO8859P5
Descr: ??????
Charset: CL8ISOIR111
Descr: ??????
Charset: CL8KOI8R
Descr: ??????
Charset: CL8KOI8U
Descr: ??????
Charset: CL8MACCYRILLIC
Descr: ??????
Charset: CL8MACCYRILLICS
Descr: ??????
Charset: CL8MSWIN1251
Descr: ??????
Charset: D7DEC
Descr: ??????
Charset: D7SIEMENS9780X
Descr: ??????
Charset: D8BS2000
Descr: ??????
Charset: D8EBCDIC1141
Descr: ??????
Charset: D8EBCDIC273
Descr: ??????
Charset: DK7SIEMENS9780X
Descr: ??????
Charset: DK8BS2000
Descr: ??????
Charset: DK8EBCDIC1142
Descr: ??????
Charset: DK8EBCDIC277
Descr: ??????
Charset: E7DEC
Descr: ??????
Charset: E7SIEMENS9780X
Descr: ??????
Charset: E8BS2000
Descr: ??????
Charset: EE8BS2000
Descr: ??????
Charset: EE8EBCDIC870
Descr: ??????
Charset: EE8EBCDIC870C
Descr: ??????
Charset: EE8EBCDIC870S
Descr: ??????
Charset: EE8ISO8859P2
Descr: ??????
Charset: EE8MACCE
Descr: ??????
Charset: EE8MACCES
Descr: ??????
Charset: EE8MACCROATIAN
Descr: ??????
Charset: EE8MACCROATIANS
Descr: ??????
Charset: EE8MSWIN1250
Descr: ??????
Charset: EE8PC852
Descr: ??????
Charset: EEC8EUROASCI
Descr: ??????
Charset: EEC8EUROPA3
Descr: ??????
Charset: EL8DEC
Descr: ??????
Charset: EL8EBCDIC423R
Descr: ??????
Charset: EL8EBCDIC875
Descr: ??????
Charset: EL8EBCDIC875R
Descr: ??????
Charset: EL8EBCDIC875S
Descr: ??????
Charset: EL8GCOS7
Descr: ??????
Charset: EL8ISO8859P7
Descr: ??????
Charset: EL8MACGREEK
Descr: ??????
Charset: EL8MACGREEKS
Descr: ??????
Charset: EL8MSWIN1253
Descr: ??????
Charset: EL8PC437S
Descr: ??????
Charset: EL8PC737
Descr: ??????
Charset: EL8PC851
Descr: ??????
Charset: EL8PC869
Descr: ??????
Charset: ET8MSWIN923
Descr: ??????
Charset: F7DEC
Descr: ??????
Charset: F7SIEMENS9780X
Descr: ??????
Charset: F8BS2000
Descr: ??????
Charset: F8EBCDIC1147
Descr: ??????
Charset: F8EBCDIC297
Descr: ??????
Charset: HU8ABMOD
Descr: ??????
Charset: HU8CWI2
Descr: ??????
Charset: I7DEC
Descr: ??????
Charset: I7SIEMENS9780X
Descr: ??????
Charset: I8EBCDIC1144
Descr: ??????
Charset: I8EBCDIC280
Descr: ??????
Charset: IN8ISCII
Descr: ??????
Charset: IS8MACICELANDIC
Descr: ??????
Charset: IS8MACICELANDICS
Descr: ??????
Charset: IS8PC861
Descr: ??????
Charset: IW7IS960
Descr: ??????
Charset: IW8EBCDIC1086
Descr: ??????
Charset: IW8EBCDIC424
Descr: ??????
Charset: IW8EBCDIC424S
Descr: ??????
Charset: IW8ISO8859P8
Descr: ??????
Charset: IW8MACHEBREW
Descr: ??????
Charset: IW8MACHEBREWS
Descr: ??????
Charset: IW8MSWIN1255
Descr: ??????
Charset: IW8PC1507
Descr: ??????
Charset: JA16DBCS
Descr: ??????
Charset: JA16DBCSFIXED
Descr: ??????
Charset: JA16EBCDIC930
Descr: ??????
Charset: JA16EUC
Descr: ??????
Charset: JA16EUCFIXED
Descr: ??????
Charset: JA16EUCTILDE
Descr: ??????
Charset: JA16EUCYEN
Descr: ??????
Charset: JA16MACSJIS
Descr: ??????
Charset: JA16SJIS
Descr: ??????
Charset: JA16SJISFIXED
Descr: ??????
Charset: JA16SJISTILDE
Descr: ??????
Charset: JA16SJISYEN
Descr: ??????
Charset: JA16VMS
Descr: ??????
Charset: KO16DBCS
Descr: ??????
Charset: KO16DBCSFIXED
Descr: ??????
Charset: KO16KSC5601
Descr: ??????
Charset: KO16KSC5601FIXED
Descr: ??????
Charset: KO16KSCCS
Descr: ??????
Charset: KO16MSWIN949
Descr: ??????
Charset: LA8ISO6937
Descr: ??????
Charset: LA8PASSPORT
Descr: ??????
Charset: LT8MSWIN921
Descr: ??????
Charset: LT8PC772
Descr: ??????
Charset: LT8PC774
Descr: ??????
Charset: LV8PC1117
Descr: ??????
Charset: LV8PC8LR
Descr: ??????
Charset: LV8RST104090
Descr: ??????
Charset: N7SIEMENS9780X
Descr: ??????
Charset: N8PC865
Descr: ??????
Charset: NDK7DEC
Descr: ??????
Charset: NE8ISO8859P10
Descr: ??????
Charset: NEE8ISO8859P4
Descr: ??????
Charset: NL7DEC
Descr: ??????
Charset: RU8BESTA
Descr: ??????
Charset: RU8PC855
Descr: ??????
Charset: RU8PC866
Descr: ??????
Charset: S7DEC
Descr: ??????
Charset: S7SIEMENS9780X
Descr: ??????
Charset: S8BS2000
Descr: ??????
Charset: S8EBCDIC1143
Descr: ??????
Charset: S8EBCDIC278
Descr: ??????
Charset: SE8ISO8859P3
Descr: ??????
Charset: SF7ASCII
Descr: ??????
Charset: SF7DEC
Descr: ??????
Charset: TH8MACTHAI
Descr: ??????
Charset: TH8MACTHAIS
Descr: ??????
Charset: TH8TISASCII
Descr: ??????
Charset: TH8TISEBCDIC
Descr: ??????
Charset: TH8TISEBCDICS
Descr: ??????
Charset: TR7DEC
Descr: ??????
Charset: TR8DEC
Descr: ??????
Charset: TR8EBCDIC1026
Descr: ??????
Charset: TR8EBCDIC1026S
Descr: ??????
Charset: TR8MACTURKISH
Descr: ??????
Charset: TR8MACTURKISHS
Descr: ??????
Charset: TR8MSWIN1254
Descr: ??????
Charset: TR8PC857
Descr: ??????
Charset: US7ASCII
Descr: ??????
Charset: US8BS2000
Descr: ??????
Charset: US8ICL
Descr: ??????
Charset: US8PC437
Descr: ??????
Charset: UTF8
Descr: ??????
Charset: UTFE
Descr: ??????
Charset: VN8MSWIN1258
Descr: ??????
Charset: VN8VN3
Descr: ??????
Charset: WE8BS2000
Descr: ??????
Charset: WE8BS2000E
Descr: ??????
Charset: WE8BS2000L5
Descr: ??????
Charset: WE8DEC
Descr: ??????
Charset: WE8DG
Descr: ??????
Charset: WE8EBCDIC1047
Descr: ??????
Charset: WE8EBCDIC1047E
Descr: ??????
Charset: WE8EBCDIC1140
Descr: ??????
Charset: WE8EBCDIC1140C
Descr: ??????
Charset: WE8EBCDIC1145
Descr: ??????
Charset: WE8EBCDIC1146
Descr: ??????
Charset: WE8EBCDIC1148
Descr: ??????
Charset: WE8EBCDIC1148C
Descr: ??????
Charset: WE8EBCDIC284
Descr: ??????
Charset: WE8EBCDIC285
Descr: ??????
Charset: WE8EBCDIC37
Descr: ??????
Charset: WE8EBCDIC37C
Descr: ??????
Charset: WE8EBCDIC500
Descr: ??????
Charset: WE8EBCDIC500C
Descr: ??????
Charset: WE8EBCDIC871
Descr: ??????
Charset: WE8EBCDIC924
Descr: ??????
Charset: WE8GCOS7
Descr: ??????
Charset: WE8HP
Descr: ??????
Charset: WE8ICL
Descr: ??????
Charset: WE8ISO8859P1
Descr: ??????
Charset: WE8ISO8859P15
Descr: ??????
Charset: WE8ISO8859P9
Descr: ??????
Charset: WE8ISOICLUK
Descr: ??????
Charset: WE8MACROMAN8
Descr: ??????
Charset: WE8MACROMAN8S
Descr: ??????
Charset: WE8MSWIN1252
Descr: ??????
Charset: WE8NCR4970
Descr: ??????
Charset: WE8NEXTSTEP
Descr: ??????
Charset: WE8PC850
Descr: ??????
Charset: WE8PC858
Descr: ??????
Charset: WE8PC860
Descr: ??????
Charset: WE8ROMAN8
Descr: ??????
Charset: YUG7ASCII
Descr: ??????
Charset: ZHS16CGB231280
Descr: ??????
Charset: ZHS16CGB231280FIXED
Descr: ??????
Charset: ZHS16DBCS
Descr: ??????
Charset: ZHS16DBCSFIXED
Descr: ??????
Charset: ZHS16GBK
Descr: ??????
Charset: ZHS16GBKFIXED
Descr: ??????
Charset: ZHS16MACCGB231280
Descr: ??????
Charset: ZHS32GB18030
Descr: ??????
Charset: ZHT16BIG5
Descr: ??????
Charset: ZHT16BIG5FIXED
Descr: ??????
Charset: ZHT16CCDC
Descr: ??????
Charset: ZHT16DBCS
Descr: ??????
Charset: ZHT16DBCSFIXED
Descr: ??????
Charset: ZHT16DBT
Descr: ??????
Charset: ZHT16HKSCS
Descr: ??????
Charset: ZHT16HKSCS31
Descr: ??????
Charset: ZHT16MSWIN950
Descr: ??????
Charset: ZHT32EUC
Descr: ??????
Charset: ZHT32EUCFIXED
Descr: ??????
Charset: ZHT32SOPS
Descr: ??????
Charset: ZHT32TRIS
Descr: ??????
Charset: ZHT32TRISFIXED
Descr: ??????

Any ideas ?
Of how i can succesfully update the database.
If toad can do it ... i'm sure you guys can find a way

Hello,

Please execute the following queries and send me their results:

SELECT USERENV ('language') from dual;

SELECT * FROM V$NLS_PARAMETERS;

v$nls_parameters

PARAMETER;VALUE
NLS_LANGUAGE;AMERICAN
NLS_TERRITORY;AMERICA
NLS_CURRENCY;$
NLS_ISO_CURRENCY;AMERICA
NLS_NUMERIC_CHARACTERS;.,
NLS_CALENDAR;GREGORIAN
NLS_DATE_FORMAT;DD-MON-RR
NLS_DATE_LANGUAGE;AMERICAN
NLS_CHARACTERSET;AL32UTF8
NLS_SORT;BINARY
NLS_TIME_FORMAT;HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT;DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT;HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT;DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY;$
NLS_NCHAR_CHARACTERSET;AL16UTF16
NLS_COMP;BINARY
NLS_LENGTH_SEMANTICS;BYTE
NLS_NCHAR_CONV_EXCP;FALSE

and language

USERENV('LANGUAGE')
AMERICAN_AMERICA.AL32UTF8

Hello,


Try to update a record using a query with parameter like:

q2.SQL.Text := 'update dbadm.test set descr = :descr where id = 20 ' ;
q2.ParamByName('descr').AsString := 'уверен';
q2.ExecSQL;

You can't use Russian characters in the query body, because your NLS_LANGUAGE parameter is AMERICAN.

I have set my language to RUSSIAN_RUSSIA.AL32UTF8.
The problem is still the same.
It's the TORASQL which uses TStrings instead of TWideStrings

Some querys should not use parameters for certain columns.
These are typically queries with skew indexes.

Assume you have a table containing all the print jobs.
you will want to select only 1 print job at a time

SELECT * FROM PRINT_JOBS WHERE STATUS=:STATUS;
:STATUS := 'NOT PRINTED';
> NOT PRINTED is default when inserting new record

Assume you have 1 million records.
The oracle optimizer will look at the statistics for the index on column STATUS and look at how many distinct value it has for column status:
3 (NOT PRINTED, PRINTING, PRINTED) values and it will estimate it can have 0.33 million records
It won't use the index, but do a full table scan.

For queries like this, it is better to not use a parameter, but the fixed value.
the next query can have a different execution plan just because there is a very limited number of rows returned:
SELECT * FROM PRINT_JOBS WHERE STATUS='NOT PRINTED';

If i have queries like this, they don't work either.
I need this to work too. Parameters are not allways good.
I agree, in 95% of the cases, in that odd 5%, no !
Typically queries with status values in the where clause.

Please provide a fix, so the whole query can work without parameters.
I have noticed that TOraSQL uses TStrings instead of TWideStrings
Wouldn't that solve the problem ?

Do you have OraCall.OCIUnicode := True; in your code? You need this to set the OCI dll calls into unicode mode so that you can use Unicode characters in your SQL literals. You'd do this as early as possible in your program.

Thanx for solving the problem.

Where is this oracall.OCIUnicode documented ?

Hello,

When using the OraCall.OCIUnicode property Oracle Client doesn't work correctly in some cases, so we don't recommend using this property without emergency.

Hi Alex,

Am I correct that the main issue with using the Unicode OCI is in editing objects (i.e. xmltype)? That's the only major bug that I've run into. I know that it is due to an Oracle bug (which they said they'd fix in the 11.2 client but it doesn't appear to have been fixed.) Are there any other specific issues that you've run into? Now that Delphi is fully Unicode it's very important for the future to support the Unicode OCI. Obviously this means trying hard to workaround any Oracle bugs or even working with Oracle to get them fixed (which would really be helpful!)

-Mark

Hello,

This bug does not arise frequently, and it arises only in a specific case, but we still don't recommend using this property.