CODEWITHSUNDEEP
oracle-database
user9152856
user9152856

Reputation: 109

casting variables and select username in oracle 12c

What type of variables should select user from dual return?

If is not varchar, how can I cast to varchar(30) or varchar2(30)?

Does a method exist which returns the current user name better than it?

Upvotes: 0

Views: 46

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

According to:

select dump(user) from dual;

it is type 1, which is varchar2. (Or nvarchar2, based just on that number.) The length - the number of bytes - varies, of course, as it's varchar.

The documentation for the user function also says:

This function returns a VARCHAR2 value.

You can cast the user to varchar2(30):

select cast(user as varchar2(30)) from dual;

but according to dump it's exactly the same afterwards. You'd only see a difference if the username was longer than 30 characters to start with; then it would be truncated. However, the docs also say:

Oracle Database compares values of this function with blank-padded comparison semantics.

Which means that if my username is, say, STACKOVERFLOW then both of these return data:

select * from dual where user = 'STACKOVERFLOW';
select * from dual where user = 'STACKOVERFLOW          ';

The blank-padded comparison means that it's padding the result of user to the length of the literal it's being compared with.

Once you cast the result that is no longer true as it reverts to nonpadded comparison:

select * from dual where cast(user as varchar2(30)) = 'STACKOVERFLOW';

gets a row back but this does not:

select * from dual where cast(user as varchar2(30)) = 'STACKOVERFLOW     ';

An alternative is to use sys_context():

select sys_context('USERENV', 'SESSION_USER') from dual;

which also returns varchar2, and again that be cast to little effect; and this uses nonpadded comparison, so again

select * from dual where sys_context('USERENV', 'SESSION_USER') = 'STACKOVERFLOW'

finds data but this does not:

select * from dual where sys_context('USERENV', 'SESSION_USER') = 'STACKOVERFLOW     '

... with or without explicit casting.

Upvotes: 2

Related Questions