OraFAQ Forum: SQL & PL/SQL » Convert input from user into UPPERCASE

Home » SQL & PL/SQL » SQL & PL/SQL » Convert input from user into UPPERCASE (Oracle SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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');

Report message to a moderator

Re: Convert input from user into UPPERCASE [message #689762 is a reply to message #689761]

Tue, 16 April 2024 10:35

Barbara Boehmer
Messages: 9091
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.

Report message to a moderator

Re: Convert input from user into UPPERCASE [message #689763 is a reply to message #689762]

Tue, 16 April 2024 10:52

Barbara Boehmer
Messages: 9091
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.

Report message to a moderator

Re: Convert input from user into UPPERCASE [message #689764 is a reply to message #689761]

Tue, 16 April 2024 11:20

Michel Cadot
Messages: 68647
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.

Report message to a moderator

Re: Convert input from user into UPPERCASE [message #689766 is a reply to message #689764]

Wed, 17 April 2024 01:12

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

Report message to a moderator

Re: Convert input from user into UPPERCASE [message #689767 is a reply to message #689766]

Wed, 17 April 2024 01:20

Michel Cadot
Messages: 68647
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.

Report message to a moderator

Re: Convert input from user into UPPERCASE [message #689797 is a reply to message #689761]

Wed, 24 April 2024 03:34

John Watson
Messages: 8931
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.

Report message to a moderator

Previous Topic:	Getting ancestors in hierarchies
Next Topic:	Condition based truncate partition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 13:44:10 CDT 2024