/// event triggered when an expired password is detected
  // - will allow to provide a new password 
  TOnPasswordExpired = function (Sender: TSQLDBConnection; var APassword: RawUTF8): Boolean of object;

  /// will implement properties shared by native Oracle Client Interface connections
  TSQLDBOracleConnectionProperties = class(TSQLDBConnectionPropertiesThreadSafe)
  private
    FUseWallet: boolean;
  protected
    fRowsPrefetchSize: Integer;
    fBlobPrefetchSize: Integer;
    fStatementCacheSize: integer;
    fInternalBufferSize: integer;
    fEnvironmentInitializationMode: integer;
    fOnPasswordChanged: TNotifyEvent;
    fOnPasswordExpired: TOnPasswordExpired;


    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
................................................................................
    property OnPasswordExpired: TOnPasswordExpired read FOnPasswordExpired write FOnPasswordExpired;
    /// Password changed event
    property OnPasswordChanged: TNotifyEvent read FOnPasswordChanged write FOnPasswordChanged;
    /// the number of prepared statements cached by OCI on the Client side
    // - is set to 30 by default
    // - only used if UseCache=true
    property StatementCacheSize: integer read fStatementCacheSize write fStatementCacheSize;
    /// Use the Secure External Password Store for Password Credentials

    // see Oracle documentation http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#DBSEG97906
    property UseWallet: boolean read FUseWallet write FUseWallet;


  end;

  /// implements a direct connection to the native Oracle Client Interface (OCI)
  TSQLDBOracleConnection = class(TSQLDBConnectionThreadSafe)
  protected
    fEnv: pointer;
    fError: pointer;
................................................................................
  end;
end;

procedure TSQLDBOracleConnection.Connect;
var Log: ISynLog;
    Props: TSQLDBOracleConnectionProperties;
    mode: ub4;

const
    type_owner_name: RawUTF8 = 'SYS';
    type_NymberListName: RawUTF8 = 'ODCINUMBERLIST';
    type_Varchar2ListName: RawUTF8 = 'ODCIVARCHAR2LIST';

begin
  Log := SynDBLog.Enter(self);
  Disconnect; // force fTrans=fError=fServer=fContext=nil
  Props := Properties as TSQLDBOracleConnectionProperties;
  with OCI do
  try
    if fEnv=nil then
................................................................................
      AttrSet(fContext,OCI_HTYPE_SVCCTX,@Props.fStatementCacheSize,0,
        OCI_ATTR_STMTCACHESIZE,fError);
      mode := OCI_STMT_CACHE;
    end else
      mode := OCI_DEFAULT;
    if Props.UserID='SYS' then
      mode := mode or OCI_SYSDBA;
    if Props.UseWallet then
      CheckSession(self,nil,SessionBegin(fContext,fError,fSession,OCI_CRED_EXT,mode),fError)
    else
      CheckSession(self,nil,SessionBegin(fContext,fError,fSession,OCI_CRED_RDBMS,mode),fError);  
    Check(self,nil,TypeByName(fEnv,fError,fContext,Pointer(type_owner_name),length(type_owner_name),
      Pointer(type_NymberListName),length(type_NymberListName),nil,0,OCI_DURATION_SESSION,OCI_TYPEGET_HEADER,
      fType_numList),fError);
    Check(self,nil,TypeByName(fEnv,fError,fContext,Pointer(type_owner_name),length(type_owner_name),
      Pointer(type_Varchar2ListName),length(type_Varchar2ListName),nil,0,OCI_DURATION_SESSION,OCI_TYPEGET_HEADER,
      fType_strList),fError);
    if fOCICharSet=0 then begin
................................................................................
        end;
      finally
        Free;
      end;
      fAnsiConvert := TSynAnsiConvert.Engine(CharSetIDToCodePage(fOCICharSet));
    end;
    if Props.UseWallet then
      Log.Log(sllInfo,'Connected to % using Oracle Wallet with %, codepage % (%/%)',
        [Props.ServerName,Props.ClientVersion,fAnsiConvert.CodePage,
         fOCICharSet,OracleCharSetName(fOCICharSet)],self)
    else
      Log.Log(sllInfo,'Connected to % as % with %, codepage % (%/%)',
        [Props.ServerName,Props.UserID,Props.ClientVersion,fAnsiConvert.CodePage,
         fOCICharSet,OracleCharSetName(fOCICharSet)],self);
    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;
    end;
    with NewStatement do
................................................................................
    tmp: RawUTF8;
    str_val: POCIString;
label txt;
begin
  if (fStatement=nil) then
    raise ESQLDBOracle.CreateUTF8('%.ExecutePrepared without previous Prepare',[self]);
  with SynDBLog.Add do
    if sllSQL in Family.Level then



      Log(sllSQL,SQL,self,2048);	

  fTimeElapsed.ProfileCurrentMethod;
  ociArraysCount := 0;
  Env := (Connection as TSQLDBOracleConnection).fEnv;
  Context := TSQLDBOracleConnection(Connection).fContext;
  Status := OCI_ERROR;
  try
    fRowFetchedEnded := false;
................................................................................
      SetLength(oIndicator,fParamCount);
      SetLength(ociArrays,fParamCount);
      for i := 0 to fParamCount-1 do
      if Length(fParams[i].VArray)>0 then begin
        // 1.2.1. Bind an array as one object
        case fParams[i].VType of
        ftInt64:
          Type_List := (Connection as TSQLDBOracleConnection).fType_numList;
        ftUTF8:
          Type_List := (Connection as TSQLDBOracleConnection).fType_strList;
        else
          Type_List := nil;
        end;
        if Type_List=nil then
         raise ESQLDBOracle.CreateUTF8(
            '%.ExecutePrepared: Unsupported array parameter type #%',[self,i+1]);
        ociArrays[ociArraysCount] := nil;
................................................................................
    end;
    // 2. execute prepared statement
    if (fColumnCount=0) and (Connection.TransactionCount=0) then
      // for INSERT/UPDATE/DELETE without a transaction: AutoCommit after execution
      mode := OCI_COMMIT_ON_SUCCESS else
      // for SELECT or inside a transaction: wait for an explicit COMMIT
      mode := OCI_DEFAULT;
    Status := OCI.StmtExecute((Connection as TSQLDBOracleConnection).fContext,
      fStatement,fError,fRowCount,0,nil,nil,mode);
    FetchTest(Status); // error + set fRowCount+fCurrentRow+fRowFetchedCurrent
    Status := OCI_SUCCESS; // mark OK for fBoundCursor[] below
  finally
    for i := 0 to ociArraysCount-1 do
      OCI.Check(nil,self,OCI.ObjectFree(Env, fError, ociArrays[i], OCI_OBJECTFREE_FORCE), fError);
    // 3. release and/or retrieve OUT bound parameters

  /// event triggered when an expired password is detected
  // - will allow to provide a new password 
  TOnPasswordExpired = function (Sender: TSQLDBConnection; var APassword: RawUTF8): Boolean of object;

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


  protected
    fRowsPrefetchSize: Integer;
    fBlobPrefetchSize: Integer;
    fStatementCacheSize: integer;
    fInternalBufferSize: integer;
    fEnvironmentInitializationMode: integer;
    fOnPasswordChanged: TNotifyEvent;
    fOnPasswordExpired: TOnPasswordExpired;
    fUseWallet: boolean;
    fLogSQLWithoutValues: boolean;
    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
................................................................................
    property OnPasswordExpired: TOnPasswordExpired read FOnPasswordExpired write FOnPasswordExpired;
    /// Password changed event
    property OnPasswordChanged: TNotifyEvent read FOnPasswordChanged write FOnPasswordChanged;
    /// the number of prepared statements cached by OCI on the Client side
    // - is set to 30 by default
    // - only used if UseCache=true
    property StatementCacheSize: integer read fStatementCacheSize write fStatementCacheSize;
    /// use the Secure External Password Store for Password Credentials
    // - see Oracle documentation
    // http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#DBSEG97906
    property UseWallet: boolean read fUseWallet write fUseWallet;
    /// by default, will log values within sllSQL unless this propert is TRUE
    property LogSQLWithoutValues: boolean read fLogSQLWithoutValues write fLogSQLWithoutValues;
  end;

  /// implements a direct connection to the native Oracle Client Interface (OCI)
  TSQLDBOracleConnection = class(TSQLDBConnectionThreadSafe)
  protected
    fEnv: pointer;
    fError: pointer;
................................................................................
  end;
end;

procedure TSQLDBOracleConnection.Connect;
var Log: ISynLog;
    Props: TSQLDBOracleConnectionProperties;
    mode: ub4;
    msg: RawUTF8;
const
    type_owner_name: RawUTF8 = 'SYS';
    type_NymberListName: RawUTF8 = 'ODCINUMBERLIST';
    type_Varchar2ListName: RawUTF8 = 'ODCIVARCHAR2LIST';
    type_Credential: array[boolean] of integer = (OCI_CRED_RDBMS,OCI_CRED_EXT);
begin
  Log := SynDBLog.Enter(self);
  Disconnect; // force fTrans=fError=fServer=fContext=nil
  Props := Properties as TSQLDBOracleConnectionProperties;
  with OCI do
  try
    if fEnv=nil then
................................................................................
      AttrSet(fContext,OCI_HTYPE_SVCCTX,@Props.fStatementCacheSize,0,
        OCI_ATTR_STMTCACHESIZE,fError);
      mode := OCI_STMT_CACHE;
    end else
      mode := OCI_DEFAULT;
    if Props.UserID='SYS' then
      mode := mode or OCI_SYSDBA;

    CheckSession(self,nil,SessionBegin(fContext,fError,fSession,type_Credential[Props.UseWallet],mode),fError);


    Check(self,nil,TypeByName(fEnv,fError,fContext,Pointer(type_owner_name),length(type_owner_name),
      Pointer(type_NymberListName),length(type_NymberListName),nil,0,OCI_DURATION_SESSION,OCI_TYPEGET_HEADER,
      fType_numList),fError);
    Check(self,nil,TypeByName(fEnv,fError,fContext,Pointer(type_owner_name),length(type_owner_name),
      Pointer(type_Varchar2ListName),length(type_Varchar2ListName),nil,0,OCI_DURATION_SESSION,OCI_TYPEGET_HEADER,
      fType_strList),fError);
    if fOCICharSet=0 then begin
................................................................................
        end;
      finally
        Free;
      end;
      fAnsiConvert := TSynAnsiConvert.Engine(CharSetIDToCodePage(fOCICharSet));
    end;
    if Props.UseWallet then
      msg := 'using Oracle Wallet' else
      msg := 'as '+Props.UserID;


    Log.Log(sllInfo,'Connected to % % with %, codepage % (%/%)',
      [Props.ServerName,msg,Props.ClientVersion,fAnsiConvert.CodePage,
       fOCICharSet,OracleCharSetName(fOCICharSet)],self);
    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;
    end;
    with NewStatement do
................................................................................
    tmp: RawUTF8;
    str_val: POCIString;
label txt;
begin
  if (fStatement=nil) then
    raise ESQLDBOracle.CreateUTF8('%.ExecutePrepared without previous Prepare',[self]);
  with SynDBLog.Add do
    if sllSQL in Family.Level then begin
      if (Connection.Properties as TSQLDBOracleConnectionProperties).LogSQLWithoutValues then
        tmp := SQL else
        tmp := SQLWithInlinedParams;
      Log(sllSQL,tmp,self,2048);
    end;
  fTimeElapsed.ProfileCurrentMethod;
  ociArraysCount := 0;
  Env := (Connection as TSQLDBOracleConnection).fEnv;
  Context := TSQLDBOracleConnection(Connection).fContext;
  Status := OCI_ERROR;
  try
    fRowFetchedEnded := false;
................................................................................
      SetLength(oIndicator,fParamCount);
      SetLength(ociArrays,fParamCount);
      for i := 0 to fParamCount-1 do
      if Length(fParams[i].VArray)>0 then begin
        // 1.2.1. Bind an array as one object
        case fParams[i].VType of
        ftInt64:
          Type_List := TSQLDBOracleConnection(Connection).fType_numList;
        ftUTF8:
          Type_List := TSQLDBOracleConnection(Connection).fType_strList;
        else
          Type_List := nil;
        end;
        if Type_List=nil then
         raise ESQLDBOracle.CreateUTF8(
            '%.ExecutePrepared: Unsupported array parameter type #%',[self,i+1]);
        ociArrays[ociArraysCount] := nil;
................................................................................
    end;
    // 2. execute prepared statement
    if (fColumnCount=0) and (Connection.TransactionCount=0) then
      // for INSERT/UPDATE/DELETE without a transaction: AutoCommit after execution
      mode := OCI_COMMIT_ON_SUCCESS else
      // for SELECT or inside a transaction: wait for an explicit COMMIT
      mode := OCI_DEFAULT;
    Status := OCI.StmtExecute(TSQLDBOracleConnection(Connection).fContext,
      fStatement,fError,fRowCount,0,nil,nil,mode);
    FetchTest(Status); // error + set fRowCount+fCurrentRow+fRowFetchedCurrent
    Status := OCI_SUCCESS; // mark OK for fBoundCursor[] below
  finally
    for i := 0 to ociArraysCount-1 do
      OCI.Check(nil,self,OCI.ObjectFree(Env, fError, ociArrays[i], OCI_OBJECTFREE_FORCE), fError);
    // 3. release and/or retrieve OUT bound parameters

'1.18.2001'

'1.18.2002'