Function to verify username and password in pl/sql

Question

I've got a table called BANKCUSTOMER with the following columns:

 USERNAME                                  NOT NULL VARCHAR2(11)
 FAMILY_NAME                               NOT NULL VARCHAR2(25)
 NAME                                      NOT NULL VARCHAR2(25)
 PASSWD                                    NOT NULL VARCHAR2(6)

I want to make a function which checks in the database if the users USERNAME and PASSWORD matches the data in the database. If the login succeeds then it should print out "Login successful!" otherwise "Wrong username or password!"

I visited a pl/sql tutorial site and came over the following code which i modified a bit so it can work with my database, but there is something I don't understand and that is what z number does and what begin select 1 into z does. Could someone please explain that for me.

create or replace function log_in(x in varchar2, y in varchar2)   
return varchar2  
as  
  z number;  
begin  
  select 1  
    into z   
    from bankcustomer   
    where username=x   
    and passwd=y;  
  dbms_output.put_line('Login successful!');  
exception  
when no_data_found then  
  dbms_output.put_line('Wrong username or password!'); 
end; 

I would like to test the function by writing SELECT log_in() FROM dual; to see if it works. When I write SELECT log_in() FROM dual; I get an error message saying:

Error starting at line 1 in command: SELECT log_in() FROM dual Error at Command Line:1 Column:7 Error report: SQL Error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'LOG_IN' 06553. 00000 - "PLS-%s: %s" *Cause:
*Action:

How can this be resolved?

Answer 1

You have defined a function but do not return a value from it. Given the fact that you "select" the function there is no need to use dbms_output:

create or replace function log_in(x in varchar2, y in varchar2)
return varchar2
as
  match_count number;
begin
  select count(*)
    into match_count
    from bankcustomer
    where username=x
    and passwd=y;
  if match_count = 0 then
    return 'Wrong username or password!';
  elsif match_count = 1 then
    return 'Login successful!';
  else
    return 'Too many matches, this should never happen!';
  end if;
end;
/

Additionally your call to the function does not provide the username and password parameters, that's why you get the error message. Assuming you have changed the function to actually return something, you need to use

SELECT log_in('username', 'secretpassword') FROM dual;