Convert input from user into UPPERCASE [message #689761] |
Tue, 16 April 2024 10:02 |
|
watisditnou@msn.com
Messages: 2 Registered: April 2024
|
Junior Member |
|
|
For Oracle i have made a script to create a user after the input of a name
var muser VARCHAR2(10)
ACCEPT muser CHAR PROMPT 'Give username :'
CREATE USER "&&muser" PROFILE "WORK" IDENTIFIED BY "Test123" PASSWORD EXPIRE DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "&&muser";
GRANT "WORK_READ" TO "&&muser";
GRANT "CLUSTER_MUTATE" TO "&&muser";
This script is working fine but the user is created in lowercase and i want it to be in uppercase. I tried a lot but i can't get it done. It would also be ok if the name will be converted to another variable before the create and grant commands.
Any ideas of maby another script that wil do the trick?
I tried below commands and lots of more:
SET @muser2 = UPPER('&&muser');
SELECT @muser2 := SELECT UPPER('&&muser') from dual;
CREATE USER UPPER(&&muser');
|
|
|
|
|
|
|
|
Re: Convert input from user into UPPERCASE [message #689797 is a reply to message #689761] |
Wed, 24 April 2024 03:34 |
John Watson
Messages: 8960 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Good morning - a bit late, I know, but I happened to come across your question while researching something similar.
It all comes down to the SQL Standard. I don't have the exact reference, but SQL-92 (I think it was 92) specified that lower case identifiers should be converted to upper case, unless double quoted. Oracle is 100% compliant with this: the SQL parser will do the conversion. Some other databases are different. For example, I think that SQL Server will preserve the case of unquoted identifiers when creating an object, but ignore it subsequently. Differences like that can cause bizarre problems when working in a heterogeneous environment.
|
|
|