Account name starts with number use double quote in oracle

[vermark]

If account name starts with number then we need to use the double quote in account name else pass whatever the account name. 

We are are using below json but it is not working

{
"CreateAccountQry": [
"CREATE USER if($accountName LIKE '[^0-9]%') \" ${accountName}\" else ${accountName} IDENTIFIED BY \"${randomPassword}\" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE APPS ACCOUNT UNLOCK"
]
}

[sahajranajee]

Hello,

Have you tried the same logic directly on the target DB and is it working for you?

[vermark]

I tried below json to see if any account name starting with 9 but it not working. Corresponding database command on traget DB is giving results. Not sure if i am using the correct syntax here...

{
"CreateAccountQry": [
"CREATE USER if($accountName LIKE '9%') \" ${accountName}\" else ${accountName} IDENTIFIED BY \"${randomPassword}\" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE APPS ACCOUNT UNLOCK"
]
}

DB command

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS Where username LIKE '9%';

[amit_krishnajit]

Could you please try the following JSON?

{
	"CreateAccountQry": [
		"CREATE USER ${Character.isDigit(accountName.charAt(0)) ? '\"'+accountName+'\"' : accountName} IDENTIFIED BY \"${randomPassword}\" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE APPS ACCOUNT UNLOCK"
	]
}

[vermark]

Hi Amit,

I tried the JSON you shared. I am getting the parse error as shown below. Could you please assist further on this.

{"log":"2022-05-25 05:22:36,398 [quartzScheduler_Worker-7] DEBUG println.PrintlnToLogger - Println :: \u001b[1;31m| Error \u001b[22;39mgroovy.lang.GroovyRuntimeException: Failed to parse template script (your template may contain an error or be trying to use expressions not currently supported): startup failed:\n","stream":"stdout","time":"2022-05-25T05:22:36.398765292Z"}

[rushikeshvartak]

Does hardcoded accountname works? 

[amit_krishnajit]

Could you pls try the following JSON? We may need to escape the slash. 

{
	"CreateAccountQry": [
		"CREATE USER ${Character.isDigit(accountName.charAt(0)) ? '\\"'+accountName+'\\"' : accountName}@localhost IDENTIFIED BY '${randomPassword}';",
		"FLUSH PRIVILEGES;"
	]
}

[vermark]

Hi Amit,

I have tried the above JSON it is giving the same error. It seems the JSON itself is not valid

{
"CreateAccountQry": [
"CREATE USER ${Character.isDigit(accountName.charAt(0)) ? '\\"'+accountName+'\\"' : accountName} IDENTIFIED BY \"${randomPassword}\" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE APPS ACCOUNT UNLOCK"
]
}

[amit_krishnajit · Accepted solution]

As I mentioned in the previous reply, you may need to escape all slashes being used. So the slash used around randomPassword must also be escaped. 
\\"${randomPassword}\\"

[vermark]

Thank you Amit. It is working fine now.

[vermark]

Hi Amit,

When i am trying the same syntax in GrantAccess And EnableAccount JSON it not working. It is giving the parse error

Here are the modified JSONs

Grant Access JSON
=================
{
"Role": [
"GRANT ${task.entitlement_valueKey.entitlement_value} TO ${Character.isDigit(accountName.charAt(0)) ? '\"'+accountName+'\"' : accountName}"
]
}

Enable Account JSON
===================
{
"EnableAccountQry" :
"ALTER USER ${Character.isDigit(accountName.charAt(0)) ? '\"'+accountName+'\"' : accountName} ACCOUNT UNLOCK"
}

[vermark]

I have also tried \\ as well as you suggested but the same parse error

Grant Access JSON
=================
{
"Role": [
"GRANT ${task.entitlement_valueKey.entitlement_value} TO ${Character.isDigit(accountName.charAt(0)) ? '\\"'+accountName+'\\"' : accountName}"
]
}

Enable Account JSON
===================
{
"EnableAccountQry" :
"ALTER USER ${Character.isDigit(accountName.charAt(0)) ? '\\"'+accountName+'
\\"' : accountName} ACCOUNT UNLOCK"
}

[vermark]

I figured out. It working now.

Thanks

[rushikeshvartak]

It will be great if you can add solution