CODEWITHSUNDEEP
oracle-databaseplsql
alj
alj

Reputation: 170

Execute a stored procedure in oracle

I need to get the output in uu in accordance with value passed through the prompt

create or replace procedure chklg( uu out logn.username%TYPE
                                 , pass in logn.password%TYPE)
is
begin
select username into uu from logn where password=pass;
end;

I tried executing the above procedure this way:

begin 

chklg(:pass);

end

Upvotes: 2

Views: 670

Answers (1)

Ben
Ben

Reputation: 52843

By definition a procedure doesn't return anything. You're looking for a function.

create or replace function chklg ( p_pass in logn.password%TYPE
          ) return varchar2 is -- assuming that logn.username%TYP is a varchar2

   l_uu logn.username%type;

begin
   select username into l_uu from logn where password = p_pass;
   return l_uu;
-- If there-s no username that matches the password return null.
exception when no_data_found then
   return null;
end;

I'm slightly worried by this as it appears as though you're storing a password as plain text. This is not best practice.

You should be storing a salted and peppered hash of your password next to the username, then apply the same salting, peppering and hashing to the password and select the hash from the database.

You can execute the function either of the following two ways:

select chklg(:pass) from dual

or 

declare
   l_pass logn.password%type;
begin
   l_pass := chklg(:pass);
end;
/

To be complete Frank Schmitt has raised a very valid point in the comments. In addition to you storing the passwords in a very dangerous manner what happens if two users have the same password?

You will get a TOO_MANY_ROWS exception raised in your SELECT INTO .... This means that too many rows are returned to the variable. It would be better if you passed the username in as well.

This could make your function look something like the following

create or replace function chklg ( 
         p_password_hash in logn.password%type
       , p_username in logn.username%type
          ) return number

   /* Authenticate a user, return 1/0 depending on whether they have
      entered the correct password.
      */

   l_yes number := 0;

begin

   -- Assumes that username is unique.
   select 1 into l_yes 
     from logn
    where password_hash = p_password_hash
      and username = p_username;

   return l_yes;

-- If there-s no username that matches the password return 0.
exception when no_data_found then
   return 0;
end;

If you're looking to only use a procedure (there's no real reason to do this at all as it unnecessarily restricts you; you're not doing any DML) then you can get the output parameter but you have to give the procedure a parameter that it can populate.

In your case it would look something like this.

declare
   l_uu logn.username%type;
begin 
   chklg(l_uu, :pass);
   dbms_output.put_line(l_uu);
end;

Upvotes: 2

Related Questions