This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Read/Updating Oracle sequence in 1IM Sync Editor

Hello,

 

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.

 

Regards,

Tac

Parents
  • 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"); }
    else
    { 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"); }
    }
    else
    { 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"); }
    else
    { sql.Append(" ACCOUNT UNLOCK"); }
    // Handle password expiration
    if (store.GetValue<string>("ACCOUNT_STATUS", "").ToUpperInvariant().Contains("EXPIRED"))
    { sql.Append(" PASSWORD EXPIRE"); }
    
    // Create user
    connection.ExecuteSqlNonQuery(sql.ToString());
    // 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));

    HTH

  • Hello Markus,

    Thanks to your solution I was able to create my own custom "script based" "Insert" operation for my Oracle connector.

    However I am experiencing a strange issue regarding Update.
    When a launch the provisioning of data change -> update, nothing happened on target system despite a sucessfull provisioning process.
    FYI I have not customized / created a custom operation for Update. It should then use the standard one.
    I have analysed the logs (stdioprocessor) and I see that no Update call / request is made to the target system.
    The correct provisioning workflow and workflow step of the sync project are called and used. The mapping is correcly evaluated and executed (it detects the new value and set it to the corresponding attrivue). But no action / call is made to the target system to perform the UPDATE (i.e. no SQL Update request).
    Everything looks fine in the logs, without any error, just nothing is done on the target system (except a select).

    Would you have any idea that could explain this strand behaviour ?
    Do I have to create a custom "Update" operation as well ? If yes, could you indicate me if there is a way to create custom "Update" operation that check and take into account only the values / attributes that have changed ?

    Thanks in advance.

    Regards,
    Maxime HENRY

  • FYI I found how to create a correct custom "Update" operation thanks to code snippet.

    Unfortunately even my custom "Update" operation is not used during provisioning and nothing happen on target system.

    I even force the generation of an exception in the code of the custom "Update" method but the provisioning process still ends up successfully without any change. I can only conclude that 1IM is not calling at all the "Update" method, custom or standard one.

    Issue is : I have no clue why.

    Any idea ?

  • I suggest contacting support.

Reply Children
No Data