// - implementations of this class will handle efficiently all Code Pages
  // - this default implementation will use the Operating System APIs
  // - you should not create your own class instance by yourself, but should
  // better retrieve an instance using TSynAnsiConvert.Engine(), which will
  // initialize either a TSynAnsiFixedWidth or a TSynAnsiConvert instance on need
  TSynAnsiConvert = class
  protected
    fCodePage: Integer;
  public
    /// initialize the internal conversion engine
    constructor Create(aCodePage: integer); reintroduce; virtual;
    /// returns the engine corresponding to a given code page
    // - a global list of TSynAnsiConvert instances is handled by the unit -
    // therefore, caller should not release the returned instance
    // - will return nil in case of unhandled code page
    class function Engine(aCodePage: integer): TSynAnsiConvert;
    /// direct conversion of a PAnsiChar buffer into an Unicode buffer
    // - Dest^ buffer must be reserved with at least SourceChars*2 bytes
    // - this default implementation will use the Operating System APIs
    function AnsiBufferToUnicode(Dest: PWideChar; Source: PAnsiChar; SourceChars: Cardinal): PWideChar; overload; virtual;
    /// direct conversion of a PAnsiChar buffer into a UTF-8 encoded buffer
    // - Dest^ buffer must be reserved with at least SourceChars*3 bytes
    // - a #0 char is appended at the end (and result will point to it)
................................................................................
      {$ifdef HASINLINE}inline;{$endif}
    /// convert any Ansi Text (providing a From converted) into Ansi Text
    function AnsiToAnsi(From: TSynAnsiConvert; const Source: RawByteString): RawByteString; overload; 
    /// convert any Ansi buffer (providing a From converted) into Ansi Text
    function AnsiToAnsi(From: TSynAnsiConvert; Source: PAnsiChar; SourceChars: cardinal): RawByteString; overload;
      {$ifdef HASINLINE}inline;{$endif}
    /// corresponding code page
    property CodePage: Integer read fCodePage;
  end;

  /// a class to handle Ansi to/from Unicode translation of fixed width encoding
  // (i.e. non MBCS)
  // - this class will handle efficiently all Code Page availables without MBCS
  // encoding - like WinAnsi (1252) or Russian (1251)
  // - it will use internal fast look-up tables for such encodings
................................................................................
  // advantage of the internal lookup tables to provide some fast process
  TSynAnsiFixedWidth = class(TSynAnsiConvert)
  protected
    fAnsiToWide: TWordDynArray;
    fWideToAnsi: TByteDynArray;
  public
    /// initialize the internal conversion engine
    constructor Create(aCodePage: integer); override;
    /// direct conversion of a PAnsiChar buffer into an Unicode buffer
    // - Dest^ buffer must be reserved with at least SourceChars*2 bytes
    function AnsiBufferToUnicode(Dest: PWideChar; Source: PAnsiChar; SourceChars: Cardinal): PWideChar; override;
    /// direct conversion of a PAnsiChar buffer into a UTF-8 encoded buffer
    // - Dest^ buffer must be reserved with at least SourceChars*3 bytes
    // - a #0 char is appended at the end (and result will point to it)
    function AnsiBufferToUTF8(Dest: PUTF8Char; Source: PAnsiChar; SourceChars: Cardinal): PUTF8Char; override;
................................................................................
    SetString(result,tmpU8,AnsiBufferToUTF8(tmpU8,Source,SourceChars)-tmpU8) else begin
    Getmem(U8,SourceChars*3);
    SetString(result,U8,AnsiBufferToUTF8(U8,Source,SourceChars)-U8);
    FreeMem(U8);
  end;
end;

constructor TSynAnsiConvert.Create(aCodePage: integer);
begin
  fCodePage := aCodePage;
end;

function IsFixedWidthCodePage(aCodePage: integer): boolean;
begin
  result := (aCodePage>=1250) and (aCodePage<=1258);
end;

class function TSynAnsiConvert.Engine(aCodePage: integer): TSynAnsiConvert;
var i: integer;
begin
  if SynAnsiConvertList=nil then
    GarbageCollectorFreeAndNil(SynAnsiConvertList,TObjectList.Create) else
    for i := 0 to SynAnsiConvertList.Count-1 do begin
      result := SynAnsiConvertList.List[i];
      if result.CodePage=aCodePage then
................................................................................
  if SourceChars=0 then
    result := '' else begin
    SetString(result,nil,SourceChars*2+1);
    AnsiBufferToUnicode(pointer(result),Source,SourceChars)^ := #0;
  end;
end;

constructor TSynAnsiFixedWidth.Create(aCodePage: integer);
var i: integer;
    A256: array[0..256] of AnsiChar;
    U256: array[0..256] of WideChar; // AnsiBufferToUnicode() write a last #0
    {$ifopt C+}
    PW: PWideChar;
    {$endif}
begin

  // - implementations of this class will handle efficiently all Code Pages
  // - this default implementation will use the Operating System APIs
  // - you should not create your own class instance by yourself, but should
  // better retrieve an instance using TSynAnsiConvert.Engine(), which will
  // initialize either a TSynAnsiFixedWidth or a TSynAnsiConvert instance on need
  TSynAnsiConvert = class
  protected
    fCodePage: cardinal;
  public
    /// initialize the internal conversion engine
    constructor Create(aCodePage: cardinal); reintroduce; virtual;
    /// returns the engine corresponding to a given code page
    // - a global list of TSynAnsiConvert instances is handled by the unit -
    // therefore, caller should not release the returned instance
    // - will return nil in case of unhandled code page
    class function Engine(aCodePage: cardinal): TSynAnsiConvert;
    /// direct conversion of a PAnsiChar buffer into an Unicode buffer
    // - Dest^ buffer must be reserved with at least SourceChars*2 bytes
    // - this default implementation will use the Operating System APIs
    function AnsiBufferToUnicode(Dest: PWideChar; Source: PAnsiChar; SourceChars: Cardinal): PWideChar; overload; virtual;
    /// direct conversion of a PAnsiChar buffer into a UTF-8 encoded buffer
    // - Dest^ buffer must be reserved with at least SourceChars*3 bytes
    // - a #0 char is appended at the end (and result will point to it)
................................................................................
      {$ifdef HASINLINE}inline;{$endif}
    /// convert any Ansi Text (providing a From converted) into Ansi Text
    function AnsiToAnsi(From: TSynAnsiConvert; const Source: RawByteString): RawByteString; overload; 
    /// convert any Ansi buffer (providing a From converted) into Ansi Text
    function AnsiToAnsi(From: TSynAnsiConvert; Source: PAnsiChar; SourceChars: cardinal): RawByteString; overload;
      {$ifdef HASINLINE}inline;{$endif}
    /// corresponding code page
    property CodePage: Cardinal read fCodePage;
  end;

  /// a class to handle Ansi to/from Unicode translation of fixed width encoding
  // (i.e. non MBCS)
  // - this class will handle efficiently all Code Page availables without MBCS
  // encoding - like WinAnsi (1252) or Russian (1251)
  // - it will use internal fast look-up tables for such encodings
................................................................................
  // advantage of the internal lookup tables to provide some fast process
  TSynAnsiFixedWidth = class(TSynAnsiConvert)
  protected
    fAnsiToWide: TWordDynArray;
    fWideToAnsi: TByteDynArray;
  public
    /// initialize the internal conversion engine
    constructor Create(aCodePage: cardinal); override;
    /// direct conversion of a PAnsiChar buffer into an Unicode buffer
    // - Dest^ buffer must be reserved with at least SourceChars*2 bytes
    function AnsiBufferToUnicode(Dest: PWideChar; Source: PAnsiChar; SourceChars: Cardinal): PWideChar; override;
    /// direct conversion of a PAnsiChar buffer into a UTF-8 encoded buffer
    // - Dest^ buffer must be reserved with at least SourceChars*3 bytes
    // - a #0 char is appended at the end (and result will point to it)
    function AnsiBufferToUTF8(Dest: PUTF8Char; Source: PAnsiChar; SourceChars: Cardinal): PUTF8Char; override;
................................................................................
    SetString(result,tmpU8,AnsiBufferToUTF8(tmpU8,Source,SourceChars)-tmpU8) else begin
    Getmem(U8,SourceChars*3);
    SetString(result,U8,AnsiBufferToUTF8(U8,Source,SourceChars)-U8);
    FreeMem(U8);
  end;
end;

constructor TSynAnsiConvert.Create(aCodePage: cardinal);
begin
  fCodePage := aCodePage;
end;

function IsFixedWidthCodePage(aCodePage: cardinal): boolean;
begin
  result := (aCodePage>=1250) and (aCodePage<=1258);
end;

class function TSynAnsiConvert.Engine(aCodePage: cardinal): TSynAnsiConvert;
var i: integer;
begin
  if SynAnsiConvertList=nil then
    GarbageCollectorFreeAndNil(SynAnsiConvertList,TObjectList.Create) else
    for i := 0 to SynAnsiConvertList.Count-1 do begin
      result := SynAnsiConvertList.List[i];
      if result.CodePage=aCodePage then
................................................................................
  if SourceChars=0 then
    result := '' else begin
    SetString(result,nil,SourceChars*2+1);
    AnsiBufferToUnicode(pointer(result),Source,SourceChars)^ := #0;
  end;
end;

constructor TSynAnsiFixedWidth.Create(aCodePage: cardinal);
var i: integer;
    A256: array[0..256] of AnsiChar;
    U256: array[0..256] of WideChar; // AnsiBufferToUnicode() write a last #0
    {$ifopt C+}
    PW: PWideChar;
    {$endif}
begin

  The Initial Developer of the Original Code is Arnaud Bouchez.

  Portions created by the Initial Developer are Copyright (C) 2013
  the Initial Developer. All Rights Reserved.

  Contributor(s):


  Alternatively, the contents of this file may be used under the terms of
  either the GNU General Public License Version 2 or later (the "GPL"), or
  the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
  in which case the provisions of the GPL or the LGPL are applicable instead
  of those above. If you wish to allow use of your version of this file only
  under the terms of either the GPL or the LGPL, and not to allow others to
  use your version of this file under the terms of the MPL, indicate your
................................................................................
    retrieved value
  - added SQLT_INTERVAL_YM/SQLT_INTERVAL_DS column support (as JSON text)
  - added SynDBOracleOCIpath global variable to force oci.dll folder
  - will search for OracleInstantClient\oci.dll in executable sub-folder (can be
    used e.g. for Win64)
  - enhance logging at connection to specify the client version, database name
    and charset/codepage


  - added some WIN1252 charset aliases for DB (ISO-8859-1/15)
  - force ftCurrency to use '.' as decimal separator in returned SQLT_STR buffer
    (as expected by JSON) - previous fix in 1.17 revision did not work
  - added TSQLDBOracleConnectionProperties.ExtractTnsName() class method
  - when a column charset does not match the connection value, log a warning and
    force connection-level code page instead of raising an Exception
  - fixed ticket [4c68975022] about broken SQL statement when logging active
................................................................................
  {$A+}
  /// wrapper to an array of TOracleDate items
  TOracleDateArray = array[0..(maxInt div sizeof(TOracleDate))-1] of TOracleDate;

  /// will implement properties shared by native Oracle Client Interface connections
  TSQLDBOracleConnectionProperties = class(TSQLDBConnectionPropertiesThreadSafe)
  protected
    fCodePage: cardinal;
    fAnsiConvert: TSynAnsiConvert;
    fRowsPrefetchSize: Integer;
    fBlobPrefetchSize: Integer;
    fStatementCacheSize: integer;
    fInternalBufferSize: integer;
    fEnvironmentInitializationMode: integer;
    function GetClientVersion: RawUTF8;
    /// initialize fForeignKeys content with all foreign keys of this DB
    // - used by GetForeignKey method
    procedure GetForeignKeys; override;
  public
    /// initialize the connection properties
    // - aDatabaseName is not used for Oracle: only aServerName is to be set
    // - this overriden method will force the code page to be zero: you shall
    // better call the CreateWithCodePage constructor instead of this generic method
    constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); override;
    /// initialize the OCI connection properties
    // - we don't need a database name parameter for Oracle connection

    // - you may specify the TNSName in aServerName, or a connection string
    // like '//host[:port]/[service_name]', e.g. '//sales-server:1523/sales'
    // - since the OCI client will make conversion when returning column data,
    // to avoid any truncate when retrieving VARCHAR2 or CHAR fields into the
    // internal fixed-sized buffer, you may specify here the exact database
    // code page, as existing on the server (e.g. CODEPAGE_US=1252 for default
    // WinAnsi western encoding) - if aCodePage is set to 0, either the global
    // NLS_LANG environnement variable is used, either the thread setting (GetACP)
    constructor CreateWithCodePage(const aServerName, aUserID, aPassWord: RawUTF8; aCodePage: integer); virtual;




    /// create a new connection
    // - call this method if the shared MainConnection is not enough (e.g. for
    // multi-thread access)
    // - the caller is responsible of freeing this instance
    // - this overriden method will create an TSQLDBOracleConnection instance
    function NewConnection: TSQLDBConnection; override;
    /// extract the TNS listener name from a Oracle full connection string
    // - e.g. ExtractTnsName('1.2.3.4:1521/dbname') returns 'dbname'
    class function ExtractTnsName(const aServerName: RawUTF8): RawUTF8;
  published
    /// returns the Client version e.g. '11.2.0.1 at oci.dll'
    property ClientVersion: RawUTF8 read GetClientVersion;
    /// the code page used for the connection
    // - e.g. 1252 for default CODEPAGE_US
    // - connection is opened globaly as UTF-8, to match the internal encoding
    // of our units; but CHAR / NVARCHAR2 fields will use this code page encoding
    // to avoid any column truncation when retrieved from the server
    property CodePage: cardinal read fCodePage;
    /// the OCI initialization mode used for the connection
    // - equals OCI_EVENTS or OCI_THREADED by default, since will likely be
    // used in a multi-threaded context (even if this class is inheriting from
    // TSQLDBConnectionPropertiesThreadSafe), and  OCI_EVENTS is needed to support
    // Oracle RAC Connection Load Balancing
    // - can be tuned depending on the configuration or the Oracle version
    property EnvironmentInitializationMode: integer
................................................................................
    fEnv: pointer;
    fError: pointer;
    fServer: pointer;
    fContext: pointer;
    fSession: pointer;
    fTrans: pointer;
    fOCICharSet: integer;

    function DateTimeToDescriptor(aDateTime: TDateTime): pointer;
    procedure STRToUTF8(P: PAnsiChar; var result: RawUTF8;
      ColumnDBCharSet,ColumnDBForm: integer);
    {$ifndef UNICODE}
    procedure STRToAnsiString(P: PAnsiChar; var result: AnsiString;
      ColumnDBCharSet,ColumnDBForm: integer);
    {$endif}
................................................................................
    /// load the oci.dll library
    // - and retrieve all Oci*() addresses for OCI_ENTRIES[] items
    constructor Create;
    /// retrieve the client version as '11.2.0.1 at oci.dll'
    function ClientRevision: RawUTF8;
    /// retrieve the OCI charset ID from a Windows Code Page
    // - will only handle most known Windows Code Page

    // - will use 'WE8MSWIN1252' (CODEPAGE_US) if the Code Page is unknown
    function CodePageToCharSet(env: pointer; aCodePage: integer): integer;
    /// raise an exception on error
    procedure Check(Status: Integer; ErrorHandle: POCIError;
      InfoRaiseException: Boolean=false; LogLevelNoRaise: TSynLogInfo=sllNone);
      {$ifdef HASINLINE} inline; {$endif}
    /// retrieve some BLOB content
    procedure BlobFromDescriptor(svchp: POCISvcCtx; errhp: POCIError;
      locp: POCIDescriptor; out result: RawByteString); overload;
................................................................................
    result := FormatUTF8(EXE_FMT,[major_version,minor_version,update_num,patch_num,fLibraryPath]);
  end;
end;

const
  // http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/applocaledata.htm#i635016
  // http://www.mydul.net/charsets.html
  CODEPAGES: array[0..14] of record Num, Charset: integer; Text: PUTF8Char end = (
    (Num: 1252; Charset: OCI_WE8MSWIN1252; Text: 'WE8MSWIN1252'),
    (Num: 1250; Charset: 170; Text: 'EE8MSWIN1250'),
    (Num: 1251; Charset: 171; Text: 'CL8MSWIN1251'),
    (Num: 1253; Charset: 174; Text: 'EL8MSWIN1253'),
    (Num: 1254; Charset: 177; Text: 'TR8MSWIN1254'),
    (Num: 1255; Charset: 175; Text: 'IW8MSWIN1255'),
    (Num: 1256; Charset: 560; Text: 'AR8MSWIN1256'),
    (Num: 1257; Charset: 179; Text: 'BLT8MSWIN1257'),
    (Num:  874; Charset: 41;  Text: 'TH8TISASCII'),
    (Num:  932; Charset: 832; Text: 'JA16SJIS'),
    (Num:  949; Charset: 846; Text: 'KO16MSWIN949'),
    (Num:  936; Charset: 850; Text: 'ZHS16CGB231280'),

    (Num: 1258; Charset: 45;  Text: 'VN8MSWIN1258'),
     // handle some WIN1252 aliases
    (Num: 1252; Charset: 46;  Text: 'WE8ISO8859P15'),
    (Num: 1252; Charset: 31;  Text: 'WE8ISO8859P1'));

function EnvVariableToCodePage: integer;
var i: integer;
    nlslang: array[byte] of AnsiChar;
begin
................................................................................
    for i2 := 0 to High(CODEPAGES) do
      if (CODEPAGES[i2].Charset=aCharset2) and
         (CODEPAGES[i1].Num=CODEPAGES[i2].Num) then
        exit; // aliases are allowed
  result := false;
end;

function OracleCharSetName(aCharset: integer): PUTF8Char;
var i: integer;
begin
  for i := 0 to high(CODEPAGES) do
    with CODEPAGES[i] do
    if Charset=aCharset then begin
      result := Text;
      exit;
    end;
  result := '?';
end;













function TSQLDBOracleLib.CodePageToCharSet(env: pointer;
  aCodePage: integer): integer;
var ocp: PUTF8Char;
    i: integer;

begin
  case aCodePage of








  CP_UTF8:
    result := OCI_UTF8;
  CP_UTF16:
    result := OCI_UTF16ID;
  else begin
    ocp := CODEPAGES[0].Text; // default is MS Windows Code Page 1252
    for i := 0 to high(CODEPAGES) do
      if aCodePage=CODEPAGES[i].Num then begin
        ocp := CODEPAGES[i].Text;
        break;
      end;
    result := NlsCharSetNameToID(env,ocp);
    if result=0 then
      result := OCI_WE8MSWIN1252;
  end;
  end;


end;

constructor TSQLDBOracleLib.Create;
var P: PPointer;
    i: integer;
    orhome: array[byte] of Char;
begin
................................................................................

var
  OCI: TSQLDBOracleLib = nil;


{ TSQLDBOracleConnectionProperties }

constructor TSQLDBOracleConnectionProperties.CreateWithCodePage(const aServerName,
  aUserID, aPassWord: RawUTF8; aCodePage: integer);
begin
  fDBMS := dOracle;
  inherited Create(aServerName,'',aUserID,aPassWord);
  if OCI=nil then
    GarbageCollectorFreeAndNil(OCI,TSQLDBOracleLib.Create);
  if aCodePage=0 then
    aCodePage := EnvVariableToCodePage;
  fCodePage := aCodePage;
  fAnsiConvert := TSynAnsiConvert.Engine(aCodePage);
  fBlobPrefetchSize := 4096;
  fRowsPrefetchSize := 128*1024;
  fStatementCacheSize := 30; // default is 20
  fInternalBufferSize := 128*1024; // 128 KB
  fBatchSendingAbilities := [cCreate,cUpdate,cDelete]; // array DML feature
  fBatchMaxSentAtOnce := 10000;  // iters <= 32767 for better performance
  fEnvironmentInitializationMode := OCI_EVENTS or OCI_THREADED;
end;

class function TSQLDBOracleConnectionProperties.ExtractTnsName(
  const aServerName: RawUTF8): RawUTF8;
var i: integer;
begin
  i := PosEx('/',aServerName);
  if i=0 then
    result := aServerName else
    result := copy(aServerName,i+1,100);
end;

constructor TSQLDBOracleConnectionProperties.Create(const aServerName,
  aDatabaseName, aUserID, aPassWord: RawUTF8);
begin
  CreateWithCodePage(aServerName,aUserID,aPassWord,0);










end;

function TSQLDBOracleConnectionProperties.GetClientVersion: RawUTF8;
begin
  result := OCI.ClientRevision;
end;

................................................................................
  with OCI do
  try
    if fEnv=nil then
      // will use UTF-8 encoding by default, in a multi-threaded context
      // OCI_EVENTS is needed to support Oracle RAC Connection Load Balancing
      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,
        nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF8);
    if fOCICharSet=0 then
      // retrieve the charset to be used for inlined CHAR / VARCHAR2 fields
      fOCICharSet := CodePageToCharSet(fEnv,Props.CodePage);
    HandleAlloc(fEnv,fError,OCI_HTYPE_ERROR);
    HandleAlloc(fEnv,fServer,OCI_HTYPE_SERVER);
    HandleAlloc(fEnv,fContext,OCI_HTYPE_SVCCTX);
    Check(ServerAttach(fServer,fError,pointer(Props.ServerName),
      length(Props.ServerName),0),fError);
    // we don't catch all errors here, since Client may ignore unhandled ATTR
    AttrSet(fContext,OCI_HTYPE_SVCCTX,fServer,0,OCI_ATTR_SERVER,fError);
................................................................................
    if Props.UseCache then begin
      AttrSet(fContext,OCI_HTYPE_SVCCTX,@Props.fStatementCacheSize,0,
        OCI_ATTR_STMTCACHESIZE,fError);
      mode := OCI_STMT_CACHE;
    end else
      mode := OCI_DEFAULT;
    Check(SessionBegin(fContext,fError,fSession,OCI_CRED_RDBMS,mode),fError);














    {$ifndef DELPHI5OROLDER}
    Log.Log(sllInfo,'Connected to % as % with %, codepage % (%/%)',
      [Props.ServerName,Props.UserID,Props.ClientVersion,Props.CodePage,
       fOCICharSet,OracleCharSetName(fOCICharSet)],self);
    {$endif}
    with NewStatement do
    try // ORM will send date/time as ISO8601 text -> force encoding
      Execute('ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD-HH24:MI:SS''',false);
    finally
      Free;
................................................................................
  ColumnDBCharSet,ColumnDBForm: integer);
var L: integer;
begin
  L := StrLen(PUTF8Char(P));
  if (L=0) or (ColumnDBCharSet=OCI_UTF8) or (ColumnDBForm=SQLCS_NCHAR) or
     (fOCICharSet=OCI_UTF8) then
    SetString(result,P,L) else
    result := TSQLDBOracleConnectionProperties(Properties).fAnsiConvert.
      AnsiBufferToRawUTF8(P,L);
end;

{$ifndef UNICODE}
procedure TSQLDBOracleConnection.STRToAnsiString(P: PAnsiChar; var result: AnsiString;
  ColumnDBCharSet,ColumnDBForm: integer);
var L: integer;
begin
  L := StrLen(PUTF8Char(P));
  with TSQLDBOracleConnectionProperties(Properties) do
  if (L=0) or ((ColumnDBCharSet<>OCI_UTF8) and (ColumnDBForm<>SQLCS_NCHAR) and
     (fCodePage=GetACP)) then
    SetString(result,P,L) else
    result := CurrentAnsiConvert.AnsiToAnsi(
      TSQLDBOracleConnectionProperties(Properties).fAnsiConvert,P,L);
end;
{$endif}



{ TSQLDBOracleStatement }

  The Initial Developer of the Original Code is Arnaud Bouchez.

  Portions created by the Initial Developer are Copyright (C) 2013
  the Initial Developer. All Rights Reserved.

  Contributor(s):
  - richard6688
  
  Alternatively, the contents of this file may be used under the terms of
  either the GNU General Public License Version 2 or later (the "GPL"), or
  the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
  in which case the provisions of the GPL or the LGPL are applicable instead
  of those above. If you wish to allow use of your version of this file only
  under the terms of either the GPL or the LGPL, and not to allow others to
  use your version of this file under the terms of the MPL, indicate your
................................................................................
    retrieved value
  - added SQLT_INTERVAL_YM/SQLT_INTERVAL_DS column support (as JSON text)
  - added SynDBOracleOCIpath global variable to force oci.dll folder
  - will search for OracleInstantClient\oci.dll in executable sub-folder (can be
    used e.g. for Win64)
  - enhance logging at connection to specify the client version, database name
    and charset/codepage
  - removed CreateWithCodePage() constructor: now the charset will be retrieved
    at connection, and used for CHAR/NVARCHAR2 fields - see ticket [a6a639ec43]
  - added some WIN1252 charset aliases for DB (ISO-8859-1/15)
  - force ftCurrency to use '.' as decimal separator in returned SQLT_STR buffer
    (as expected by JSON) - previous fix in 1.17 revision did not work
  - added TSQLDBOracleConnectionProperties.ExtractTnsName() class method
  - when a column charset does not match the connection value, log a warning and
    force connection-level code page instead of raising an Exception
  - fixed ticket [4c68975022] about broken SQL statement when logging active
................................................................................
  {$A+}
  /// wrapper to an array of TOracleDate items
  TOracleDateArray = array[0..(maxInt div sizeof(TOracleDate))-1] of TOracleDate;

  /// will implement properties shared by native Oracle Client Interface connections
  TSQLDBOracleConnectionProperties = class(TSQLDBConnectionPropertiesThreadSafe)
  protected


    fRowsPrefetchSize: Integer;
    fBlobPrefetchSize: Integer;
    fStatementCacheSize: integer;
    fInternalBufferSize: integer;
    fEnvironmentInitializationMode: integer;
    function GetClientVersion: RawUTF8;
    /// initialize fForeignKeys content with all foreign keys of this DB
    // - used by GetForeignKey method
    procedure GetForeignKeys; override;
  public
    /// initialize the connection properties





    // - we don't need a database name parameter for Oracle connection: only
    // aServerName is to be set
    // - you may specify the TNSName in aServerName, or a connection string
    // like '//host[:port]/[service_name]', e.g. '//sales-server:1523/sales'







    // - connection is opened globaly as UTF-8, to match the internal encoding
    // of our units; but CHAR / NVARCHAR2 fields will use the Oracle charset
    // as retrieved from the opened connection (to avoid any conversion error)
    constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); override;
    /// create a new connection
    // - call this method if the shared MainConnection is not enough (e.g. for
    // multi-thread access)
    // - the caller is responsible of freeing this instance
    // - this overriden method will create an TSQLDBOracleConnection instance
    function NewConnection: TSQLDBConnection; override;
    /// extract the TNS listener name from a Oracle full connection string
    // - e.g. ExtractTnsName('1.2.3.4:1521/dbname') returns 'dbname'
    class function ExtractTnsName(const aServerName: RawUTF8): RawUTF8;
  published
    /// returns the Client version e.g. '11.2.0.1 at oci.dll'
    property ClientVersion: RawUTF8 read GetClientVersion;






    /// the OCI initialization mode used for the connection
    // - equals OCI_EVENTS or OCI_THREADED by default, since will likely be
    // used in a multi-threaded context (even if this class is inheriting from
    // TSQLDBConnectionPropertiesThreadSafe), and  OCI_EVENTS is needed to support
    // Oracle RAC Connection Load Balancing
    // - can be tuned depending on the configuration or the Oracle version
    property EnvironmentInitializationMode: integer
................................................................................
    fEnv: pointer;
    fError: pointer;
    fServer: pointer;
    fContext: pointer;
    fSession: pointer;
    fTrans: pointer;
    fOCICharSet: integer;
    fAnsiConvert: TSynAnsiConvert;
    function DateTimeToDescriptor(aDateTime: TDateTime): pointer;
    procedure STRToUTF8(P: PAnsiChar; var result: RawUTF8;
      ColumnDBCharSet,ColumnDBForm: integer);
    {$ifndef UNICODE}
    procedure STRToAnsiString(P: PAnsiChar; var result: AnsiString;
      ColumnDBCharSet,ColumnDBForm: integer);
    {$endif}
................................................................................
    /// load the oci.dll library
    // - and retrieve all Oci*() addresses for OCI_ENTRIES[] items
    constructor Create;
    /// retrieve the client version as '11.2.0.1 at oci.dll'
    function ClientRevision: RawUTF8;
    /// retrieve the OCI charset ID from a Windows Code Page
    // - will only handle most known Windows Code Page
    // - if aCodePage=0, will use the NLS_LANG environment variable
    // - will use 'WE8MSWIN1252' (CODEPAGE_US) if the Code Page is unknown
    function CodePageToCharSetID(env: pointer; aCodePage: integer): integer;
    /// raise an exception on error
    procedure Check(Status: Integer; ErrorHandle: POCIError;
      InfoRaiseException: Boolean=false; LogLevelNoRaise: TSynLogInfo=sllNone);
      {$ifdef HASINLINE} inline; {$endif}
    /// retrieve some BLOB content
    procedure BlobFromDescriptor(svchp: POCISvcCtx; errhp: POCIError;
      locp: POCIDescriptor; out result: RawByteString); overload;
................................................................................
    result := FormatUTF8(EXE_FMT,[major_version,minor_version,update_num,patch_num,fLibraryPath]);
  end;
end;

const
  // http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/applocaledata.htm#i635016
  // http://www.mydul.net/charsets.html
  CODEPAGES: array[0..15] of record Num, Charset: integer; Text: PUTF8Char end = (
    (Num: 1252; Charset: OCI_WE8MSWIN1252; Text: 'WE8MSWIN1252'),
    (Num: 1250; Charset: 170; Text: 'EE8MSWIN1250'),
    (Num: 1251; Charset: 171; Text: 'CL8MSWIN1251'),
    (Num: 1253; Charset: 174; Text: 'EL8MSWIN1253'),
    (Num: 1254; Charset: 177; Text: 'TR8MSWIN1254'),
    (Num: 1255; Charset: 175; Text: 'IW8MSWIN1255'),
    (Num: 1256; Charset: 560; Text: 'AR8MSWIN1256'),
    (Num: 1257; Charset: 179; Text: 'BLT8MSWIN1257'),
    (Num:  874; Charset: 41;  Text: 'TH8TISASCII'),
    (Num:  932; Charset: 832; Text: 'JA16SJIS'),
    (Num:  949; Charset: 846; Text: 'KO16MSWIN949'),
    (Num:  936; Charset: 852; Text: 'ZHS16GBK'),
    (Num:  950; Charset: 867; Text: 'ZHT16MSWIN950'),
    (Num: 1258; Charset: 45;  Text: 'ZHT16HKSCS'),
     // handle some aliases
    (Num: 1252; Charset: 46;  Text: 'WE8ISO8859P15'),
    (Num: 1252; Charset: 31;  Text: 'WE8ISO8859P1'));

function EnvVariableToCodePage: integer;
var i: integer;
    nlslang: array[byte] of AnsiChar;
begin
................................................................................
    for i2 := 0 to High(CODEPAGES) do
      if (CODEPAGES[i2].Charset=aCharset2) and
         (CODEPAGES[i1].Num=CODEPAGES[i2].Num) then
        exit; // aliases are allowed
  result := false;
end;

function OracleCharSetName(aCharsetID: integer): PUTF8Char;
var i: integer;
begin
  for i := 0 to high(CODEPAGES) do
    with CODEPAGES[i] do
    if Charset=aCharsetID then begin
      result := Text;
      exit;
    end;
  result := '?';
end;

function CharSetIDToCodePage(aCharSetID: integer): integer;
var i: integer;
begin
  for i := 0 to high(CODEPAGES) do
    with CODEPAGES[i] do
    if Charset=aCharsetID then begin
      result := Num;
      exit;
    end;
  result := GetACP; // return the default OS code page if not found
end;

function TSQLDBOracleLib.CodePageToCharSetID(env: pointer;
  aCodePage: integer): integer;
var ocp: PUTF8Char;
    i: integer;
    nlslang: array[byte] of AnsiChar;
begin
  case aCodePage of
  0: begin
    i := GetEnvironmentVariableA('NLS_LANG',nlslang,sizeof(nlslang));
    if i<>0 then begin
      nlslang[i] := #0;
      result := NlsCharSetNameToID(env,nlslang);
    end else
      result := 0;
  end;
  CP_UTF8:
    result := OCI_UTF8;
  CP_UTF16:
    result := OCI_UTF16ID;
  else begin
    ocp := CODEPAGES[0].Text; // default is MS Windows Code Page 1252
    for i := 0 to high(CODEPAGES) do
      if aCodePage=CODEPAGES[i].Num then begin
        ocp := CODEPAGES[i].Text;
        break;
      end;
    result := NlsCharSetNameToID(env,ocp);


  end;
  end;
  if result=0 then
    result := OCI_WE8MSWIN1252;
end;

constructor TSQLDBOracleLib.Create;
var P: PPointer;
    i: integer;
    orhome: array[byte] of Char;
begin
................................................................................

var
  OCI: TSQLDBOracleLib = nil;


{ TSQLDBOracleConnectionProperties }





















class function TSQLDBOracleConnectionProperties.ExtractTnsName(
  const aServerName: RawUTF8): RawUTF8;
var i: integer;
begin
  i := PosEx('/',aServerName);
  if i=0 then
    result := aServerName else
    result := copy(aServerName,i+1,100);
end;

constructor TSQLDBOracleConnectionProperties.Create(const aServerName,
  aDatabaseName, aUserID, aPassWord: RawUTF8);
begin
  fDBMS := dOracle;
  inherited Create(aServerName,'',aUserID,aPassWord);
  if OCI=nil then
    GarbageCollectorFreeAndNil(OCI,TSQLDBOracleLib.Create);
  fBlobPrefetchSize := 4096;
  fRowsPrefetchSize := 128*1024;
  fStatementCacheSize := 30; // default is 20
  fInternalBufferSize := 128*1024; // 128 KB
  fBatchSendingAbilities := [cCreate,cUpdate,cDelete]; // array DML feature
  fBatchMaxSentAtOnce := 10000;  // iters <= 32767 for better performance
  fEnvironmentInitializationMode := OCI_EVENTS or OCI_THREADED;
end;

function TSQLDBOracleConnectionProperties.GetClientVersion: RawUTF8;
begin
  result := OCI.ClientRevision;
end;

................................................................................
  with OCI do
  try
    if fEnv=nil then
      // will use UTF-8 encoding by default, in a multi-threaded context
      // OCI_EVENTS is needed to support Oracle RAC Connection Load Balancing
      EnvNlsCreate(fEnv,Props.EnvironmentInitializationMode,
        nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF8);



    HandleAlloc(fEnv,fError,OCI_HTYPE_ERROR);
    HandleAlloc(fEnv,fServer,OCI_HTYPE_SERVER);
    HandleAlloc(fEnv,fContext,OCI_HTYPE_SVCCTX);
    Check(ServerAttach(fServer,fError,pointer(Props.ServerName),
      length(Props.ServerName),0),fError);
    // we don't catch all errors here, since Client may ignore unhandled ATTR
    AttrSet(fContext,OCI_HTYPE_SVCCTX,fServer,0,OCI_ATTR_SERVER,fError);
................................................................................
    if Props.UseCache then begin
      AttrSet(fContext,OCI_HTYPE_SVCCTX,@Props.fStatementCacheSize,0,
        OCI_ATTR_STMTCACHESIZE,fError);
      mode := OCI_STMT_CACHE;
    end else
      mode := OCI_DEFAULT;
    Check(SessionBegin(fContext,fError,fSession,OCI_CRED_RDBMS,mode),fError);
    if fOCICharSet=0 then begin
      // retrieve the charset to be used for inlined CHAR / VARCHAR2 fields
      with NewStatement do
      try
        Execute('SELECT NLS_CHARSET_ID(PROPERTY_VALUE) FROM DATABASE_PROPERTIES'+
          ' WHERE PROPERTY_NAME=''NLS_CHARACTERSET''',true);
        if Step then
          fOCICharSet := ColumnInt(0) else
          fOCICharSet := CodePageToCharSetID(fEnv,0); // retrieve from NLS_LANG
      finally
        Free;
      end;
      fAnsiConvert := TSynAnsiConvert.Engine(CharSetIDToCodePage(fOCICharSet));
    end;
    {$ifndef DELPHI5OROLDER}
    Log.Log(sllInfo,'Connected to % as % with %, codepage % (%/%)',
      [Props.ServerName,Props.UserID,Props.ClientVersion,fAnsiConvert.CodePage,
       fOCICharSet,OracleCharSetName(fOCICharSet)],self);
    {$endif}
    with NewStatement do
    try // ORM will send date/time as ISO8601 text -> force encoding
      Execute('ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD-HH24:MI:SS''',false);
    finally
      Free;
................................................................................
  ColumnDBCharSet,ColumnDBForm: integer);
var L: integer;
begin
  L := StrLen(PUTF8Char(P));
  if (L=0) or (ColumnDBCharSet=OCI_UTF8) or (ColumnDBForm=SQLCS_NCHAR) or
     (fOCICharSet=OCI_UTF8) then
    SetString(result,P,L) else

    result := fAnsiConvert.AnsiBufferToRawUTF8(P,L);
end;

{$ifndef UNICODE}
procedure TSQLDBOracleConnection.STRToAnsiString(P: PAnsiChar; var result: AnsiString;
  ColumnDBCharSet,ColumnDBForm: integer);
var L: integer;
begin
  L := StrLen(PUTF8Char(P));

  if (L=0) or ((ColumnDBCharSet<>OCI_UTF8) and (ColumnDBForm<>SQLCS_NCHAR) and
     (fAnsiConvert.CodePage=GetACP)) then
    SetString(result,P,L) else
    result := CurrentAnsiConvert.AnsiToAnsi(fAnsiConvert,P,L);

end;
{$endif}



{ TSQLDBOracleStatement }