000033589 - How to make the Oracle INITCAP function work to format raw data collected from a csv file during Identity Data Collection in RSA Via Lifecycle and Governance

Document created by RSA Customer Support Employee on Jul 25, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 4Show Document
  • View in full screen mode

Article Content

Article Number000033589
Applies ToRSA Product Set: RSA Via Lifecycle and Governance (RSA Via L&G)
 
IssueWhen using the Oracle INITCAP function in the User Data Query of the Identity Data Collection having CSV as DB type and using driver class com.hxtt.sql.text.TextDriver, the Oracle INITCAP function does not capitalize the first letter of the word in the raw data collected.
 


Identity Data Collection having csv as the DB type and using driver class com.hxtt.sql.text.TextDriver



Identity Data Collection configuration

CSV file containing data to be collected by Identity Data Collection


In the example below, lines 2, 3, 7 and 9 have the emploees' first names and last names in all capital letters.  These need to  be formatted with a capitalized first letter of the word during collection (Michael Jackson vs. MICHAEL JACKSON).
 

CSV file

 

The Oracle INITCAP function returns a character string with the first letter of each word converted to uppercase (also called title case).All other letters in the string are converted to lowercase.
The Oracle INITCAP function works as expected in Oracle SQL Developer.

 

Test Oracle INITCAP via Oracle SQL Developer
 

When using the Oracle INITCAP function in the IDC User Data Query to capitalize the first letter of the first name and last name:
 

Identity Data Collection User Query


As in the example below, the first names and last names are not formatted correctly.  Results after running the collection and verifying the raw data. Note the first name and last name are still in all caps.
 

Identity Data Collection - Collected Raw Data - Using INITCAP only

 
ResolutionTo format this correctly in RSA Via L&G,
  1. Convert the string to lowercase using LOWER, then apply the INITCAP function.
INITCAP(LOWER(FIRSTNAME)) AS FIRSTNAME
INITCAP(LOWER(LASTNAME)) AS LASTNAME

  1. Modify the collector definition "User Data Query" as follows:
Identity Data Collection User Query with workaround

 

  1. Run the collection and verify that the raw data are formatted correctly now; that is, that the first and last names'  first letter is capitalized. Identity Data Collection - Collected Raw Data - Using workaround

Attachments

    Outcomes