-
Please help! I cannot figure out how to handle 'no data found' in this function. This is called from a procedure and the intent is to check to see if this user name exists in the database before continuing with the procedure. I pass in the user name and if they do exist, I exit the procedure (from which this function is called.) If the user does NOT exist, I want to continue with the calling procedure and enter some information into the database. It seems to be working properly if the user DOES exist... the procedure is exited. If the user does NOT exist, I get an 'ORA-01403: no data found' error and the procedure does not complete. Here is the problem function:
CREATE OR REPLACE FUNCTION USERNAMEEXISTS
(ai_user_name password_hist.user_name%TYPE)
RETURN number
IS
v_passwd_id password_hist.passwd_id%TYPE;
v_return_value number;
BEGIN
SELECT passwd_id into v_passwd_id
FROM password_hist
WHERE UPPER(user_name) = UPPER(ai_user_name);
IF v_passwd_id is NULL
THEN v_return_value := 0;
ELSE
v_return_value := 1;
END IF;
RETURN v_return_value;
END UserNameExists;
Any suggestions? Thank you for any help!!
-
Use exceptions.
CREATE OR REPLACE FUNCTION USERNAMEEXISTS
(ai_user_name password_hist.user_name%TYPE)
RETURN number
IS
v_passwd_id password_hist.passwd_id%TYPE;
v_return_value number;
BEGIN
SELECT passwd_id into v_passwd_id
FROM password_hist
WHERE UPPER(user_name) = UPPER(ai_user_name);
IF v_passwd_id is NULL
THEN v_return_value := 0;
ELSE
v_return_value := 1;
END IF;
RETURN v_return_value;
EXCEPTION
when NO_DATA_FOUND then return null;
END UserNameExists;
-
just a little adjustment ...
You probably don't want the return value to be three-state (0,1,null).
Assuming passw_id is a NOT NULL column you can change the function in this way:
Code:
CREATE OR REPLACE FUNCTION USERNAMEEXISTS
(ai_user_name password_hist.user_name%TYPE)
RETURN number
IS
v_passwd_id password_hist.passwd_id%TYPE;
BEGIN
SELECT passwd_id into v_passwd_id
FROM password_hist
WHERE UPPER(user_name) = UPPER(ai_user_name);
RETURN 1;
EXCEPTION
when NO_DATA_FOUND then return 0;
END UserNameExists;
-
that worked! thank you so much!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|