Home » SQL & PL/SQL » SQL & PL/SQL » Convert input from user into UPPERCASE (Oracle SQL)
Convert input from user into UPPERCASE [message #689761] Tue, 16 April 2024 10:02 Go to next message
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 #689762 is a reply to message #689761] Tue, 16 April 2024 10:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
Senior Member
Welcome to the OraFAQ forums.  Here is a simplified demonstration of one method;


SCOTT@orcl_12.1.0.2.0> var muser VARCHAR2(10)
SCOTT@orcl_12.1.0.2.0> ACCEPT muser CHAR PROMPT 'Give username :'
Give username :Test123
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    v_user  VARCHAR2(10) := UPPER('&&muser');
  3  BEGIN
  4    EXECUTE IMMEDIATE
  5  	 'CREATE USER "' || v_user || '" IDENTIFIED BY "Test123"';
  6    EXECUTE IMMEDIATE
  7  	 'GRANT CONNECT TO "' || v_user || '"';
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT username FROM all_users WHERE UPPER(username) = 'TEST123'
  2  /

USERNAME
--------------------------------------------------------------------------------
TEST123

1 row selected.
Re: Convert input from user into UPPERCASE [message #689763 is a reply to message #689762] Tue, 16 April 2024 10:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
Senior Member
Here is another method that works from SQL*Plus:


SCOTT@orcl_12.1.0.2.0> COLUMN muser NEW_VALUE v_user NOPRINT
SCOTT@orcl_12.1.0.2.0> ACCEPT muser CHAR PROMPT 'Give username :'
Give username :Test123
SCOTT@orcl_12.1.0.2.0> SELECT UPPER('&&muser') AS muser FROM DUAL
  2  /




1 row selected.

SCOTT@orcl_12.1.0.2.0> CREATE USER "&v_user" IDENTIFIED BY "Test123"
  2  /

User created.

SCOTT@orcl_12.1.0.2.0> GRANT "CONNECT" TO "&v_user"
  2  /

Grant succeeded.

SCOTT@orcl_12.1.0.2.0> SELECT username FROM all_users WHERE UPPER(username) = 'TEST123'
  2  /

USERNAME
--------------------------------------------------------------------------------
TEST123

1 row selected.
Re: Convert input from user into UPPERCASE [message #689764 is a reply to message #689761] Tue, 16 April 2024 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just remove the " around the user name in CREATE USER then you don't need to upper case, Oracle will do it:
SQL> select username from all_users where username='TEST123';

no rows selected

SQL> create user test123 identified by "test123";

User created.

SQL> select username from all_users where username='TEST123';
USERNAME
------------------------------
TEST123

1 row selected.
Re: Convert input from user into UPPERCASE [message #689766 is a reply to message #689764] Wed, 17 April 2024 01:12 Go to previous messageGo to next message
watisditnou@msn.com
Messages: 2
Registered: April 2024
Junior Member
Thank you all very much for your reply. At last it was much easier then i thought. I tried (also) the suggestion from Michel Cadot and remove the " around the user name did the trick. Pff can't believe i didn't tried that Smile.
Re: Convert input from user into UPPERCASE [message #689767 is a reply to message #689766] Wed, 17 April 2024 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Generally speaking, when you give an identifier (user name, table name, column name or any object name), if you specify it with " Oracle keeps it as it, otherwise it converts it in upper case.

Thank you for your feedback.
Re: Convert input from user into UPPERCASE [message #689797 is a reply to message #689761] Wed, 24 April 2024 03:34 Go to previous message
John Watson
Messages: 8932
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.
Previous Topic: Getting ancestors in hierarchies
Next Topic: Condition based truncate partition
Goto Forum:
  


Current Time: Sun May 19 21:07:01 CDT 2024