CODEWITHSUNDEEP
sqloracle-databaseoracle11gora-06553
Posidon
Posidon

Reputation: 35

Function to verify username and password in pl/sql

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?

Upvotes: -1

Views: 17516

Answers (3)

user330315
user330315

Reputation:

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;

Upvotes: 2

Beverly
Beverly

Reputation: 1

Just to add more information to what's already been provided, the BEGIN keyword indicates the beginning of the execution block; what's above that is the function header and any declaration statements.

The statement z number; is a variable declaration statement declaring a variable that is named z and is of the datatype number. The SELECT 1 INTO z WHERE... statement is checking the BANKCUSTOMER table for a row where the username matches what's passed to the function in the first parameter, and a password that matches what's passed to the function in the second parameter.

If there is a row where the username and password match what's passed to the function, then the variable z will contain the number 1. If there isn't, the Oracle NO_ROWS_FOUND exception will be raised, because SELECT...INTO statements must always select one and only one row, or else they will raise an exception (the NO_ROWS_FOUND exception for no rows, and the TOO_MANY_ROWS exception for more than one row).

Hope that's helpful! Don't hesitate to ask if you have more questions.

Upvotes: 0

Irfy
Irfy

Reputation: 9587

Have you actually passed any arguments to the log_in function? And what is logga_in()? Is the latter a typo on your side?

Anyway, the select 1 into z only forces an exception in case no match is found. Nothing more.

In other words, you could write the code without it, for example with select count(*) into authenticated ... and then you could check if authenticated != 0 and do the appropriate action. I don't have an Oracle instance so this code is written blindly, you'll need to test it:

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
    dbms_output.put_line('Wrong username or password!');
  elsif match_count = 1 then
    dbms_output.put_line('Login successful!');
  else
    dbms_output.put_line('Too many matches, this should never happen!');
end;

Upvotes: 1

Related Questions