Create Provisioning Policy transform

[jmmb (Jasmine Medina)]

Hi All,

We have create a create account provisioning policy in which it will concatenate the firstname and lastname of the user is the characters are less than 30, else, it will output up to the 30th character of the first and last name.

When I test using a user with less than 30 characters, it works fine, but if it exceeds 30 characters, it is giving this error:

Request failed:
sailpoint.connector.ConnectorException: ORA-01722: invalid number Database - ORA-01722 - unable to convert string value containing invalid_char to a number: column_or_expression

This is the logic that I am using:

{

 "name": "EMP_NAME",

"transform": {

"type": "static",

"attributes": {

"firstname": {

"type": "identityAttribute",

"attributes": {

"name": "firstname"

                        }

                    },

"lastname": {

"type": "identityAttribute",

"attributes": {

"name": "lastname"

                        }

                    },

"value": "#set($name = $firstname + ' ' + $lastname))#if($name.length() > 30)#set($result = $name.substring(0,30))$result#else$name#end"

                }

            },

"attributes": {},

"isRequired": false,

"type": "string",

"isMultiValued": false

        },

[suraj_gorle]

@jmmb

When I test using a user with less than 30 characters, it works fine, but if it exceeds 30 characters, it is giving this error:

Hi @jmmb ,

Two things I’d look at here.

First, there’s a small syntax slip in your #set() — you’ve got an extra closing bracket:

#set($name = $firstname + ' ' + $lastname))

That double )) shouldn’t be there. rewrite it a bit cleaner too, since + in Velocity is really meant for numbers, not strings, and can act a little unpredictable:

#set($name = \"$firstname $lastname\")#if($name.length() > 30)#set($result = $name.substring(0,30))$result#else$name#end

Second — and I think this is the actual root cause — that ORA-01722 error is Oracle complaining that it tried to shove a non-numeric value into a column that’s defined as a NUMBER. So it’s probably not your EMP_NAME logic that’s broken, it’s more likely something else that only kicks in once the name hits exactly 30 characters — like a uniqueness suffix the connector adds when there’s no more room to append it, or some other field tied to this one.

Please try below pointers:

• Run the transform through Preview in ISC with a test identity over 30 characters, just to see the raw output it’s producing
• Check if anything else in the provisioning policy references EMP_NAME or adds a number/suffix to it
• Take a look at the actual Oracle table and see which column is numeric — that’s probably where the real conflict is happening

Curious what the Preview shows once you try it — that should tell us pretty quickly whether it’s the transform or something downstream.

Thanks

[punna0001 (harish Punna)]

Hello Jasmine. I would first clean up the transform. There is an extra ) in the first #set, and the expression can be simplified with Velocity interpolation:

"value": "#set($name = \"$firstname $lastname\")#if($name.length() > 30)$name.substring(0,30)#{else}$name#end"

+ can concatenate strings in Velocity, so I would not treat that as the root cause. Interpolation is just cleaner here.

SailPoint Static transforms support Velocity expressions like this. You could also use the concat transform for the join.

The important test is the output for a name over 30 characters. If it returns exactly the expected 30-character string, then the transform has done its job. ORA-01722 is Oracle’s character-to-number conversion error, not a string-length error, so at that point I would check the Oracle-side create operation or data mapping for the value being converted to a numeric field/expression.

[jmmb (Jasmine Medina)]

Hi @suraj_gorle, thank you for your input!

• I have tried running the transform through Previous in ISC and it outputs the expected result so I do not think there is something wrong with the logic.
• EMP_NAME is not referenced by anything in the provisioning polic

[j_place (Jeremy Place)]

HI Jasmine

I think you should look again. I suspect the additional ‘)’ is getting outputted, so in the case of 30 chars or more, it will return 31 characters.

[suraj_gorle]

Hi @jmmb ,

Please check below screenshot. There is extra ) in the velocity expression.

[punna0001 (harish Punna)]

Since Preview returns the expected value, the transform is likely not the cause.

EMP_NAME not being referenced elsewhere does not rule out the Create Account SQL, which can pull plan values directly via $plan.fieldname.fieldtype$.

ORA-01722 means Oracle failed converting character data to a number (a width problem would be ORA-12899), so check the Create Account query for numeric $plan...int$/.dbl$/.flt$ values, NUMBER columns/parameters, or numeric conversions. Sharing the Create Account SQL or procedure would let us pinpoint the field.