| Bizarre behaviour of a Function [message #2631] |
Thu, 01 August 2002 08:12  |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
I am confused as to why the following function that I have in a package repeatedly returns true even though I can invoke it with different user names on each invocation.
FUNCTION UserExists(userName IN user_users.username%TYPE) RETURN BOOLEAN IS
RecordCount NUMBER;
BEGIN
SELECT COUNT(*) INTO RecordCount FROM dba_users
WHERE dba_users.username = userName;
IF RecordCount = 0 THEN
RETURN false;
ELSE
RETURN true;
END IF;
END;
Any ideas anyone ?
Mark Grimshaw
|
|
|
|
| Re: Bizarre behaviour of a Function [message #2633 is a reply to message #2631] |
Thu, 01 August 2002 10:17  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Just a note that you can collapse that return logic into just:
return (RecordCount = 1);
As far as the results go, it is impossible to provide any ideas without more data. The function itself looks fine. You would have to show us the equivalent query results outside the function - show a query with a valid user, then a non-existent user, then show the function result for both users.
|
|
|
|
| Re: Bizarre behaviour of a Function [message #2638 is a reply to message #2633] |
Fri, 02 August 2002 03:04 |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
Todd, As ever thanks for the reply.
Due to the mere fact that you replied to my question and that you prompted me to scrutinise my code I have have made 2 observations. Maybe you could comment on these observations and give me guidance.
I checked my test script and ran the query as you asked from the SQL Plus command line.
These are my results/observations:-
-------------------------------------------------------
1) User DML queries such as SELECT use CASE
SENSITIVE comparisons when comparing column
values.
I created a user fred as SYSTEM at the SQL
command prompt. I then ran the following queries
from the command prompt:
SELECT COUNT(*) FROM dba_users WHERE username=
'fred';
This returned a count of 0 !!
The following query returned a count of 1
SELECT COUNT(*) FROM dba_users WHERE username=
'FRED';
I do not understand the reasoning behind the
result for the first query when you consider
observation 2 below.
2) DDL SQL statement use CASE INSENSITIVE
comparisons.
I tried to do the following from the command
prompt:-
CREATE USER FRED IDENTIFIED BY letmein;
I got the 01920 error complaining that the
user 'FRED' conflicts with another user name or
role name.
-------------------------------------------------------
I promptly changed my test script that used the function by passing an upper case name and it worked
as expected. I understand the reasoning behind
case insensitivity of stored usernames but don't know why a query such as SELECT needs to use the correct case?
Thanks
Mark
Oh - could you point out the function to use to
convert strings to upper case as I have had enough
of reading Oracle documentation for one week.
|
|
|
|
| Re: Bizarre behaviour of a Function [message #2644 is a reply to message #2633] |
Fri, 02 August 2002 10:49 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Yes, DML is always case-sensitive unless compensated for in the query (by changing the case of the column and/or the criteria). And this is a good thing.
The DDL is different in that Oracle stores all object names in uppercase by default. You may create a user like:
create user fred identified by fred;
but that user gets created internally as FRED.
sql>create user fred identified by fred;
User created.
sql>create user "fred" identified by fred;
User created.
sql>select username from dba_users where username in ('fred', 'FRED');
USERNAME
------------------------------
FRED
fred
sql>create user FRED identified by fred;
create user FRED identified by fred
*
ERROR at line 1:
ORA-01920: user name 'FRED' conflicts with another user or role name
Oh, the function to convert to uppercase...UPPER.
|
|
|
|
| Re: Bizarre behaviour of a Function [message #2651 is a reply to message #2633] |
Sun, 04 August 2002 06:04 |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
Thanks,
I am currently writing this reply from home and so don't have my Oracle docs to hand so I have two further questions: - You say that with DDL all object names are stored as uppercase but you appeared to override that particular rule with create user "fred" ... I don't get it. Also, you you enlighten me as to why DML is case sensitive ? Is it anything to do with the ability to store usernames as lower case using the override that you demonstrated?
Thanks
Mark
|
|
|
|
| Re: Bizarre behaviour of a Function [message #2661 is a reply to message #2633] |
Mon, 05 August 2002 08:21 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
By using the double-quotes around the object name, I overrode the default uppercase storage. The same principle applies to objects like tables. Although legal and possible, the creation of case-sensitive (non-uppercase) object names can lead to all sorts of compatibility issues down the road with not only internal code/queries, but third-party tools.
DML is always case-sensitive because data is always stored as entered. A first name of 'Mark' is very different than 'MARK' or 'mark'. It is up to the developer to insure data consistency. If you allow names to be entered in mixed-case (which is typical), then your query to retrieve rows by name needs to be case-insensitive. This can be accomplished several ways - by uppercasing the data on the search query, storing a duplicate case-insentitive version of the column, or by using function-based indexes (IMHO, the best way).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
