• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

PL/SQL

-- I AM CREATING A FUNCTON THAT ACCEPT A USER NAME AND PASSWORD AS ARGUMENT AND VERIFY THESE VALUES
     AGAINST DATABASE FOR A MATCH. IF A MATCH IS FOUND RETURN VALUE OF 'Y'.
--SET THE INITIAL VALUE THE VARIABLE HOLDING THE RETURN VALUE TO 'N'.
   
CREATE OR REPLACE FUNCTION check_pass
(p_user IN varchar2, p_pass IN varchar2)
 RETURN BOOLEAN
   IS
 v_passwd varchar2(30);
 v_bool BOOLEAN := 'N';
 begin
 select password into v_passwd
 from bb-shopper
 where username =p_user;
 if v_passwd!=p_pass  THEN
 v_bool := FALSE;
 ELSE
 v_bool := TRUE;
 END IF;
exception
when no_data_found then
raise_application_error(-20101,'Invalid username/password!');
end;
/

-- I NEED HELP IN CORRRECTING THIS CODE
0
lojayn
Asked:
lojayn
  • 3
  • 2
1 Solution
 
flow01Commented:
You will have to return the boolean and you cant assign 'N'

CREATE OR REPLACE FUNCTION check_pass
(p_user IN varchar2, p_pass IN varchar2)
 RETURN BOOLEAN
   IS
 v_passwd varchar2(30);
 v_bool BOOLEAN := FALSE;
 begin
 select password into v_passwd
 from bb-shopper
 where username =p_user;
 if v_passwd!=p_pass  THEN
 v_bool := FALSE;
 ELSE
 v_bool := TRUE;
 END IF;
 RETURN v_boolean;
exception
when no_data_found then
raise_application_error(-20101,'Invalid username/password!');
end;
/

do you really want to raise the error or return n

CREATE OR REPLACE FUNCTION check_pass
(p_user IN varchar2, p_pass IN varchar2)
 RETURN BOOLEAN
   IS
 v_passwd varchar2(30);
 v_bool BOOLEAN := FALSE;
 begin
   begin
     select password into v_passwd
        from bb-shopper
        where username =p_user;
        if v_passwd!=p_pass  THEN
       v_bool := FALSE;
      ELSE
      v_bool := TRUE;
      END IF;
   exception
   when no_data_found then
     raise_application_error(-20101,'Invalid username/password!');
  end;
 
  RETURN v_boolean;
   end;
/
0
 
lojaynAuthor Commented:
I want to  raise the error if logon is invalid
0
 
lojaynAuthor Commented:
I am still getting an error
10/16          PL/SQL: ORA-00933: SQL command not properly ended
9/6            PL/SQL: SQL Statement ignored
0
 
flow01Commented:
I don't think a '-' in the table bb-shopper is valid
bb-shopper >  bb_shopper ?
or
bb-shopper >  "bb-shopper"  ?
0
 
lojaynAuthor Commented:
that is right, it is bb_shopper
Also return v_bool not v_boolean
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now