function returning boolean
719887Jan 8 2010 — edited Jan 8 2010Hi All,
I have a validation on a Page processing that checks If the value in the field USERNAME already exists in a field called USER_NAME in a table called USER_ACCESS. If it does then an error should display saying the username already exists. If it does not exist then values get inserted into the USER_ACCESS table. To do this I have created the following FUNCTION RETURNING BOOLEAN validation:
DECLARE
username VARCHAR2(20) := :P2_USERNAME;
db_username VARCHAR2(20);
result BOOLEAN;
BEGIN
SELECT USER_NAME INTO db_username FROM USER_ACCESS Where USER_ACCESS.USER_NAME = :P2_USERNAME;
IF db_username Is Null THEN
return true;
ELSIF username = db_username THEN
return false;
END IF;
END;
Now when I enter a duplicate username the error message displays correctly however when I enter one that doesnt exist I get the follwoing error:
ORA-01403: no data found
Error ERR-1021 Unable to run "function body returning boolean" validation.
Now it seems that the validation is half working but I'm not sure why the other half isn't????
Anyone any ideas?
I'm sure it obvious but until I go on a PLSQL course next week I'm kinda stuck!! :)
Cheers
Dave