Read/Updating Oracle sequence in 1IM Sync Editor



We established a target system connection using native Oracle connector. The target system is a small application, they use Oracle Sequence to generate simple user UID as key column.


So when we provision new user from UNSAccountB of 1IM to the target system, we need to read the Oracle Sequence and insert the value into the UID column, but I don't know how to achieve it.


Can anyone give me an example how the mapping can be done? Thanks.




No Data
  • You need to create your own data operation in your Oracle connector configuration, either pattern based or script based.

    A sample script based data operation to create new Oracle users in the table SYS.DBA_USERS is following below.

    StringBuilder sql = new StringBuilder();
    IValueStore store = data.Values.CreateValueStore();
    // Construct the SQL statement for creating user
    sql.AppendFormat(store.Replace("CREATE USER \"%USERNAME%\"", true));
    // Handle profile
    if (string.IsNullOrEmpty(store.GetValue<string>("PROFILE", "")))
    { sql.Append(" PROFILE DEFAULT"); }
    { sql.AppendFormat(" PROFILE \"{0}\"", store.GetValue<string>("PROFILE", "")); }
    // Handle authentication
    if (!string.IsNullOrEmpty(store.GetValue<string>("AUTHENTICATION_TYPE", "")))
        if (store.GetValue<string>("AUTHENTICATION_TYPE", "").Equals("External", StringComparison.InvariantCultureIgnoreCase))
        { sql.Append(" IDENTIFIED EXTERNALLY"); }
    { sql.AppendFormat(" IDENTIFIED BY \"{0}\"", store.GetValue<string>("PASSWORD", "")); }
    // Handle default tablespace
    if (!string.IsNullOrEmpty(store.GetValue<string>("DEFAULT_TABLESPACE", "")))
    { sql.AppendFormat(" DEFAULT TABLESPACE \"{0}\"", store.GetValue<string>("DEFAULT_TABLESPACE", "").ToUpperInvariant()); }
    // Handle temporary tablespace
    if (!string.IsNullOrEmpty(store.GetValue<string>("TEMPORARY_TABLESPACE", "")))
    { sql.AppendFormat(" TEMPORARY TABLESPACE \"{0}\"", store.GetValue<string>("TEMPORARY_TABLESPACE", "").ToUpperInvariant()); }
    // Handle account lock
    if (store.GetValue<string>("ACCOUNT_STATUS", "").ToUpperInvariant().Contains("LOCKED"))
    { sql.Append(" ACCOUNT LOCK"); }
    { sql.Append(" ACCOUNT UNLOCK"); }
    // Handle password expiration
    if (store.GetValue<string>("ACCOUNT_STATUS", "").ToUpperInvariant().Contains("EXPIRED"))
    { sql.Append(" PASSWORD EXPIRE"); }
    // Create user
    // Grant connect to user
    connection.ExecuteSqlNonQuery(store.Replace("GRANT \"CONNECT\" TO \"%USERNAME%\"", true));
    // Fetch auto-generated primary key from database
    object key = connection.ExecuteSqlScalar(store.Replace("SELECT USER_ID FROM SYS.DBA_USERS WHERE USERNAME = '%USERNAME%'", true));
    // Return key value (You may have to convert the key to correct datatype)
    return new ScriptBasedDMLStrategyResult("USER_ID", Convert.ToInt64(key));