Oracle DB Connection - Accounts Import XML

[itinjic]

Hello community,

Does anyone know how to create XML Accounts Import configuration for Oracle DB. It is necessary to call the store procedure which should return three columns username, account_status and lock_date.

This is the code I use to test the store procedure in SQL editor :

DECLARE
  users sys.test.user_cursor;
  username VARCHAR2(30);
  account_status VARCHAR2(30);
  lock_date DATE;
BEGIN
  sys.test.UserList(users);
  LOOP
    FETCH users INTO username, account_status, lock_date;
    EXIT WHEN users%NOTFOUND;
    -- Do something with the user data
    DBMS_OUTPUT.PUT_LINE('Username: ' || username || ', Account Status: ' || account_status || ', Lock Date: ' || lock_date);
  END LOOP;
  CLOSE users;
END;

This code prints the results in textual form line by line.

<dataMapping>
    <sql-query description="This is the Source DB Query" >
        <![CDATA[
          DECLARE
            users sys.test.user_cursor;
            username VARCHAR2(30);
            account_status VARCHAR2(30);
            lock_date DATE;
            BEGIN
            sys.test.UserList(users);
            LOOP
                FETCH users INTO username, account_status, lock_date;
                EXIT WHEN users%NOTFOUND;
                -- Do something with the user data
                DBMS_OUTPUT.PUT_LINE('Username: ' || username || ', Account Status: ' || account_status || ', Lock Date: ' || lock_date);
            END LOOP;
            CLOSE users;
            END;

        ]]>
    </sql-query>
    
    <mapper description="This is the mapping field for Saviynt Field name">
        <mapfield saviyntproperty="accounts.accountID" sourceproperty="username" type="character" />
        <mapfield saviyntproperty="accounts.name" sourceproperty="username" type="character" />
        <mapfield saviyntproperty="accounts.status" sourceproperty="account_status" type="character" />
        <mapfield saviyntproperty="accounts.customproperty2" sourceproperty="lock_date" type="date" />
    </mapper>
</dataMapping>

How to handle this ? Are there any required columns that must be present for account import ?

[rushikeshvartak]

• Ask application team to create view and call view in XML

[itinjic]

Are there require parameters that must be inside the view ? 

Reading this documentation Configuring the Integration for Importing Accounts  it says that some parameters must be present in query.

"For this connector, you must pass the value of accountName, securitysystem, endpoint, entitlementtype, and entitlementvalue in this parameter."

[rushikeshvartak]

Yes above parameters are mandatory