Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

function returning boolean

719887Jan 8 2010 — edited Jan 8 2010
Hi 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 5 2010
Added on Jan 8 2010
5 comments
1,564 views