Unicode Character Data

Symbolic information in Oracle can be retrieved for the user as a different character encoding according to the query. Oracle supports a number of encoding formats including Unicode. ODAC components support UTF-16 Unicode encoding formats for data fields with OCI 8.0 or higher. Any character of any language can be represented in UTF-16.

ODAC allows to represent string data using string and WideString types. You can use TOraSession.UseUnicode property to enable this behaviour.This property value affects the description of queries and stored procedures. TOraSession.UseUnicode property does not influence the parameters' types that were set manually.

Suppose that SIMPLE_TYPES table is created as:

CREATE TABLE SIMPLE_TYPES (
  ID NUMBER(6) NOT NULL,
  F_CHAR CHAR(250),
  F_VARCHAR VARCHAR2(300),
  F_RAW RAW(250),
)

Suppose we open following SELECT statement in dataset

SELECT a.RowId, a.* FROM SIMPLE_TYPES a

If TOraSession.UseUnicode is set to False you get the next fields list after dataset open:

RowId:     TStringField
ID:        TIntegerField
F_CHAR:    TStringField
F_VARCHAR: TStringField
F_RAW:     TVarBytesField

When you set TOraSession.UseUnicode to True the string fields' type changes:

RowId:     TWideStringField
ID:        TIntegerField
F_CHAR:    TWideStringField
F_VARCHAR: TWideStringField
F_RAW:     TWideStringField

Fields of TWideStringField type hold rows in UTF-16 Unicode format. To get the value of the fields you can use TWideStringField.Value property. You can use FlatBuffers, LongString, FieldsAsString, RawAsString, TrimFixedChar options of TOraDataSet which are compatible with TOraSession.UseUnicode.

To use Unicode values as parameters previously you need to set the value of data type field to ftWideString or ftFixedWideChar for the fields of VARHAR or CHAR types accordingly. Otherwise after the execution of AsWideString or AsString operation data type field will be ftString by default.

var
  WS: WideString;
begin
...
  with OraQuery1 do begin
    Close;
    SQL.Text:=
      'SELECT * from SIMPLE_TYPES '+
      'WHERE '+
      '  F_CHAR = :F_CHAR';

    Params[0].DataType := ftFixedWideChar;
    Params[0].AsWideString := WS;
    Open;
...

If parameter has Unicode data type value, assigning value by using AsString property converts String to WideString. And vice versa, if parameter doesn't have Unicode data type value, assigning value by AsWideString property converts WideString into String.

Also Unicode encoding is supported for ROWID, NUMBER, INTERVAL, TIMESTAMP, RAW, CLOB.

CLOB data type supports string data in UTF-16 Unicode encoding. You can set TOraSession.UseUnicode property to True and get TMemoField of ftOraClob blob type. You can update CLOB field and set its value to Unicode string the following way:

var
  WS: WideString;
begin
...
  with OraQuery1 do begin
    SQL.Text:=
      'UPDATE ODAC_CLOB '+
      'SET '+
      '  Value = EMPTY_CLOB() '+
      'WHERE '+
      '    ID = :ID '+
      'RETURNING '+
      '  Value '+
      'INTO '+
      '  :Value ';
    ParamByName('ID').AsFloat:=1;
    ParamByName('Value').ParamType := ptInput;
    ParamByName('Value').AsCLOBLocator.IsUnicode := True;

    ParamByName('Value').AsCLOBLocator.Write(0, Length(WS)*2, PWideChar(WS));
    // or 
    ParamByName('Value').AsWideString := WS;
    Execute;
...

Unicode Character Data

See Also