?
Solved

PL/SQL

Posted on 2011-10-23
5
Medium Priority
?
200 Views
Last Modified: 2012-05-12
-- 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
Comment
Question by:lojayn
  • 3
  • 2
5 Comments
 
LVL 21

Accepted Solution

by:
flow01 earned 1000 total points
ID: 37014485
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
 

Author Comment

by:lojayn
ID: 37014601
I want to  raise the error if logon is invalid
0
 

Author Comment

by:lojayn
ID: 37014620
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
 
LVL 21

Expert Comment

by:flow01
ID: 37014794
I don't think a '-' in the table bb-shopper is valid
bb-shopper >  bb_shopper ?
or
bb-shopper >  "bb-shopper"  ?
0
 

Author Comment

by:lojayn
ID: 37014877
that is right, it is bb_shopper
Also return v_bool not v_boolean
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question